KingbaseES 手工修复元数据
问题现象
在数据库运维中，查询到某个表时，报错信息如下：
ERROR: catalog is missing x attribute(s) for relid xxx
或者
错误: catalog 遗失了 relid为 xxx 的 x 个属性
报错原因
这个错误通常与系统表（如 sys_class, sys_attribute 等）的元数据不一致有关。表明系统目录（catalog）中的某些信息不一致或已损坏。
处理方法
- 如果报错的对象为索引，可以选择重建进行修复，参考语句如下：
ALTER INDEX index_name REBUILD;
参考链接：
https://help.kingbase.com.cn/v9/development/sql-plsql/sql/SQL_Statements_1.html#alter-index
- 如果报错对象为表，有备份可以选择通过备份进行备份恢复；如果没有备份，可以选择通过下列方式进行修复，即修改数据库元数据信息的方式来进行修复。
下面将介绍如何通过修复元数据的方式来解决表元数据问题，示例如下
数据库报错信息
查询表报错
kingbase=# select * from test;
错误: catalog 遗失了 relid为 16385 的 1 个属性
LINE 1: select * from test;
^
analyze表报错
kingbase=# analyze test;
错误: catalog 遗失了 relid为 16385 的 1 个属性
vacuum full表报错
kingbase=# vacuum full test;
错误: catalog 遗失了 relid为 16385 的 1 个属性
说明：
对目标表test的select,analyze,vacuum full等操作都报错
问题分析思路
查看报错对象信息
kingbase=# select * from sys_class where relname='test';
oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
-------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
16385 | test | 2200 | 16387 | 0 | 16384 | 2 | 16385 | 0 | 1 | 2 | 0 | 0 | f | f | p | r | 3 | 0 | f | f | f | f | f | t | d | f | 0 | 1043 | 1 | | |
(1 row)
kingbase=# select * from sys_attribute where attrelid=16385;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attgenerated | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval
----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+---------------+---------------+---------------
16385 | tableoid | 26 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | cmax | 29 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | xmax | 28 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | cmin | 29 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | xmin | 28 | 0 | 4 | -2 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | f | | | f | t | 0 | 0 | | | |
16385 | b | 23 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | {column_id=2} | |
16385 | c | 1043 | -1 | -1 | 3 | 0 | -1 | 24 | f | x | i | f | f | f | | | f | t | 0 | 100 | | {column_id=3} | |
(8 行记录)
说明：
test表在sys_class系统视图中relnatts的值为3，表示test表有3个列；
test表在sys_attribute系统视图中attnum的值只有2个，表示test表有2个列；
从上述信息结果来看，test表在sys_attribute系统视图中只有2个列的信息，缺失了列a的信息。需要修复sys_attribute系统视图中关于test表的元信息
修复表元数据步骤示例
创建与表test表结构一样的test1表
create table test1 (a int, b int, c varchar(20));
kingbase=# select oid from sys_class where relname='test1';
oid
-------
16388
(1 row)
kingbase=# select * from sys_attribute where attrelid=16388;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attgenerated | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval
----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+---------------+---------------+---------------
16388 | tableoid | 26 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16388 | cmax | 29 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16388 | xmax | 28 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16388 | cmin | 29 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16388 | xmin | 28 | 0 | 4 | -2 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16388 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | f | | | f | t | 0 | 0 | | | |
16388 | a | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | {column_id=1} | |
16388 | b | 23 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | {column_id=2} | |
16388 | c | 1043 | -1 | -1 | 3 | 0 | -1 | 24 | f | x | i | f | f | f | | | f | t | 0 | 100 | | {column_id=3} | |
(9 rows)
说明：获取表test1的列a在sys_attribute系统视图中的信息，信息为倒数第三行。
更改sys_attribute元数据
将test表中列a的元信息插入到sys_attribute系统视图中，值为表test1的列a在sys_attribute系统视图中的值。需要注意的是，需要将attrelid的值改为test表的oid。
参考SQL如下：
insert into sys_attribute values ('16385','a','23','-1','4','1','0','-1','-1','t', 'p','i','f','f','f',' ',' ','f','t','0' ,'0','','{column_id=1}' ,null,null);
查看test元数据表
kingbase=# select * from sys_attribute where attrelid=16385;
attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attgenerated | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval
----------+----------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+--------------+------------+-------------+--------------+--------+---------------+---------------+---------------
16385 | tableoid | 26 | 0 | 4 | -6 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | cmax | 29 | 0 | 4 | -5 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | xmax | 28 | 0 | 4 | -4 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | cmin | 29 | 0 | 4 | -3 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | xmin | 28 | 0 | 4 | -2 | 0 | -1 | -1 | t | p | i | t | f | f | | | f | t | 0 | 0 | | | |
16385 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f | p | s | t | f | f | | | f | t | 0 | 0 | | | |
16385 | a | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | {column_id=1} | |
16385 | b | 23 | -1 | 4 | 2 | 0 | -1 | -1 | t | p | i | f | f | f | | | f | t | 0 | 0 | | {column_id=2} | |
16385 | c | 1043 | -1 | -1 | 3 | 0 | -1 | 24 | f | x | i | f | f | f | | | f | t | 0 | 100 | | {column_id=3} | |
(9 行记录)
说明:
sys_attribute系统表中test表的列a信息已修复（倒数第三列）。
验证
kingbase=# select * from test;
a | b | c
---+---+----------
1 | 1 | kingbase
2 | 2 | database
(2 rows)
kingbase=# analyze test;
ANALYZE
kingbase=# vacuum full test;
VACUUM
说明：
修复完成后，test表正常。
KINGBASE研究院
