catalog is missing 10 attribute(s)錯誤的解決辦法一例

查詢該表報錯：

test=# select count(*) from test;

2021-04-28 11:41:35.783 HKT [36138] ERROR:  catalog is missing 10 attribute(s) for relid 30804 at character 22

2021-04-28 11:41:35.783 HKT [36138] STATEMENT:  select count(*) from test;

ERROR:  XX000: catalog is missing 10 attribute(s) for relid 30804

LINE 1: select count(*) from test;

                             ^

LOCATION:  RelationBuildTupleDesc, relcache.c:581

Time: 0.707 ms

一般報這種錯就是表有損壞，可以嘗試從備份中恢復，或者直接刪除，但 刪除該表發現報錯：

test=# drop table if exists test;

2021-04-28 11:39:50.670 HKT [36138] ERROR:  cache lookup failed for attribute 9 of relation 30804

2021-04-28 11:39:50.670 HKT [36138] STATEMENT:  drop table if exists test;

ERROR:  XX000: cache lookup failed for attribute 9 of relation 30804

LOCATION:  get_relid_attribute_name, lsyscache.c:808

Time: 0.870 ms

問題解決思路：

找到test表對應的oid值，然後根據oid值在pg_class和pg_depend表裡刪除對應的後設資料資訊。

查test表的oid值:

test=# select oid from pg_class where relname='test';

  oid  

-------

 30804

(1 row)

Time: 0.649 ms

刪除pg_class中對應test的後設資料資訊：

11:41:42 (postgres@127.0.0.1:5432)test=# delete from pg_class where oid =30804;

DELETE 1

Time: 1.189 ms

刪除pg_class中對應test的後設資料資訊：

11:41:55 (postgres@127.0.0.1:5432)test=# delete from pg_depend where objid = 30804;

DELETE 1

test表已正常從資料庫裡刪除：

test=# select count(*) from test;

2021-04-28 11:52:06.483 HKT [36138] ERROR:  relation "test" does not exist at character 22

2021-04-28 11:52:06.483 HKT [36138] STATEMENT:  select count(*) from test;

ERROR:  42P01: relation "test" does not exist

LINE 1: select count(*) from test;

                             ^

LOCATION:  parserOpenTable, parse_relation.c:1180

Time: 0.547 ms

備註：

pg_class後設資料表記錄表和幾乎所有具有列或者像表的東西。這包括索引（但還要參見pg_index）、序列（但還要參見pg_sequence）、檢視、物化檢視、組合型別和TOAST表。

pg_depend

目錄pg_depend後設資料表記錄資料庫物件之間的依賴關係。這些資訊允許DROP命令查詢必須被DROP CASCADE刪除的其他物件，

或者在DROP RESTRICT情況下阻止刪除。