Re: Error “cache lookup failed for function” - Mailing list pgsql-general

On 2/21/20 9:55 AM, Albrecht Dreß wrote:
> Am 20.02.20 21:41 schrieb(en) Adrian Klaver:
>> It would be nice to know what:
> [snip]
>> represented in:
> 
> Dropping and re-creating the function is actually the last operation in 
> the script.  The function is /very/ simple (just a wrapper to hide all 
> internals from "agent" clients):
> 
> ---8<--------------------------------------------------------------------
> DROP FUNCTION public.get_result2(mytaskid bigint, OUT data bytea, OUT 
> metadata jsonb, OUT errortext text, OUT vanished boolean);
> CREATE FUNCTION public.get_result2(mytaskid bigint, OUT data bytea, OUT 
> metadata jsonb, OUT errortext text, OUT vanished boolean) RETURNS record
>      LANGUAGE plpgsql STABLE SECURITY DEFINER
>      SET search_path TO 'public', 'pg_temp'
>      AS $$
> BEGIN
>      SELECT r.data, r.metadata, r.errortext FROM results r INNER JOIN 
> tasks USING(resultid) WHERE taskid = mytaskid LIMIT 1 INTO data, 
> metadata, errortext;
>      SELECT COUNT(*) = 0 FROM tasks WHERE taskid = mytaskid INTO vanished;
> END;
> $$;
> ALTER FUNCTION public.get_result2(mytaskid bigint, OUT data bytea, OUT 
> metadata jsonb, OUT errortext text, OUT vanished boolean) OWNER TO manager;
> REVOKE ALL ON FUNCTION public.get_result2(mytaskid bigint, OUT data 
> bytea, OUT metadata jsonb, OUT errortext text, OUT vanished boolean) 
> FROM PUBLIC;
> GRANT ALL ON FUNCTION public.get_result2(mytaskid bigint, OUT data 
> bytea, OUT metadata jsonb, OUT errortext text, OUT vanished boolean) TO 
> "agent";
> COMMIT;
> ---8<--------------------------------------------------------------------
> 
>> The Postgres logs during and after restart might provide some info.
>>
>> Also the errors thrown when accessing the other function.
> 
> I attach the (slightly stripped down; I don't want to post ~100k…) log, 
> starting with the very first error at 13:39:59.302 UTC.  Prior to that 
> line are *no* errors.  I added a few [comments].
> 
> At 13:39:59.484 the error message changes, referring to an ancient 
> function “retrieve_single_result()” which (according to the person who 
> wrote the “agent” client) is *not* called.  The clients try periodically 
> poll “get_result2()”.
> 
> At 13:42:00 the “systemctl restart” has been initiated.  At 13:42:02 the 
> database has been stopped and is restarted immediately, revealing one 
> misconfigured client (should be harmless), but the cache lookup error 
> persists.

Hmm. More questions:

1) From your original post what does the below mean?:

-- add several db functions
-- replace a DB function:

2) What do you see when you do?:

select * from pg_proc where oid = 1821571;

3) What is the definition for retrieve_single_result()?

4) What does the below show?:

select proname, prosrc from pg_proc  where proname in 
('retrieve_single_result', 'get_result2');

> 
> Thanks in advance for your help,
> Albrecht.


-- 
Adrian Klaver
adrian.klaver@aklaver.com