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.
