Обсуждение: BUG #12832: pg_dump ERROR with temporary functions

The following bug has been logged on the website:

Bug reference:      12832
Logged by:          Marko Tiikkaja
Email address:      marko@joh.to
PostgreSQL version: 9.3.6
Operating system:   Linux
Description:

Hi,

We've occasionally been having a problem where a script does roughly this:

psql -d dbname -c "CREATE TEMPORARY FUNCTION pg_temp.foo() AS ..; <run a
query using the temporary function a number of times>"
pg_dump -d dbname

And the pg_dump call ERRORs out like this:

pg_dump: [archiver (db)] query failed: ERROR:  cache lookup failed for
function 2540046
pg_dump: [archiver (db)] query was: SELECT tableoid, oid, proname, prolang,
pronargs, proargtypes, prorettype, proacl, pronamespace,
pg_catalog.pg_get_function_identity_arguments(oid) AS proiargs,(SELECT
rolname FROM pg_catalog.pg_roles WHERE oid = proowner) AS rolname FROM
pg_proc p WHERE NOT proisagg AND (pronamespace != (SELECT oid FROM
pg_namespace WHERE nspname = 'pg_catalog')
  AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid =
'pg_proc'::regclass AND objid = p.oid AND deptype = 'i'))

The problem seems to be that the snapshot of pg_proc the pg_dump session has
sees the function, but by the time it gets to run
pg_get_function_identity_arguments() on it, it's been cleaned up by the
backend which created it.  Now this is an issue with normal functions as
well if they're dropped concurrently with a pg_dump run, but running into
problems like this when doing everything sequentially seems more astonishing
to my little brain.

Any thoughts on adding  NOT pg_is_other_temp_schema(pg_proc.pronamespace)
quals to the queries which look at pg_proc to counter this problem?
Obviously this can be worked around in client code with a strategically
placed DISCARD TEMP, but in general this seems like a POLA violation.

marko@joh.to writes:
> We've occasionally been having a problem where a script does roughly this:

> psql -d dbname -c "CREATE TEMPORARY FUNCTION pg_temp.foo() AS ..; <run a
> query using the temporary function a number of times>"
> pg_dump -d dbname

> And the pg_dump call ERRORs out like this:

> pg_dump: [archiver (db)] query failed: ERROR:  cache lookup failed for
> function 2540046
> pg_dump: [archiver (db)] query was: SELECT tableoid, oid, proname, prolang,
> pronargs, proargtypes, prorettype, proacl, pronamespace,
> pg_catalog.pg_get_function_identity_arguments(oid) AS proiargs,(SELECT
> rolname FROM pg_catalog.pg_roles WHERE oid = proowner) AS rolname FROM
> pg_proc p WHERE NOT proisagg AND (pronamespace != (SELECT oid FROM
> pg_namespace WHERE nspname = 'pg_catalog')
>   AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid =
> 'pg_proc'::regclass AND objid = p.oid AND deptype = 'i'))

I think the core of the problem here is an ill-advised decision to use
pg_get_function_identity_arguments() at all in the first query.  The
result is only used for sorting objects, and we could surely do that
another way.

            regards, tom lane