Re: Error“cache lookup failed for function”

Sorry for the late reply, I've been on a short vacation…

Am 21.02.20 21:44 schrieb(en) Adrian Klaver:
> 1) From your original post what does the below mean?:
> 
> -- add several db functions

One trigger function, plus two “normal” ones, all (yet) unused.

> -- replace a DB function:

This was the

---8<--------------------------------------------------------------------
DROP FUNCTION public.get_result2[…]
CREATE FUNCTION public.get_result2[…]
---8<--------------------------------------------------------------------

sequence triggering the error.

> 2) What do you see when you do?:
> 
> select * from pg_proc where oid = 1821571;

Unfortunately, when I saw that restarting the cluster didn't solve the issue, I re-installed it from a dump (which appears to be intact, btw), but /without/ taking a copy of the original data folder.  Thus, this information is lost, sorry.

> 3) What is the definition for retrieve_single_result()?

Ancient implementation, somewhat similar to get_result2(), but with less security and different error checking approach:

---8<--------------------------------------------------------------------
CREATE FUNCTION public.retrieve_single_result(mytaskid bigint, OUT data bytea, OUT metadata jsonb) RETURNS record
     LANGUAGE plpgsql STABLE SECURITY DEFINER
     AS $$
DECLARE
     errortext text;
BEGIN
     SELECT results.data, results.metadata, results.errortext FROM results LEFT JOIN tasks ON tasks.resultid = results.resultid WHERE taskid = mytaskid LIMIT 1 INTO data, metadata, errortext;
     IF errortext IS NOT NULL THEN
         RAISE EXCEPTION 'error result "%"', errortext;
     END IF;
END;
$$;
---8<--------------------------------------------------------------------

> 4) What does the below show?:
> 
> select proname, prosrc from pg_proc  where proname in ('retrieve_single_result', 'get_result2');

Same problem as with #2, sorry…

Thanks,
Albrecht.