pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18574: procedure cache does not invalidate when output parameter type mismatch
2+ messages / 2 participants
[nested] [flat]
* BUG #18574: procedure cache does not invalidate when output parameter type mismatch
@ 2024-08-07 05:55 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2024-08-07 05:55 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: hysong0101@163.com
The following bug has been logged on the website:
Bug reference: 18574
Logged by: Song Hongyu
Email address: hysong0101@163.com
PostgreSQL version: 14.12
Operating system: CentOS 7
Description:
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;
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;
The reproduced script is as above. There is one out parameter in the two
procedures. In the initial state, we make their types do not match and call
the fisrt procedure which internally calls the other procedure. Then we will
get "procedure parameter "r1" is an output parameter but corresponding
argument is not writable". After this, we drop procedure + create procedure
for p2. In this time, we re-call the fisrt procedure，we will get "cache
lookup failed for function".
We can only close the current connection to avoid this problem.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #18574: procedure cache does not invalidate when output parameter type mismatch
@ 2024-08-07 16:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2024-08-07 16:55 UTC (permalink / raw)
To: hysong0101@163.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> The reproduced script is as above. There is one out parameter in the two
> procedures. In the initial state, we make their types do not match and call
> the fisrt procedure which internally calls the other procedure. Then we will
> get "procedure parameter "r1" is an output parameter but corresponding
> argument is not writable". After this, we drop procedure + create procedure
> for p2. In this time, we re-call the fisrt procedure，we will get "cache
> lookup failed for function".
Fixed, thanks for the report!
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-08-07 16:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-07 05:55 BUG #18574: procedure cache does not invalidate when output parameter type mismatch PG Bug reporting form <noreply@postgresql.org>
2024-08-07 16:55 ` Tom Lane <tgl@sss.pgh.pa.us>
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