PostgreSQL cache lookup failed for type XXXX 錯誤

文前說明

作為碼農中的一員，需要不斷的學習，我工作之餘將一些分析總結和學習筆記寫成部落格與大家一起交流，也希望採用這種方式記錄自己的學習之旅。

本文僅供學習交流使用，侵權必刪。
不用於商業目的，轉載請註明出處。

錯誤資訊

• 執行 pg_dump 命令備份，提示 cache lookup failed for type... 錯誤。

2018-03-08 00:19:14 4285: Start of engine-backup mode backup scope all file /root/ovirt-engine.bak
2018-03-08 00:19:14 4285: Backing up:
2018-03-08 00:19:14 4285: Generating pgpass
2018-03-08 00:19:15 4285: Creating temp folder /tmp/engine-backup.Z8RvsbYGGl/tar
2018-03-08 00:19:15 4285: - Files
2018-03-08 00:19:15 4285: Backing up files to /tmp/engine-backup.Z8RvsbYGGl/tar/files
2018-03-08 00:19:15 4285: - Engine database 'engine'
2018-03-08 00:19:15 4285: Backing up database to /tmp/engine-backup.Z8RvsbYGGl/tar/db/engine_backup.db
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  cache lookup failed for type 222222
pg_dump: The command was: SELECT proretset, prosrc, probin, pg_catalog.pg_get_function_arguments(oid) AS funcargs, pg_catalog.pg_get_function_identity_arguments(oid) AS funciargs, pg_catalog.pg_get_function_result(oid) AS funcresult, proiswindow, provolatile, proisstrict, prosecdef, proconfig, procost, prorows, (SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) AS lanname FROM pg_catalog.pg_proc WHERE oid = '237534'::pg_catalog.oid
2018-03-08 00:19:16 4285: FATAL: Database engine backup failed

錯誤分析

• 根據提示資訊上網查詢資料，大概瞭解了錯誤原因。參考資料如下。

1. https://www.postgresql.org/message-id/AANLkTik0Nfyw%2B1QV7nD5z6GtkrF92w9AvitHYfgibTJb@mail.gmail.com
2. http://www.postgresql-archive.org/Cache-lookup-failure-for-index-during-pg-dump-td2125326.html

• 根據上面日誌中的提示資訊，可以確定 222222 這個 ID 號，在 pg_type 表中無法查詢到。

# select * from pg_type where oid = '222222';
 typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typarray | typinput | typoutput | typreceive | typsend | typmodin | typmodout | typanalyze 
| typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typdefaultbin | typdefault 
---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+----------+----------+-----------+------------+---------+----------+-----------+------------
+----------+------------+------------+-------------+-----------+----------+---------------+------------
(0 rows)

• 分析 pg_proc 表結構。檢視 pg_proc 表中對 pg_type.oid 引用的相關欄位。

# select provariadic,prorettype,proallargtypes,proargtypes from pg_proc where provariadic = '222222' or prorettype = '222222' or proallargtypes @> '{222222}' or proargtypes @> '222222';
 provariadic | prorettype | proallargtypes | proargtypes  
-------------+------------+----------------+--------------
           0 |     222222 |                | 2950 2950 16
(1 row)

• 查詢到 prorettype 欄位中使用到了 222222 這個 ID。

解決方案

• 將 pg_proc 表的 proname 欄位也輸出，確認函式名稱。

# select proname,provariadic,prorettype,proallargtypes,proargtypes from pg_proc where provariadic = '222222' or prorettype = '222222' or proallargtypes @> '{222222}' or proargtypes @> '222222';
           proname            | provariadic | prorettype | proallargtypes | proargtypes  
------------------------------+-------------+------------+----------------+--------------
 getuserpermissionsbyentityid |           0 |     222222 |                | 2950 2950 16
(1 row)

• 根據 getuserpermissionsbyentityid 函式名稱，在資料庫中查詢到該函式。

  • 檢視到該函式的返回型別為 permissions_view。

• 在 pg_type 表中查詢型別名稱為 permissions_view 的型別是否存在。

# select oid from pg_type where typname = 'permissions_view';
  oid   
--------
 236728
(1 row)

• 查詢到 permissions_view 型別的 oid 號為 236728。（如果未查詢到需要先手動 insert 這條資料，再查詢出 oid）。

• 修改 pg_proc 表中 prorettype 欄位的引用 ID 為 236728。

# update pg_proc set prorettype = '236728' where proname = 'getuserpermissionsbyentityid';
UPDATE 1

• 再次進行備份成功。

2018-03-08 00:49:49 4677: Start of engine-backup mode backup scope all file /root/ovirt-engine.bak
2018-03-08 00:49:49 4677: Backing up:
2018-03-08 00:49:49 4677: Generating pgpass
2018-03-08 00:49:49 4677: Creating temp folder /tmp/engine-backup.4n8mAdmak4/tar
2018-03-08 00:49:49 4677: - Files
2018-03-08 00:49:49 4677: Backing up files to /tmp/engine-backup.4n8mAdmak4/tar/files
2018-03-08 00:49:50 4677: - Engine database 'engine'
2018-03-08 00:49:50 4677: Backing up database to /tmp/engine-backup.4n8mAdmak4/tar/db/engine_backup.db
2018-03-08 00:49:51 4677: Creating md5sum at /tmp/engine-backup.4n8mAdmak4/tar/md5sum
2018-03-08 00:49:53 4677: Packing into file '/root/ovirt-engine.bak'
2018-03-08 00:49:53 4677: Creating tarball /root/ovirt-engine.bak
2018-03-08 00:50:06 4677: Done.