Horton

Thread

Commits

  1. Fix edge case in plpgsql's make_callstmt_target().

  1. BUG #18574: procedure cache does not invalidate when output parameter type mismatch

    The Post Office <noreply@postgresql.org> — 2024-08-07T05:55:24Z

    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.

  2. Re: BUG #18574: procedure cache does not invalidate when output parameter type mismatch

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-08-07T16:55:47Z

    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