The following bug has been logged on the website:
Bug reference: 18577
Logged by: haiyang li
Email address: ocean_li_996@163.com
PostgreSQL version: 14.12
Operating system: centos7 5.10.84 x86_64
Description:
With the following procedures, I always get the error "ERROR: cache lookup
failed for function xxx" if calling the DO procedure in this session.
###
create or replace procedure p1(in n1 numeric, out r1 integer)
as $$
begin
call p2(n1, r1);
raise notice 'r1 of p1: %', r1;
end;
$$ language plpgsql;
create or replace procedure p2(in n1 numeric, out r1 numeric)
as $$
begin
r1 := n1;
raise notice 'r1 of p2: %', r1;
end;
$$ language plpgsql;
-- another error
do $$
declare
a numeric := 100;
b integer;
begin
call p1(a, b);
raise notice 'b is: %', b;
end;
$$ language plpgsql;
-- recreate the procedure p2
drop procedure p2;
create or replace procedure p2(in n1 numeric, out r1 numeric)
as $$
begin
r1 := n1;
raise notice 'r1 of p2: %', r1;
end;
$$ language plpgsql;
-- cache lookup failed for function xxx
do $$
declare
a numeric := 100;
b integer;
begin
call p1(a, b);
raise notice 'b is: %', b;
end;
$$ language plpgsql;
###
My analysis:
For one CallStmt, a saved plansource will be generated in
_SPI_prepare_plan(). But, plansource->invalItems of 'call p1(a, b)' only
contains p1 itself in this case.
If we do some DDL on p2, only the plansource of 'call p2(n1, r1)' is
invalidated and plansource of 'call p1(a, b)' is still valid. Then, we can
get the same error until
plansource of 'call p1(a, b)' invalidated.
In my opinion, the PROCOID reported in 'Cached lookup falied for function'
error is stable which means plansource of 'call p1(a, b)' record the
PROCOID. And we
have a chance to detect p2 proc and add it to plansource->invalItems of
'call p1(a, b)'. However, I'm not quite sure how
extract_query_dependencies() works. So,
I have no idea how to fix it. Any ideas?
regards
Haiyang Li
