适用范围

Postgresql 9 and later

问题概述

客户大约一周前数据库强制重启后，最近陆续出现查询相关对象Catalog is missing n attribute(s) for relid xxx错误

1__#$!@%!#__Pasted Graphic.png

问题原因

在执行DDL操作时，会将表的元信息记录在系统表中，例如，执行一个简单的create table test(a int,b int)操作，会在pg_class中新增一条数据来记录表信息，在pg_type中新增一条数据来记录与这张表对应的存储数据类型，在pg_depend中新增一条数据来记录pg_class中的数据和pg_type中的数据之间的依赖关系，在pg_attribute中新增九条数据来记录字段a、字段b以及表的7个默认隐藏字段。

当系统表之间的信息不匹配时，就出现了元数据不一致的场景，例如，当pg_class中记录还在，而pg_attribute中对应记录不存在时，访问该表就会报错。常见的系统表之间依赖关系如下图：

客户环境该表对应的元数据pg_attribute存在不一致

解决方案

1、查看pg_class对应oid信息的relnatts数量

select oid,* from pg_class where oid = 177258;

2、查看pg_attribute对应attrelid信息的relnatts是否连续和一致

select * from pg_attribute where attrelid = 177258;

3、修复pg_attribute的索引数据，

pg_attribute访问总是访问索引，确认索引数据是否准确 set enable_indexscan=off;

set enable_bitmapscan=off;

select * from pg_attribute where attrelid = 177258;

如果通过表查询的数据是连续和一致的，代表pg_attribute表的索引数据异常，可以直接新建vacuum full pg_attribute即可修复

4、修复pg_attribute表数据

如果通过表查询的数据不是连续和一致的，代表pg_attribute表的数据不一致。参考异常的表新建相同表结构的表名称为xxx，获取到该xxx对应的pg_class的oid数据（不用导入数据，只需要建表语句即可） select oid,* from pg_class where relname ='xxx';

获取到上面xxxx对象的oid查询新建xxx表的元数据，数据可以导出 select * from pg_attribute where attrelid = oid;

找到缺少的relnatts部分数据手动插入pg_attribute insert into pg_attribute values(oid需要修改为异常的表的oid也就是上面的177258，其他values部分直接复用不用修改......)

扩展：模拟Catalog is missing n attribute(s) for relid xxx元数据不一致

postgres=# \d t10000;
                        Table "public.t10000"
       Column        |     Type     | Collation | Nullable | Default 
---------------------+--------------+-----------+----------+---------
 oid                 | oid          |           |          | 
 relname             | name         |           |          | 
 relnamespace        | oid          |           |          | 
 reltype             | oid          |           |          | 
 reloftype           | oid          |           |          | 
 relowner            | oid          |           |          | 
 relam               | oid          |           |          | 
 relfilenode         | oid          |           |          | 
 reltablespace       | oid          |           |          | 
 relpages            | integer      |           |          | 
 reltuples           | real         |           |          | 
 relallvisible       | integer      |           |          | 
 reltoastrelid       | oid          |           |          | 
 relhasindex         | boolean      |           |          | 
 relisshared         | boolean      |           |          | 
 relpersistence      | "char"       |           |          | 
 relkind             | "char"       |           |          | 
 relnatts            | smallint     |           |          | 
 relchecks           | smallint     |           |          | 
 relhasrules         | boolean      |           |          | 
 relhastriggers      | boolean      |           |          | 
 relhassubclass      | boolean      |           |          | 
 relrowsecurity      | boolean      |           |          | 
 relforcerowsecurity | boolean      |           |          | 
 relispopulated      | boolean      |           |          | 
 relreplident        | "char"       |           |          | 
 relispartition      | boolean      |           |          | 
 relrewrite          | oid          |           |          | 
 relfrozenxid        | xid          |           |          | 
 relminmxid          | xid          |           |          | 
 relacl              | aclitem[]    |           |          | 
 reloptions          | text[]       | C         |          | 
 relpartbound        | pg_node_tree | C         |          | 
Indexes:
    "ind_t10000_oid" btree (oid)
    "ind_t10000_oid_relanme" btree (oid, relname)


postgres=# select * from pg_class where relname='t10000';
  oid   | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasrules | rel

......