Albrecht =?iso-8859-1?b?RHJl3w==?= <albrecht.dress@arcor.de> writes:
> I tried to update the running server by executing the following SQL update script using psql:
> ---8<-----------------------------------------------
> BEGIN;
> -- add a column to an existing table
> -- add a new table
> -- add several db functions
> -- replace a DB function:
> DROP FUNCTION public.get_result2(mytaskid bigint, OUT data bytea, OUT metadata jsonb, OUT errortext text, OUT
vanishedboolean);
> CREATE FUNCTION public.get_result2(mytaskid bigint, OUT data bytea, OUT metadata jsonb, OUT errortext text, OUT
vanishedboolean) RETURNS record
> […]
> COMMIT;
> ---8<-----------------------------------------------
> About ~350 clients were connected to the server when I ran the above script, a few of them using, inter alia, the
functionget_result2() which ought to be replaced.
> Immediately after running the script, the log was filled with errors
> ---8<-----------------------------------------------
> ERROR: cache lookup failed for function 1821571
> CONTEXT: PL/pgSQL function get_result2(bigint) while casting return value to function's return type
> STATEMENT: SELECT data, metadata, errortext, vanished FROM get_result2(26671107)
> ---8<-----------------------------------------------
This is, actually, not very surprising. You dropped the old function
while clients were using it. The new function is a completely unrelated
object, even if it happens to have the same name.
What you should have done was CREATE OR REPLACE FUNCTION, which would
have preserved the object's identity.
It does seem a bit annoying that something in plpgsql is apparently
doing a fresh catalog lookup to find information that likely was
already cached at the start of function execution. But I think that's
a performance deficiency, not a bug per se.
regards, tom lane
