I recently moved a Postgres function app_user_id
from an old, custom extension called intpair
into a new, custom extension called heap_exts
. This worked well except for machines I restored from a Postgres backup.
Here's the error:
db=> select app_user_id(1, 2);
ERROR: could not access file "$libdir/intpair": No such file or directory
intpair
is not installed and heap_exts
is installed as shown by the output of \dx
:
-[ RECORD 3 ]-------------------------------------------------------------
Name | heap_exts
Version | 1.0
Schema | public
Description |
-- No mention of intpair
The function app_user_id
is listed in the system catalogs as being owned by 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
I initially migrated the function from the old extension intpair
into the new extension heap_exts
with the following code:
-- 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);
However, for the new machine restored via backup from the old machine, I created the app_user_id
function directly because the old extension intpair
no longer exists:
CREATE FUNCTION app_user_id(BIGINT, BIGINT) RETURNS app_user_id
AS 'MODULE_PATHNAME', 'app_user_id_make' LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;
Why is Postgres trying to look in the intpair
extension for the app_user_id
function instead of looking in heap_exts
?
Best Answer
It sounds like you still have (or had) an entry in
pg_proc
forapp_user_id(bigint, bigint)
which points to the old function definition (probin
would be$libdir/intpair
).Assuming there is an entry there, I'd guess it was somehow not properly associated with the old
intpair
extension (maybe manually added, or the code pre-dates PostgreSQL's extension functionality and the function was missed fromCREATE EXTENSION FROM unpackaged
or something) so was not removed when the extension was dropped, so is still present in your restored instance - but the associated extension library file no longer exists, of course.The question is whether that
pg_proc
entry has somehow ended up associated with your new extension, or you have two entries inpg_proc
for the function.What does this modified version of your above query report?