From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | ocean_li_996(at)163(dot)com |
Subject: | BUG #18577: Miss out part invalItems in saved plansource of one CallStmt |
Date: | 2024-08-08 03:19:03 |
Message-ID: | 18577-dad0e1d043a096b5@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18577
Logged by: haiyang li
Email address: ocean_li_996(at)163(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2024-08-08 07:03:29 | Re: BUG #18575: Sometimes pg_rewind mistakenly assumes that nothing needs to be done. |
Previous Message | Thomas Munro | 2024-08-08 02:35:11 | Re: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607 |