Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJV0N-005E4F-Ux for pgsql-bugs@arkaria.postgresql.org; Tue, 18 Jun 2024 09:19:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sJV0L-00ANsq-Lu for pgsql-bugs@arkaria.postgresql.org; Tue, 18 Jun 2024 09:19:54 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJV0L-00ANoN-9g for pgsql-bugs@lists.postgresql.org; Tue, 18 Jun 2024 09:19:54 +0000 Received: from mx0b-00134001.pphosted.com ([205.220.172.129]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sJV0E-002GHf-IZ for pgsql-bugs@lists.postgresql.org; Tue, 18 Jun 2024 09:19:52 +0000 Received: from pps.filterd (m0267962.ppops.net [127.0.0.1]) by mx0a-00134001.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 45I4YDR9012771 for ; Tue, 18 Jun 2024 09:19:42 GMT DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=optiver.com; h= content-type:date:from:message-id:mime-version:subject:to; s= mail; bh=jtE/L3edYEGGgub/4Wmvw9ZKzkHIhmKWbgzl3lbtFhU=; b=gtTJ5Bc R12tw524ShmP4ORdYYhEi3McmZa6VCKkBHbyEhhK33oL2o0r66H4jLtQMfd2vN3/ cpXs0hY14LoqwVDUDALUBzVxm8CstkimT/iMAIgHxwQR23wyUeL8dV+h6QoIvZRx D3doDk/lWeKa7WRq3L3fHILHraz8fSGccqIi3eYAHVoVKefq2ml9UujkZxIR767u dhqk0ten/azP94dOuZpH0v3EcK+9U4razHenRfq5nS+nflgMnju1C3r+b/16nUbX UBUVj+AsuFyS+K95cLH0AGeKkpwa1Hb4PgoSmALkTAhghi2cvOSzR9wFDHKCfPVK Ci4ewZTtcOixDlg== Received: from opammb1266.comp.optiver.com ([195.246.114.78]) by mx0a-00134001.pphosted.com (PPS) with ESMTPS id 3ys0tmk638-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Tue, 18 Jun 2024 09:19:42 +0000 (GMT) Received: from opammb1267.comp.optiver.com (100.68.11.12) by opammb1266.comp.optiver.com (100.68.11.26) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1118.26; Tue, 18 Jun 2024 11:19:41 +0200 Received: from opammb1267.comp.optiver.com ([fe80::5b6f:1976:98cd:68a7]) by opammb1267.comp.optiver.com ([fe80::5b6f:1976:98cd:68a7%13]) with mapi id 15.02.1118.026; Tue, 18 Jun 2024 11:19:41 +0200 From: Floris Van Nee To: "pgsql-bugs@lists.postgresql.org" Subject: error cache lookup failed in plpgsql function Thread-Topic: error cache lookup failed in plpgsql function Thread-Index: AdrBYCRxKvVnt0w4TeaFk5zceFUwGw== Date: Tue, 18 Jun 2024 09:19:41 +0000 Message-ID: <148fde845019499db7d14776f46b3ad4@Optiver.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [100.68.31.122] Content-Type: multipart/alternative; boundary="_000_148fde845019499db7d14776f46b3ad4Optivercom_" MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.28.16 definitions=2024-06-18_02,2024-06-17_01,2024-05-17_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_148fde845019499db7d14776f46b3ad4Optivercom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 --_000_148fde845019499db7d14776f46b3ad4Optivercom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi hackers,

 

I ran into a bug with plpgsql not invalidating its c= ache.

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 ne= w 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 164= 03

CONTEXT:  PL/pgSQL function test_f() line 6 at = RETURN

LOCATION:  format_type_extended, format_type.c:= 137

 

--_000_148fde845019499db7d14776f46b3ad4Optivercom_--