昨天接到同事求助,前端应用连接某个PostgreSQL 9.6.13库上后无法创建某个Type,远程发现是因为创建Type的时候需要创建c的存储过程,在创建c的存储过程中报错,导致无法创建该Type。
错误如下:
test=# CREATE OR REPLACE FUNCTION sde.st_geometry_release()
RETURNS text
LANGUAGE c
IMMUTABLE PARALLEL SAFE STRICT
AS 'st_geometry', $function$ST_Geometry_Release$function$;
ERROR: cache lookup failed for function 1
由于extension后台也都是c的存储过程形式存在,因此也报同样的问题:
test=# create extension pageinspect;
ERROR: cache lookup failed for function 1
解决过程:
打开PostgreSQL调试功能,进行代码调试(具体调试方式可以到网上查找相关资料,在此不进行详细介绍):
发现在CreateFunction函数中,要先从pg_language字典表的lanvalidator中获取c的validator的函数号,通过查询
test=# select oid,ctid,* from pg_language;
oid | ctid | lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-------+--------+----------+----------+---------+--------------+---------------+-----------+--------------+--------
12 | (0,1) | internal | 10 | f | f | 0 | 0 | 2246 |
14 | (0,3) | sql | 10 | f | t | 0 | 0 | 2248 |
13315 | (0,4) | plpgsql | 10 | t | t | 13312 | 13313 | 13314 |
13 | (0,25) | c | 10 | f | f | 0 | 0 | 1 |
发现c的验证函数号是1,这导致后续ProcedureCreate中执行这个函数的时候找不到,因此报上面的错误。
查询了正常的库,c库的lanvalidator正常的号是2247,而且是是在0页的2的位置上,不清楚为什么被更新了这么多次。
解决方法:
test=# update pg_language set lanvalidator=2247 where oid=13;
UPDATE 1
test=# select oid,ctid,* from pg_language;
oid | ctid | lanname | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl
-------+--------+----------+----------+---------+--------------+---------------+-----------+--------------+--------
12 | (0,1) | internal | 10 | f | f | 0 | 0 | 2246 |
14 | (0,3) | sql | 10 | f | t | 0 | 0 | 2248 |
13315 | (0,4) | plpgsql | 10 | t | t | 13312 | 13313 | 13314 |
13 | (0,26) | c | 10 | f | f | 0 | 0 | 2247 |
(4 rows)
test=# select * from pg_proc where oid=2247;
-[ RECORD 1 ]---+-----------------
proname | fmgr_c_validator
pronamespace | 11
proowner | 10
prolang | 12
procost | 1
prorows | 0
provariadic | 0
protransform | -
proisagg | f
proiswindow | f
prosecdef | f
proleakproof | f
proisstrict | t
proretset | f
provolatile | s
proparallel | s
pronargs | 1
pronargdefaults | 0
prorettype | 2278
proargtypes | 26
proallargtypes |
proargmodes |
proargnames |
proargdefaults |
protrftypes |
prosrc | fmgr_c_validator
probin |
proconfig |
proacl |
test=# CREATE OR REPLACE FUNCTION sde.st_geometry_release()
test-# RETURNS text
test-# LANGUAGE c
test-# IMMUTABLE PARALLEL SAFE STRICT
test-# AS 'st_geometry', $function$ST_Geometry_Release$function$
test-# ;
CREATE FUNCTION
test=# create extension pageinspect;
CREATE EXTENSION