SQL Error [XX000]: ERROR: cache lookup failed for type 0 with pg auto explain
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
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 as well.
So looks like a bug: some incompatibility between Citus/autoexplain while
using delete inside **procedure**
The same bug was reported on the PostgreSQL mail list
https://www.postgresql.org/message-id/18458-61332181c54e5f5c%40postgresql.org
Contributor guide
Assessment
This issue has not been assessed yet.