Postgresql
調試 Postgres 為什麼要為函式使用已解除安裝的擴展
我最近將一個 Postgres 函式
app_user_id
從一個舊的自定義副檔名intpair
移到了一個新的自定義副檔名heap_exts
. 除了我從 Postgres 備份恢復的機器之外,這執行良好。這是錯誤:
db=> select app_user_id(1, 2); ERROR: could not access file "$libdir/intpair": No such file or directory
intpair
未安裝並heap_exts
已安裝,如以下輸出所示\dx
:-[ RECORD 3 ]------------------------------------------------------------- Name | heap_exts Version | 1.0 Schema | public Description | -- No mention of intpair
該功能
app_user_id
在系統目錄中列為歸heap_exts
:SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema FROM pg_catalog.pg_extension AS e INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid) INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid) INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace) INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace) WHERE d.deptype = 'e' AND e.extname IN ('heap_exts') AND p.proname = 'app_user_id' ORDER BY 1, 3;
-[ RECORD 1 ]---------- extname | heap_exts extschema | public proname | app_user_id proschema | public
我最初使用以下程式碼將該功能從舊擴展遷移
intpair
到新擴展:heap_exts
-- version 0.1 in heap_exts--0.1.sql ALTER EXTENSION intpair DROP FUNCTION app_user_id(bigint, bigint); ALTER EXTENSION heap_exts ADD FUNCTION app_user_id(bigint, bigint);
但是,對於通過舊機器備份恢復的新機器,我
app_user_id
直接創建了函式,因為舊擴展intpair
不再存在:CREATE FUNCTION app_user_id(BIGINT, BIGINT) RETURNS app_user_id AS 'MODULE_PATHNAME', 'app_user_id_make' LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
為什麼 Postgres 試圖查看函式的
intpair
擴展app_user_id
而不是查看heap_exts
?
為什麼 Postgres 試圖在 intpair 擴展中查找 app_user_id 函式而不是在 heap_exts 中查找?
聽起來您仍然有(或有)一個指向舊函式定義(將是)的條目
pg_proc
。app_user_id(bigint, bigint)``probin``$libdir/intpair
假設那裡有一個條目,我猜它與舊
intpair
副檔名沒有正確關聯(可能是手動添加的,或者程式碼早於 PostgreSQL 的擴展功能並且該功能被遺漏了CREATE EXTENSION FROM unpackaged
或其他東西)所以當副檔名已被刪除,因此仍存在於您恢復的實例中 - 當然,相關的副檔名庫文件不再存在。問題是該
pg_proc
條目是否以某種方式最終與您的新擴展相關聯,或者您有兩個pg_proc
用於該功能的條目。您的上述查詢報告的這個修改版本是什麼?
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema, p.probin FROM pg_catalog.pg_extension AS e INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid) INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid) INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace) INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace) WHERE d.deptype = 'e' AND p.proname = 'app_user_id' ORDER BY 1, 3;