Thread: BUG #18458: SQL Error [XX000]: ERROR: cache lookup failed for type 0

The following bug has been logged on the website:

Bug reference:      18458
Logged by:          Stepan Yankevych
Email address:      stepya@ukr.net
PostgreSQL version: 15.6
Operating system:   CentOS Linux release 7.9.2009 (Core)
Description:

pg 15.6 / 15.3
Citus 12.1.1 / 12.1.3
autoexplain ON

test case 

CREATE TABLE billing.test_table
(  report_id int8 NULL,
    date_id int4 NOT NULL);
 
SELECT create_distributed_table('test_table', 'report_id');

CREATE OR REPLACE PROCEDURE billing.test_delete_from(p_date_id int)
  LANGUAGE plpgsql AS
$body$
BEGIN
    DELETE FROM billing.test_table
    WHERE  date_id = p_date_id;
END;
$body$;

CALL billing.test_delete_from(20240401);
SQL Error [XX000]: ERROR: cache lookup failed for type 0
  Where: SQL statement "DELETE FROM billing.test_table
    WHERE  date_id = p_date_id"

PL/pgSQL function test_delete_from(integer) line 3 at SQL statement

BUT if we use constant instead of parameter then everything works fine 

CREATE OR REPLACE PROCEDURE billing.test_delete_from(p_date_id int)
  LANGUAGE plpgsql AS
$body$
BEGIN
    DELETE FROM billing.test_table
    WHERE  date_id = 20240401;
END;
$body$;

Also using Function instead of procedure works fine. 

Disabling auto_explain resolves the issue. 
So looks like a bug some incompatibility between Citus/autoexplain while
using delete inside procedure

On Tue, 2024-05-07 at 09:47 +0000, PG Bug reporting form wrote:
> So looks like a bug some incompatibility between Citus/autoexplain while
> using delete inside procedure

Looks like it.  You should complain to Microsoft.

Yours,
Laurenz Albe

Posted bug in citus bug tracker as well https://github.com/citusdata/citus/issues/7596 
But just to be honest I guess the issue should/could be fixed on the pg_auto_expaine side. 

Stepan Yankevych 
Senior Software Engineering Manager 

Office:  +380 44 390 5457x58840    Mobile:  +380 96 915 9551    
Uhorska St., 14, Building One     Lviv     Ukraine     Office Timezone: (UTC+03:00)     
Working Remotely     Timezone: (UTC+03:00) 
Microsoft Teams  |  LinkedIn

EPAM.COM 
confidentiality caution and disclaimer
This message is intended only for the use of the individual(s) or entity(ies) to which it is addressed and contains
confidentialinformation that may be legally privileged. If you are not the intended recipient, or authorized to read
thismessage on behalf of the intended recipient, do not read, disseminate, distribute or copy this message and please
deletethis message and destroy any printed copies. 
 

-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at> 
Sent: Tuesday, May 7, 2024 2:19 PM
To: stepya@ukr.net; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18458: SQL Error [XX000]: ERROR: cache lookup failed for type 0

On Tue, 2024-05-07 at 09:47 +0000, PG Bug reporting form wrote:
> So looks like a bug some incompatibility between Citus/autoexplain 
> while using delete inside procedure

Looks like it.  You should complain to Microsoft.

Yours,
Laurenz Albe