DWS元数据不一致-分区索引异常

问题现象

某局点查看表定义报错：“The local index xxx on the partition xxx not exist.”。

原因分析

该问题主表索引无损坏，报错原因是由于单个分区索引记录在系统表pg_partition中与其他系统表不一致，未找到对应分区索引信息。

问题复现

1. 创建分区表a_0317，含p1，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. 创建主表与分区索引。

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

3. 查看分区表分区索引信息如下：

   1. 查看主表索引信息。

      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. 根据主表索引信息查看分区索引信息。

      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. 连接CN开启读写事务，从pg_partition系统表删除p1分区的索引信息。

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

5. 查看表定义报错与现场报错相同，问题复现：

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

处理方法

1. 删除该表索引信息。

   1	DROP INDEX a_0317_index;

2. 对该表索引进行重建。

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

3. 查看表定义无报错。

   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