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
- 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));
- 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);
Check the partition index information.
- 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)
- 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)
- Check the primary table index information.
- 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';
- 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
- Delete the index information of the table.
1
DROP INDEX a_0317_index;
- Recreate the index of the table.
1
CREATE INDEX a_0317_index on a_0317(a) local (partition p1_index, partition p2_inde);
- 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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.