pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

error cache lookup failed in plpgsql function
2+ messages / 2 participants
[nested] [flat]

* error cache lookup failed in plpgsql function
@ 2024-06-18 09:19  Floris Van Nee <florisvannee@Optiver.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Floris Van Nee @ 2024-06-18 09:19 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: error cache lookup failed in plpgsql function
@ 2024-06-27 12:57  Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
  parent: Floris Van Nee <florisvannee@Optiver.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Bertrand Drouvot @ 2024-06-27 12:57 UTC (permalink / raw)
  To: Floris Van Nee <florisvannee@optiver.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-06-27 12:57 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-18 09:19 error cache lookup failed in plpgsql function Floris Van Nee <florisvannee@Optiver.com>
2024-06-27 12:57 ` Bertrand Drouvot <bertranddrouvot.pg@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox