The following bug has been logged on the website:
Bug reference: 19399
Logged by: yang lei
Email address: yanglei@halodbtech.com
PostgreSQL version: 18.1
Operating system: Ubuntu 20.04.6 LTS
Description:
Hi,
I've encountered an issue that appears to be a bug.
After a composite type is recreated, the new type no longer matches the
parameter types in cached plans. This has been observed in PL/pgSQL and
PREPARE/EXECUTE scenarios; it is unclear whether other contexts are also
affected.
Test Cases：
-- Test Case 1:
create type rec as ( f int);
create or replace procedure test() as
$$
declare
a rec;
begin
a.f = 1;
raise notice '%', a;
end;
$$ language plpgsql;
call test();
drop type rec;
create type rec as (f int);
call test();
drop procedure test();
drop type rec;
ERROR: cache lookup failed for type 16499
CONTEXT: PL/pgSQL function test() line 6 at RAISE
-- Test Case 2:
create type rec1 as (f int);
create or replace procedure test1() as
$$
declare
a rec1;
b rec1;
begin
b.f = 1;
a = b;
end;
$$ language plpgsql;
call test1();
drop type rec1;
create type rec1 as (f int);
call test1();
drop procedure test1();
drop type rec1;
ERROR: cache lookup failed for type 16506
CONTEXT: PL/pgSQL function test1() line 7 at assignment
-- Test Case 3:
create type rec2 as ( f text);
create or replace procedure test2() as
$$
declare
a rec2;
begin
a.f = 1;
raise notice '%', a.f;
end;
$$ language plpgsql;
call test2();
alter type rec2 alter attribute f TYPE varchar(10);
call test2();
drop procedure test2();
drop type rec2;
ERROR: type of parameter 3 (character varying) does not match that when
preparing the plan (text)
CONTEXT: PL/pgSQL function test2() line 6 at RAISE
-- Test Case 4:
create table t (id int);
insert into t values (1);
create type rec as (f int);
PREPARE test_prepare(rec) AS
SELECT * FROM t WHERE id = $1.f;
EXECUTE test_prepare(row(1));
drop type rec;
create type rec as (f int);
EXECUTE test_prepare(row(1));
drop type rec;
drop table t;
deallocate test_prepare;
ERROR: cache lookup failed for type 16520
