Thread: error cache lookup failed in plpgsql function
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
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