Re: Error “cache lookup failed for function”

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Albrecht Dreß <albrecht(dot)dress(at)arcor(dot)de>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Error “cache lookup failed for function”
Date: 2020-02-20 18:57:07
Message-ID: 17817.1582225027@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I wrote:
> It does seem a bit annoying that something in plpgsql is apparently
> doing a fresh catalog lookup to find information that likely was
> already cached at the start of function execution. But I think that's
> a performance deficiency, not a bug per se.

I reproduced this issue locally (to avoid race conditions, I just had the
function drop itself ;-)) and traced the site of the failure to this
bit in plpgsql function exit:

/*
* Need to look up the expected result type. XXX would be
* better to cache the tupdesc instead of repeating
* get_call_result_type(), but the only easy place to save it
* is in the PLpgSQL_function struct, and that's too
* long-lived: composite types could change during the
* existence of a PLpgSQL_function.
*/
switch (get_call_result_type(fcinfo, &resultTypeId, &tupdesc))

The catalog access inside get_call_result_type is fairly essential,
because this function has OUT parameters, so its pg_proc row is the
ultimate source of truth about what it returns. We could imagine
caching the info earlier during function execution, but as the comment
says, that has its own failure modes ... and they're more important
ones in practice. So I'm afraid there's not much to be done to
improve this.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bellrose, Brian 2020-02-20 19:03:46 RE: [EXTERNAL]: Re: pglogical install errors openSUSE Leap 42.1
Previous Message Tom Lane 2020-02-20 18:32:53 Re: Error “cache lookup failed for function”