Re: Error “cache lookup failed for function”

From: Albrecht Dreß <albrecht(dot)dress(at)arcor(dot)de>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Error “cache lookup failed for function”
Date: 2020-02-21 17:55:56
Message-ID: TVMGPZGB.WERKCQFI.IIIKKR4J@5OKNVNID.2NW2IVCE.WQWGUKU4
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Thanks in advance for your help,
Albrecht.

Attachment Content-Type Size
cache_lookup_failed.log text/x-log 9.0 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2020-02-21 18:24:44 Re: How to fix 0xC0000005 exception in Postgres 9.0
Previous Message Justin 2020-02-21 17:29:23 Re: How to fix 0xC0000005 exception in Postgres 9.0