Thread: BUG #18574: procedure cache does not invalidate when output parameter type mismatch
BUG #18574: procedure cache does not invalidate when output parameter type mismatch
From
PG Bug reporting form
Date:
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.
Re: BUG #18574: procedure cache does not invalidate when output parameter type mismatch
From
Tom Lane
Date:
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