kingbase 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.