Re: Error “cache lookup failed for function”

On 2/26/20 11:37 AM, Albrecht Dreß wrote:
> 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…

Alright, as Tom said upstream, there is not much that can be done with
information missing. Hopefully it does not happen again, but if it does
you know what needs to be captured.

>
> Thanks,
> Albrecht.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com