Re: "ERROR: cache lookup failed for function %d" while dropping function from another session

On Mon, 9 Aug 2021 at 11:07, Mahendra Singh Thalor <mahi6run(at)gmail(dot)com>
wrote:
>
> Hi,
> I am able to hit "ERROR: cache lookup failed for function %d" while I am
dropping function from another session.
>
> Reproducible steps are as(I tried on current head d8a75b1308b133502ae3):
>
> Step 1) Fire below query from session-1 to create a function:
>
> create function fun1(int) returns record as ' DECLARE retval RECORD;
BEGIN SELECT INTO retval 5, 10, 15; RETURN retval;
> END;' language plpgsql;
>
> Step 2) Attach PID of session-1 into gdb and set a breakpoint into
internal_get_result_type and then press c.
>
> (gdb) b internal_get_result_type Breakpoint 1 at 0x5565dd4f3ce4: file
funcapi.c, line 325. (gdb) c
> Continuing.
>
> Step 3) Fire below select command from session-1:
>
> SELECT * FROM fun1(1500) AS (a int, b int, c int);
>
> Step 3) Open session-2 and drop the function:
>
> psql (15devel) Type "help" for help. postgres=# drop function fun1; DROP
FUNCTION
> postgres=#
>
> Step 4) Press 'c' in gdb for 2 times and we will get an error in the 1st
session.
>
> (gdb) c Continuing. Breakpoint 1, internal_get_result_type (funcid=16384,
call_expr=0x5565de885af0, rsinfo=0x0, resultTypeId=0x7ffc94572608,
resultTupleDesc=0x7ffc94572628) at funcapi.c:325 (gdb) c
> Continuing.
>
> postgres=# SELECT * FROM fun1(1500) AS (a int, b int, c int);
> ERROR: cache lookup failed for function 16384
>
> Call stack of error:
>>
>> Breakpoint 2, errfinish (filename=0x55e0b8907c6f "funcapi.c",
lineno=336, funcname=0x55e0b89083a0 <__func__.23015>
"internal_get_result_type") at elog.c:515
>> 515 ErrorData *edata = &errordata[errordata_stack_depth];
>> (gdb) bt
>> #0 errfinish (filename=0x55e0b8907c6f "funcapi.c", lineno=336,
funcname=0x55e0b89083a0 <__func__.23015> "internal_get_result_type") at
elog.c:515
>> #1 0x000055e0b86abdaf in internal_get_result_type (funcid=16384,
call_expr=0x55e0ba9e2ad0, rsinfo=0x0, resultTypeId=0x7ffcb7ca4918,
resultTupleDesc=0x7ffcb7ca4938)
>> at funcapi.c:336
>> #2 0x000055e0b86ab6d0 in get_expr_result_type (expr=0x55e0ba9e2ad0,
resultTypeId=0x7ffcb7ca4918, resultTupleDesc=0x7ffcb7ca4938) at
funcapi.c:230
>> #3 0x000055e0b7fef027 in ExecInitFunctionScan (node=0x55e0ba9e3cd0,
estate=0x55e0ba9d2fe0, eflags=16) at nodeFunctionscan.c:370
>> #4 0x000055e0b7fbb408 in ExecInitNode (node=0x55e0ba9e3cd0,
estate=0x55e0ba9d2fe0, eflags=16) at execProcnode.c:254
>> #5 0x000055e0b7faa1d5 in InitPlan (queryDesc=0x55e0ba926f50, eflags=16)
at execMain.c:936
>> #6 0x000055e0b7fa84b6 in standard_ExecutorStart
(queryDesc=0x55e0ba926f50, eflags=16) at execMain.c:263
>> #7 0x000055e0b7fa7fc7 in ExecutorStart (queryDesc=0x55e0ba926f50,
eflags=0) at execMain.c:143
>> #8 0x000055e0b83ba8de in PortalStart (portal=0x55e0ba968870,
params=0x0, eflags=0, snapshot=0x0) at pquery.c:512
>> #9 0x000055e0b83ae112 in exec_simple_query (query_string=0x55e0ba906760
"SELECT * FROM fun1(1500) AS (a int, b int, c int);") at postgres.c:1175
>> #10 0x000055e0b83b7a86 in PostgresMain (argc=1, argv=0x7ffcb7ca4f70,
dbname=0x55e0ba932770 "postgres", username=0x55e0ba932748 "mahendrathalor")
at postgres.c:4488
>> #11 0x000055e0b825161b in BackendRun (port=0x55e0ba929730) at
postmaster.c:4519
>> #12 0x000055e0b8250971 in BackendStartup (port=0x55e0ba929730) at
postmaster.c:4241
>> #13 0x000055e0b8248a1b in ServerLoop () at postmaster.c:1758
>> #14 0x000055e0b8247c18 in PostmasterMain (argc=3, argv=0x55e0ba8fee70)
at postmaster.c:1430
>> #15 0x000055e0b808dbe9 in main (argc=3, argv=0x55e0ba8fee70) at
main.c:199
>
>
> cache lookup failed errors are never an expected behavior(Thanks Robert
Hass for your opinion) so I think we should fix this error.
>
> I haven't debugged it so I will debug it and will post my findings in the
coming days.
>

I googled it and found that we already discussed this issue in another
thread.

Link:
https://www.postgresql.org/message-id/flat/NX26BD7O.SQ6NCQDL.Z4KK4FBY%40HXCHZXMY.EBBFSJQH.OUMOBSJN

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
>

--
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com