The following bug has been logged on the website:
Bug reference: 18432
Logged by: Narek Galstyan
Email address: narek@lantern.dev
PostgreSQL version: 16.2
Operating system: Linux (via official docker image)
Description:
Hi all,
I think I am running into some kind of catalog cache invalidation issue.
Say I have a PL/pgSQL function that takes in an anyelement polymorphic
argument and the argument resolves into a table of arbitrary structure.
The function returns a table type and returns a filtered subset of the
argument table.
The function does not directly implement the logic but instead calls another
function.
When I run ALTER TABLE on the table I have called the function with, the
function stops working until some kind of event (e.g. creating a new schema
that has no overlap with the function)
To reproduce: (tested on pg15 and pg16)
DROP schema if exists issue4 cascade;
CREATE schema issue4;
-- The next two functions define some internal table-polimorphic function,
and the pablic wrapper around it
CREATE FUNCTION issue4.identity_internal(t anyelement) RETURNS TABLE ("row"
anyelement) AS $$
DECLARE
query_base text;
BEGIN
query_base := format('SELECT * FROM %s ', pg_typeof(t));
RETURN QUERY EXECUTE query_base;
END $$ LANGUAGE plpgsql;
CREATE FUNCTION issue4.identity(t anyelement) RETURNS TABLE ("row"
anyelement) AS $$
DECLARE
query_base text;
BEGIN
query_base := format('SELECT * FROM %s ', pg_typeof(t));
RETURN QUERY SELECT * FROM issue4.identity_internal(t);
END $$ LANGUAGE plpgsql;
-- Create tables to call the table-polimorphic function on
CREATE TABLE issue4.test_table(id SERIAL PRIMARY KEY, vec real[]);
INSERT INTO issue4.test_table(vec) VALUES ('{1,2,3}'), ('{4,5,6}'),
('{7,8,9}');
-- the issue is not triggered when the table is modified before the first
invocation of the function
-- The line below demosntrates that there is no issue calling the
polymorphic functions after an ALTER TABLE
-- if there were no prior calls to the function
ALTER TABLE issue4.test_table ADD COLUMN vec23 real[];
-- succeeds!
SELECT * FROM issue4.identity(CAST(NULL as "issue4"."test_table"));
\echo --------------------- CORE OF THE ISSUE
------------------------------------------
-- Now, let's modify the 2-column table, andd add a column to it after the
function was called
ALTER TABLE issue4.test_table ADD COLUMN vec2 real[];
UPDATE issue4.test_table SET vec2 = vec;
\set ON_ERROR_STOP off
-- this fails with:
-- psql:../pg_anyelement_issue.sql:34: ERROR: structure of query does
not match function result type
-- DETAIL: Number of returned columns (3) does not match expected
column count (4).
-- CONTEXT: SQL statement "SELECT * FROM issue4.identity_internal(t)"
-- PL/pgSQL function issue4.identity(anyelement) line 6 at RETURN
QUERY
SELECT * FROM issue4.identity(CAST(NULL as "issue4"."test_table"));
\set ON_ERROR_STOP on
\echo ^^^^^^^^^^^^^^^^^^^^^ CORE OF THE ISSUE
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
CREATE SCHEMA issue4_dummy;
DROP SCHEMA issue4_dummy;
-- This (EXACT SAME QUERY AS ABOVE) now succeeds!? after creating and
dropping a schema
SELECT * FROM issue4.identity(CAST(NULL as "issue4"."test_table"));
-- cleanup
DROP SCHEMA issue4 CASCADE;