From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Albrecht Dreß <albrecht(dot)dress(at)arcor(dot)de>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Error “cache lookup failed for function” |
Date: | 2020-02-21 20:44:36 |
Message-ID: | b75ac0b5-f265-0c00-d95e-6a9e79bf852d@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2020-02-21 20:50:01 | Re: How to fix 0xC0000005 exception in Postgres 9.0 |
Previous Message | Michael Lewis | 2020-02-21 20:24:16 | Re: Logical replication lag in seconds |