DWS Metadata Inconsistency - Abnormal Partition Index

Symptom

The following error is reported when a user checks table definitions: "ERROR: The local index xxx on the partition xxx not exist"

Possible Causes

The primary table index is not damaged, but a partition index recorded in the pg_partition system catalog is inconsistent with that in other system catalogs. As a result, the partition index information cannot be found and an error is reported.

Reproducing the Issue

1. Create a table with two partitions, p1 and p2.

   1	CREATE TABLE a_0317(a int) partition by range(a) (partition p1 values less than (4), partition p2 values less than (8));

2. Create a primary table and its partition indexes.

   1	CREATE INDEX a_0317_index on a_0317(a) local (partition p1_index, partition p2_inde);

3. Check the partition index information.

   1. Check the primary table index information.

      1 2 3 4 5 6 7 8 9 10 11

      SELECT oid,* FROM pg_class where relname ='a_0317_index'; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reld eltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid64 --------+--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+----- --------+----------------+--------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------- -+------------------+----------------+----------+--------------+--------+------------+--------------+---------------- 241487 | a_0317_index | 2200 | 0 | 0 | 16393 | 403 | 241487 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | f | f | p | i | 1 | 0 | f | f | f | f | f | 0 | f | f | p | 0 | | | n | 0 (1 row)

   2. Check the partition index information based on the primary table index information.

      1 2 3 4 5 6 7 8 9 10

      SELECT * FROM pg_partition where parentid= 241487; relname | parttype | parentid | rangenum | intervalnum | partstrategy | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | indextblid | indisusable | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relfrozenxid | intspnum | partkey | intervaltablespace | interval | boundaries | transit | reloptions | relfrozenxid64 ----------+----------+----------+----------+-------------+--------------+-------------+---------------+----------+-----------+---------------+---------------+---------------+------------+-------------+- --------------+-------------+----------------+--------------+--------------+----------+---------+--------------------+----------+------------+---------+------------+---------------- p1_index | x | 241487 | 0 | 0 | n | 241488 | 0 | 0 | 0 | 0 | 0 | 0 | 241485 | t | 0 | 0 | 0 | 0 | 0 | | | | | | | | 0 p2_inde | x | 241487 | 0 | 0 | n | 241489 | 0 | 0 | 0 | 0 | 0 | 0 | 241486 | t | 0 | 0 | 0 | 0 | 0 | | | | | | | | 0 (2 rows)

4. Connect to a CN to start read and write transactions. Delete the index information of the p1 partition from the pg_partition system catalog.

   1 2	START TRANSACTION read write; DELETE from pg_partition where relname = 'p1_index';

5. Check the table definition error.

   1 2	\d+ a_0317 ERROR: The local index 700633 on the partition 700647 not exist.CONTEXT: referenced column: pg_get_indexdef

Solution

1. Delete the index information of the table.

   1	DROP INDEX a_0317_index;

2. Recreate the index of the table.

   1	CREATE INDEX a_0317_index on a_0317(a) local (partition p1_index, partition p2_inde);

3. Check the table definition.

   1 2 3 4 5 6 7 8 9 10 11 12 13

   \d+ a_0317 Table "public.a_0317" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+------------- a | integer | | plain | | Indexes: "a_0317_index" btree (a) LOCAL(PARTITION p1_index, PARTITION p2_inde) TABLESPACE pg_default Range partition by(a) Number of partition: 2 (View pg_partition to check each partition range.) Has OIDs: no Distribute By: HASH(a) Location Nodes: ALL DATANODES Options: orientation=row, compression=no