Re: error cache lookup failed in plpgsql function

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