citusdata / citusdata/citus

SQL Error [XX000]: ERROR: cache lookup failed for type 0 with pg auto explain

Open
#7,596 2 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.