Hi,
On Tue, Jun 18, 2024 at 09:19:41AM +0000, Floris Van Nee wrote:
> Hi hackers,
>
> I ran into a bug with plpgsql not invalidating its cache.
> Experienced it on 15.5, but it reproduces on master.
> It involves a function that references a custom type
> which gets "replaced" by a new type with the same name.
> Reconnecting makes the error go away.
>
> Reproducible example:
>
> create type test_t as (a int);
> create table t1 (a test_t);
> insert into t1 (values ('(1)'));
> create or replace function test_f()
> returns int
> language plpgsql
> as $function$
> declare
> t t1;
> begin
> select * into t from t1;
> return (t.a).a;
> end;
> $function$
> ;
> select test_f(); -- OK
>
> -- create a new type and replace the old with the new one
> create type test_t2 as (a int);
> alter table t1 alter column a type test_t2 using a::text::test_t2;
> drop type test_t;
> alter type test_t2 rename to test_t;
> select test_f(); -- ERROR
>
> ERROR: XX000: cache lookup failed for type 16403
> CONTEXT: PL/pgSQL function test_f() line 6 at RETURN
> LOCATION: format_type_extended, format_type.c:137
>
Thanks for the report!
I can reproduce the issue on my side. We can already see another error after the
"alter table t1 alter column a type test_t2 using a::text::test_t2;":
postgres=# create type test_t2 as (a int);
alter table t1 alter column a type test_t2 using a::text::test_t2;
CREATE TYPE
ALTER TABLE
postgres=# select test_f();
ERROR: type of parameter 3 (test_t2) does not match that when preparing the plan (test_t)
CONTEXT: PL/pgSQL function test_f() line 6 at RETURN
A simpler repro based on int/bigint could be:
"
create table t2 (a int);
create or replace function test_f2()
returns int
language plpgsql
as $function$
declare
t t2;
begin
select * into t from t2;
return t.a;
end;
$function$
;
select test_f2(); -- OK
alter table t2 alter column a type bigint;
select test_f2(); -- ERROR
ERROR: type of parameter 3 (bigint) does not match that when preparing the plan (integer)
CONTEXT: PL/pgSQL function test_f2() line 6 at RETURN
"
The issue can be observed as of REL_12_STABLE.
I'm not an expert on this code area, but what I can see is that in
PlanCacheRelCallback() this gets invalidated:
(gdb) p plansource->query_string
$2 = 0x5a693ed28178 "select * from t2"
thanks to this:
(gdb) p list_member_oid(plansource->relationOids, relid)
$3 = true
While this one is not invalidated:
(gdb) p plansource->query_string
$4 = 0x5a693ed31098 "t.a"
because the relation is not part of plansource->relationOids:
(gdb) p list_member_oid(plansource->relationOids, relid)
$5 = false
If I invalidate this plansource "manually":
(gdb) set plansource->is_valid = false
(gdb) set plansource->gplan->is_valid = false
then the function does return without any error.
I'm not sure at all about what needs to be done to solve this, just sharing my
findings here.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com