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

[StepanYankevych]

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

[emelsimsek]

I debugged this a bit and it looks like a bug.

Repro Steps:

On the coordinator

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
$$
BEGIN
DELETE FROM billing.test_table
WHERE date_id = p_date_id;
END;$$;


LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze to true;

CALL billing.test_delete_from(20240401);

Note that log_analyze being true or false produces different type of failures.

Below steps throws an error as well albeit a different on from reported.

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
$$
BEGIN
DELETE FROM billing.test_table
WHERE date_id = p_date_id;
END;$$;


LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
RESET auto_explain.log_analyze;

CALL billing.test_delete_from(20240401);

[emelsimsek]

Created a PR #7598 for a possible solution.