From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: ocean_li_996@163.com
Subject: BUG #18577: Miss out part invalItems in saved plansource of one CallStmt
Date: Thu, 08 Aug 2024 03:19:03 +0000
Message-ID: <18577-dad0e1d043a096b5@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ocean_li_996@163.com
Subject: Re: BUG #18577: Miss out part invalItems in saved plansource of one CallStmt
In-Reply-To: <18577-dad0e1d043a096b5@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox