On Mon, 9 Aug 2021 at 11:07, Mahendra Singh Thalor <mahi6...@gmail.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
