修复因Cluster Index错误导致的分区表数据无法查询与主键重复-实时数仓Hologres-阿里云

问题描述

在4.0.0到4.0.8版本中，因为 4.0 版本为了优化逻辑分区表并发写多个分区的性能，支持了多分区的数据落盘，落盘时 Cluster Index 的处理逻辑存在 Bug，导致写到盘古上的 AliORC 文件（列存内表的数据文件）内部的 Cluster Index 可能有错，导致潜在的2个问题。
- Cluster Index 错误可能导致数据存在但查不出来。
- 因为Cluster Index 错误可能导致数据存在但查不出来最终导致在写入更新场景下，主键重复的问题。
触发条件（需要同时满足以下3个条件）
- 实例版本处于4.0.0到4.0.8版本。
- 使用逻辑分区表，且表的存储使用了列存或者行列共存。
- 显式设置了clustering_key。

处理方式

升级实例

将实例升级到4.0.9以及以上版本。
升级到4.0.9后，如果查询使用了错误的索引，查询会报错，报错信息如下：
```
clustered_index size [x] incorrect。
```

检查索引存在问题的表

使用如下SQL检测存在问题的表，需要逐个数据库逐个执行：

WITH logical_partition_tables AS (
    SELECT DISTINCT 
        p1.table_namespace, 
        p1.table_name
    FROM hologres.hg_table_properties p1
    WHERE p1.property_key = 'logical_partition_columns'
      AND EXISTS (
          SELECT 1
          FROM hologres.hg_table_properties p2
          WHERE p2.table_namespace = p1.table_namespace
            AND p2.table_name = p1.table_name
            AND p2.property_key = 'orientation'
            AND p2.property_value IN ('column', 'row,column', 'column,row')
      )
),
tables_with_pk AS (
    SELECT 
        l.table_namespace,
        l.table_name,
        p.property_value AS pk_expr
    FROM logical_partition_tables l
    JOIN hologres.hg_table_properties p
      ON l.table_namespace = p.table_namespace
     AND l.table_name = p.table_name
    WHERE p.property_key = 'primary_key'
),
partition_cols AS (
    SELECT 
        l.table_namespace,
        l.table_name,
        p.property_value AS part_expr
    FROM logical_partition_tables l
    JOIN hologres.hg_table_properties p
      ON l.table_namespace = p.table_namespace
     AND l.table_name = p.table_name
    WHERE p.property_key = 'logical_partition_columns'
),
all_partitions AS (
    SELECT 
        t.table_namespace,
        t.table_name,
        t.pk_expr,
        pc.part_expr,
        part.partition
    FROM tables_with_pk t
    JOIN partition_cols pc
      ON t.table_namespace = pc.table_namespace
     AND t.table_name = pc.table_name
    CROSS JOIN LATERAL (
        SELECT partition
        FROM hologres.hg_list_logical_partition(
            (t.table_namespace || '.' || t.table_name)::TEXT
        )
    ) AS part
),
split_kv AS (
    SELECT 
        table_namespace,
        table_name,
        pk_expr,
        partition,
        TRIM(SPLIT_PART(kv, '=', 1)) AS col_name,
        TRIM(SPLIT_PART(kv, '=', 2)) AS col_val
    FROM all_partitions,
    UNNEST(STRING_TO_ARRAY(partition, '/')) AS kv
),
final_where AS (
    SELECT 
        table_namespace,
        table_name,
        pk_expr,
        partition,
        STRING_AGG(
            FORMAT('%I = %L', col_name, col_val),
            ' AND '
            ORDER BY col_name
        ) AS where_clause
    FROM split_kv
    GROUP BY table_namespace, table_name, pk_expr, partition
)
SELECT
    FORMAT(
        '-- Check PK duplicates in %I.%I, partition: %s' || E'\n' ||
        'SELECT %s, COUNT(1) FROM %I.%I WHERE %s GROUP BY %s HAVING COUNT(1) > 1;',
        table_namespace, table_name, partition,
        pk_expr,
        table_namespace, table_name,
        where_clause,
        pk_expr
    ) AS detection_sql
FROM final_where
ORDER BY table_namespace, table_name, partition;

执行上述SQL会返回一些SQL, 运行返回的SQL可以检查当前数据库是否有表存在索引写错问题。
返回的结果示例:
运行SQL,如果成功执行,说明索引正确, 如果出现cluster_index size mismatch的报错,说明索引有误, 请对报错中的表执行full_compaction操作，触发文件合并修复错误的索引文件。
```
SELECT hologres.hg_full_compact_table('schema.table_name','max_file_size_mb=1, reclaim_deleted_data_space = false');
```
执行后，索引构建正确，可以对相应的表运行上述检查SQL进行复核，预期不会出现错误。

检查主键重复的表

对逻辑分区表, 可能出现主键重复的问题,现象是出现duplicate key value violates unique constraint的报错, 或者同样的PK存在两条以上的数据, 请按照以下步骤进行检查：

WITH logical_partition_tables AS (
    SELECT DISTINCT 
        p1.table_namespace, 
        p1.table_name
    FROM hologres.hg_table_properties p1
    WHERE p1.property_key = 'logical_partition_columns'
      AND EXISTS (
          SELECT 1
          FROM hologres.hg_table_properties p2
          WHERE p2.table_namespace = p1.table_namespace
            AND p2.table_name = p1.table_name
            AND p2.property_key = 'orientation'
            AND p2.property_value IN ('column', 'row,column')
      )
),
tables_with_pk AS (
    SELECT 
        l.table_namespace,
        l.table_name,
        p.property_value AS pk_expr
    FROM logical_partition_tables l
    JOIN hologres.hg_table_properties p
      ON l.table_namespace = p.table_namespace
     AND l.table_name = p.table_name
    WHERE p.property_key = 'primary_key'
),
partition_cols AS (
    SELECT 
        l.table_namespace,
        l.table_name,
        p.property_value AS part_expr
    FROM logical_partition_tables l
    JOIN hologres.hg_table_properties p
      ON l.table_namespace = p.table_namespace
     AND l.table_name = p.table_name
    WHERE p.property_key = 'logical_partition_columns'
),
all_partitions AS (
    SELECT 
        t.table_namespace,
        t.table_name,
        t.pk_expr,
        pc.part_expr,
        part.partition
    FROM tables_with_pk t
    JOIN partition_cols pc
      ON t.table_namespace = pc.table_namespace
     AND t.table_name = pc.table_name
    CROSS JOIN LATERAL (
        SELECT partition
        FROM hologres.hg_list_logical_partition(
            (t.table_namespace || '.' || t.table_name)::TEXT
        )
    ) AS part
),
split_kv AS (
    SELECT 
        table_namespace,
        table_name,
        pk_expr,
        partition,
        TRIM(SPLIT_PART(kv, '=', 1)) AS col_name,
        TRIM(SPLIT_PART(kv, '=', 2)) AS col_val
    FROM all_partitions,
    UNNEST(STRING_TO_ARRAY(partition, '/')) AS kv
),
final_where AS (
    SELECT 
        table_namespace,
        table_name,
        pk_expr,
        partition,
        STRING_AGG(
            FORMAT('%I = %L', col_name, col_val),
            ' AND '
            ORDER BY col_name
        ) AS where_clause
    FROM split_kv
    GROUP BY table_namespace, table_name, pk_expr, partition
)
SELECT
    FORMAT(
        '-- Check PK duplicates in %I.%I, partition: %s' || E'\n' ||
        'SELECT %s, COUNT(1) FROM %I.%I WHERE %s GROUP BY %s HAVING COUNT(1) > 1;',
        table_namespace, table_name, partition,
        pk_expr,
        table_namespace, table_name,
        where_clause,
        pk_expr
    ) AS detection_sql
FROM final_where
ORDER BY table_namespace, table_name, partition;

执行该SQL会返回一些SQL, 运行返回的SQL可以检查当前数据库是否有表存在主键重复问题。
返回的结果示例：

运行SQL查询时，如果未查出数据，则表明不存在主键重复。如果SQL返回了数据，则说明存在主键重复。请对相应表执行full_compaction操作后，再运行removePK命令。

-- 第一步
select hologres.hg_full_compact_table('schema.table_name','max_file_size_mb=1');
-- 第二步
call public.hg_remove_duplicated_pk('schema.table_name','max_file_size_mb=1');
-- 注意:如果hg_remove_duplicated_pk出现了'Query exceed memory limit'的报错,说明数据量比较大,可以改成按分区去重(上一步的SQL注释中有展示有重复数据的分区)
call public.hg_remove_duplicated_pk('schema.table_name_1', 'dt_int=1');
call public.hg_remove_duplicated_pk('schema.table_name_2', 'dt_text=''A''');

执行后，重复数据已被去除。可以对相应的表运行上述检查SQL进行再次验证，预期不会出现重复数据。

检查潜在存在问题的表

如果以上两个检查都没有问题，可以进一步检查，对于没有设置过clustering key的表，不会出现显式查询的正确性的问题，但是可能会存在 Primary Key 重复的风险, 实时写入反查也可能出现clustere_index size mismatch的报错，针对这种情况，可以运行如下SQL生成对应的潜在风险表列表，建议在业务低峰期对潜在问题的表执行 full compaction操作, 这样可以规避潜在风险，运行如下SQL：

SELECT
    FORMAT(
       'SELECT hologres.hg_full_compact_table(%L, ''max_file_size_mb=1''); -- Table: %I.%I (has logical partition but NO clustering_key)',
      table_namespace || '.' || table_name,
      table_namespace,
      table_name
    ) AS compact_sql
FROM (
  SELECT DISTINCT 
  p1.table_namespace, 
  p1.table_name
  FROM hologres.hg_table_properties p1
  WHERE p1.property_key = 'logical_partition_columns'
  AND EXISTS (
    SELECT 1
    FROM hologres.hg_table_properties p2
    WHERE p2.table_namespace = p1.table_namespace
    AND p2.table_name = p1.table_name
    AND p2.property_key = 'orientation'
    AND p2.property_value IN ('column', 'row,column')
  )
) AS tables_missing_ck
ORDER BY table_namespace, table_name;

运行上述SQL, 会生成full compact的命令。
返回结果示例：
在业务低峰期执行即可。