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.