Fix Partitioned Table Errors and Missing Data in Hologres V4.0 - Hologres - Alibaba Cloud - Hologres

Problem

In Hologres versions 4.0.0 to 4.0.8, an optimization for concurrent writes to logical partitioned tables introduced a bug. This bug can incorrectly write the clustering index within AliORC data files, which are the data files for internal tables that use column-oriented storage. This can cause two issues.
- A corrupted clustering index can cause queries to fail to find existing data.
- Because queries cannot find existing data, update operations may mistakenly insert new records, leading to duplicate primary keys.
This issue occurs only if all of the following three conditions are met:
- Your Hologres instance is on a version from 4.0.0 to 4.0.8.
- You use a logical partitioned table with column-oriented storage or hybrid row-columnar storage.
- You have explicitly set a clustering key.

Solution

Step 1: Upgrade instance

Upgrade your instance to version 4.0.9 or later.
After you upgrade to version 4.0.9, if a query attempts to use a corrupted index, the query fails and returns the following error message:
```
clustered_index size [x] incorrect.
```

Step 2: Check tables for corrupted indexes

Run the following SQL statement for each database to detect tables with potential issues.

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;

The preceding statement generates a set of SQL statements. Run these statements to find tables in the current database that have a corrupted index.

The following code block shows a sample result after you run the detection statement:

 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)

Run the generated SQL statements. If a statement runs successfully, the index is correct. If a statement returns a cluster_index size mismatch error, the index is corrupted. You must perform a full compaction on the table mentioned in the error to merge files and repair the corrupted index file.
```
SELECT hologres.hg_full_compact_table('schema.table_name','max_file_size_mb=1, reclaim_deleted_data_space = false');
```
Re-run the generated SQL statement for the table to verify the fix. It should now complete without error.

Step 3: Fix duplicate primary keys

Logical partitioned tables may contain duplicate primary keys. This issue manifests as a duplicate key value violates unique constraint error, or you might find multiple rows with the same primary key. Follow these steps to check for this issue:

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;

This statement generates a set of SQL statements. Run these statements to find tables in the current database with duplicate primary keys.

The following code block shows a sample result:

 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)

Run the generated SQL queries. If a query returns no data, no duplicate primary keys exist. If a query returns data, duplicate primary keys exist. For the affected table, perform a full compaction, and then run the command to remove the duplicate primary keys.

-- Step 1
select hologres.hg_full_compact_table('schema.table_name','max_file_size_mb=1');
-- Step 2
call public.hg_remove_duplicated_pk('schema.table_name','max_file_size_mb=1');
-- Note: If hg_remove_duplicated_pk returns a 'Query exceed memory limit' error, the data volume is too large. 
-- In this case, remove duplicates partition by partition. The comments in the SQL statements generated in the previous step identify which partitions contain duplicate data.
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''');

After you run the commands, the duplicate data is removed. Run the check statement for the affected table again to verify the fix. The check statement should now return no data.

Step 4 (Optional): Compact other at-risk tables

If the checks in the previous steps find no issues, you can perform a further check. For tables without a defined clustering key, query correctness is not affected. However, there is still a risk of a duplicate primary key, and real-time write-back operations might still cause a cluster_index size mismatch error. To mitigate this potential risk, you can run the following SQL statement to generate a list of potentially affected tables. We recommend performing a full compaction on these tables during off-peak hours to mitigate this risk.

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;

The preceding SQL statement generates full compaction commands.

The following code block shows a sample result:

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)

Run the generated commands during off-peak hours.