Обсуждение: BUG #18432: Polymorphic, table-returning PL/pgSQL function fails with an erroneous "schema mismatch" error
BUG #18432: Polymorphic, table-returning PL/pgSQL function fails with an erroneous "schema mismatch" error
От
PG Bug reporting form
Дата:
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;