分區表常見問題

問題描述

在4.0.0到4.0.8版本中，因為 4.0 版本為了最佳化邏輯分區表並發寫多個分區的效能，支援了多分區的資料落盤，落盤時 Cluster Index 的處理邏輯存在 Bug，導致寫到Apsara Distributed File System上的 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 後返回如下樣本結果：

 detection_sql
----------------------------------------------------------------------------------------------------------------------
 SELECT COUNT(1) FROM public.tbl WHERE c2 >= -2147483648; -- DB:postgres, Table:public.tbl, CK:c2 (type:integer)
 SELECT COUNT(1) FROM public.tbl2 WHERE c1 >= ''; -- DB:postgres, Table:public.tbl2, CK:c1 (type:text)
 SELECT COUNT(1) FROM public.tbl3 WHERE c1 >= ''; -- DB:postgres, Table:public.tbl3, CK:c1 (type:text)
 SELECT COUNT(1) FROM public.tbl4 WHERE c1 >= ''; -- DB:postgres, Table:public.tbl4, CK:c1 (type:text)
 SELECT COUNT(1) FROM public.tbl5 WHERE c1 >= ''; -- DB:postgres, Table:public.tbl5, CK:c1 (type:text)
(5 rows)

運行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可以檢查當前資料庫是否有表存在主鍵重複問題。

返回的結果樣本：

 detection_sql
---------------------------------------------------------------------------------------------------------------------+
 -- Check PK duplicates in public.tbl, partition: c2=1                                                              +
 SELECT c1,c2, COUNT(1) FROM public.tbl WHERE c2 = '1' GROUP BY c1,c2 HAVING COUNT(1) > 1;
 -- Check PK duplicates in public.tbl, partition: c2=2                                                              +
 SELECT c1,c2, COUNT(1) FROM public.tbl WHERE c2 = '2' GROUP BY c1,c2 HAVING COUNT(1) > 1;
 -- Check PK duplicates in public.tbl, partition: c2=4                                                              +
 SELECT c1,c2, COUNT(1) FROM public.tbl WHERE c2 = '4' GROUP BY c1,c2 HAVING COUNT(1) > 1;
 -- Check PK duplicates in public.tbl4, partition: c1=1                                                             +
 SELECT c1,c2, COUNT(1) FROM public.tbl4 WHERE c1 = '1' GROUP BY c1,c2 HAVING COUNT(1) > 1;
 -- Check PK duplicates in public.tbl4, partition: c1=2                                                             +
 SELECT c1,c2, COUNT(1) FROM public.tbl4 WHERE c1 = '2' GROUP BY c1,c2 HAVING COUNT(1) > 1;
 -- Check PK duplicates in public.tbl5, partition: c1=1/c2=1                                                        +
 SELECT c1,c2, COUNT(1) FROM public.tbl5 WHERE c1 = '1' AND c2 = '1' GROUP BY c1,c2 HAVING COUNT(1) > 1;
(6 rows)

運行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 重複的風險, 即時寫入反查也可能出現cluster_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的命令。

返回結果樣本：

compact_sql
=========================================================================================================================
 SELECT hologres.hg_full_compact_table('public.tbl6'); -- Table: public.tbl6 (has logical partition but NO clustering_key)
 SELECT hologres.hg_full_compact_table('public.tbl7'); -- Table: public.tbl7 (has logical partition but NO clustering_key)
(2 rows)

在業務低峰期執行即可。