public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey Borodin <[email protected]>
To: PostgreSQL mailing lists <[email protected]>
Subject: Two bugs around ALTER TYPE
Date: Tue, 14 Apr 2026 16:53:09 +0500
Message-ID: <[email protected]> (raw)
Hi,
Users reported a bug involving ALTER TYPE/TABLE and set-returning PL/pgSQL
functions, so I took a stab at fixing them.
Bug 1: ALTER TYPE/TABLE ADD COLUMN does not invalidate cached SETOF plans
When a PL/pgSQL function executes RETURN QUERY against a SETOF function
returning a named composite type, the SPI plan for that inner query is
cached. If ALTER TYPE ... ADD ATTRIBUTE (or ALTER TABLE ... ADD COLUMN
when the table's rowtype is the return type) is executed afterward, the
cached plan is not marked stale and the next call raises:
ERROR: structure of query does not match function result type
DETAIL: Number of returned columns (3) does not match expected column
count (2).
Reproduction (single session):
CREATE TYPE planinv_ct AS (a int, b int);
CREATE TABLE planinv_tbl (a int, b int);
INSERT INTO planinv_tbl VALUES (1, 2);
CREATE FUNCTION planinv_srf() RETURNS SETOF planinv_ct
LANGUAGE sql STABLE SECURITY DEFINER AS $$
SELECT * FROM planinv_tbl $$;
CREATE FUNCTION planinv_caller() RETURNS SETOF planinv_ct
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY SELECT r.* FROM planinv_srf() r;
END; $$;
SELECT * FROM planinv_caller(); -- warms up plan cache
ALTER TYPE planinv_ct ADD ATTRIBUTE c int;
ALTER TABLE planinv_tbl ADD COLUMN c int DEFAULT 99;
SELECT * FROM planinv_caller(); -- ERROR without fix, (1,2,99) with fix
Root cause:
When a plan is finalised, setrefs.c records OIDs of referenced relations
in glob->relationOids so that relcache invalidations can mark it stale.
For RTE_FUNCTION nodes only the function OID is tracked. If the
function's declared return type is a named composite, ALTER TYPE (or
ALTER TABLE for table rowtypes) updates pg_class for that composite
without touching the function OID, so the cached plan never sees the
invalidation.
Note: SECURITY DEFINER is required in the reproducer to prevent the
planner from inlining planinv_srf() into the caller; the bug affects any
non-inlined SETOF function returning a named composite type.
Fix:
Add a helper add_function_rte_relation_deps() in setrefs.c that, for
each RTE_FUNCTION node, resolves the typrelid of the function's return
type via typeOrDomainTypeRelid() and appends it to glob->relationOids.
Call it from add_rte_to_flat_rtable(), flatten_rtes_walker(), and
extract_query_dependencies_walker(). ALTER TYPE/TABLE then triggers
relcache invalidation and seems to force a re-plan.
Bug 2: Concurrent ALTER TYPE/TABLE mid-execution causes mismatch in PL/pgSQL
I was looking for workarounds and proposed the user to add something
that would invalidate plan when they alter a type:
CREATE OR REPLACE FUNCTION planinv_srf()
Users replied that they already do it, and it does not help.
Together we found out that a race window exists inside a single PL/pgSQL call.
The function enters with a 2-column tuple descriptor;
a concurrent transaction commits ALTER TYPE ADD ATTRIBUTE plus CREATE OR
REPLACE FUNCTION while RETURN QUERY is still running. The inner SRF
is replanned with 3 columns, but the tstoreReceiver was already set up
for 2, giving the same error mid-execution.
The race requires precise timing and is reproduced reliably only with
injection points (TAP test included in the patch series). In brief:
Session A: SELECT * FROM planinv_caller(); -- suspended just before
-- RETURN QUERY executes
Session B: BEGIN;
ALTER TYPE planinv_ct ADD ATTRIBUTE c int;
CREATE OR REPLACE FUNCTION planinv_srf() ... 3 columns ...
COMMIT;
Session A resumes: ERROR
The same race exists with ALTER TABLE when the return type is a table
rowtype that is not itself scanned by the function body. When the
function does scan the same table it returns, ALTER TABLE is naturally
blocked by the AccessShareLock already held by the scan; but for
standalone composite types there is no such automatic protection.
Root cause:
plpgsql_estate_setup() captures the return type descriptor with no lock
preventing ALTER TYPE from modifying the composite type's pg_class row
between estate setup and the RETURN QUERY re-plan. Within a single SQL
statement the row shape should be stable, analogous to snapshot
semantics for catalog changes.
Fix:
In plpgsql_estate_setup(), when the function is a SETOF returning a
named composite type (retisset && retistuple), resolve typrelid via
typeOrDomainTypeRelid() and call LockRelationOid(typrelid,
AccessShareLock). The lock is released at transaction end.
This mirrors how table scans acquire AccessShareLock to pin the schema
for statement duration. ALTER TYPE/TABLE requires AccessExclusiveLock
on the same relid, so it blocks behind the in-progress call.
Thanks to Victor Popov for reporting and adapting single-session test case
for the race condition.
Regards, Andrey Borodin.
Attachments:
[application/octet-stream] 0001-Add-regression-test-for-SETOF-composite-invalidation.patch (5.1K, 2-0001-Add-regression-test-for-SETOF-composite-invalidation.patch)
download | inline diff:
From 287c3aacc201787521525165e9a19566cda86a43 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Tue, 7 Apr 2026 15:27:43 +0500
Subject: [PATCH 1/4] Add regression test for SETOF composite invalidation
Add an alter_table regression test for ALTER TYPE ADD ATTRIBUTE on a named
composite returned by a SECURITY DEFINER SQL SRF used from PL/pgSQL
RETURN QUERY.
Before the fix, a warmed plan can stay stale and raise a column-count
mismatch. The test captures that behavior.
---
src/test/regress/expected/alter_table.out | 47 +++++++++++++++++++++++
src/test/regress/sql/alter_table.sql | 38 ++++++++++++++++++
2 files changed, 85 insertions(+)
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index dad9d36937e..b8136666f05 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3348,6 +3348,53 @@ ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
CREATE TYPE test_type_empty AS ();
DROP TYPE test_type_empty;
--
+-- Cached plans for queries that scan a set-returning function returning a
+-- named composite type must be invalidated when ALTER TYPE ADD ATTRIBUTE
+-- widens that type. The fix records the composite's underlying relation OID
+-- (pg_type.typrelid) in the plan's invalItems so the relcache invalidation
+-- broadcast by ALTER TYPE reaches the cached plan.
+--
+-- Without the fix the plan is never marked stale. A PL/pgSQL function whose
+-- RETURN QUERY calls such an SRF raises "structure of query does not match
+-- function result type" because its SPI plan still expects the old column
+-- count while the SRF (independently replanned via table relcache) already
+-- returns the new one.
+--
+-- SECURITY DEFINER prevents inlining; without inlining the outer plan holds
+-- only the proc OID in invalItems, so only the typrelid dependency added by
+-- the fix triggers its invalidation.
+--
+CREATE TYPE planinv_ct AS (a int, b int);
+CREATE TABLE planinv_tbl (a int, b int);
+INSERT INTO planinv_tbl VALUES (1, 2);
+CREATE FUNCTION planinv_srf() RETURNS SETOF planinv_ct
+ LANGUAGE sql STABLE SECURITY DEFINER AS $$ SELECT * FROM planinv_tbl $$;
+CREATE FUNCTION planinv_caller() RETURNS SETOF planinv_ct LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN QUERY SELECT r.* FROM planinv_srf() r;
+END; $$;
+-- Warm up the plan cache.
+SELECT * FROM planinv_caller();
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+ALTER TYPE planinv_ct ADD ATTRIBUTE c int;
+ALTER TABLE planinv_tbl ADD COLUMN c int DEFAULT 99;
+-- Without the fix: ERROR: structure of query does not match function result type
+-- With the fix: the plan is invalidated and the function returns all columns.
+SELECT * FROM planinv_caller();
+ a | b | c
+---+---+----
+ 1 | 2 | 99
+(1 row)
+
+DROP FUNCTION planinv_caller();
+DROP FUNCTION planinv_srf();
+DROP TABLE planinv_tbl;
+DROP TYPE planinv_ct;
+--
-- typed tables: OF / NOT OF
--
CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index f5f13bbd3e7..f4216ceb2e1 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2055,6 +2055,44 @@ ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
CREATE TYPE test_type_empty AS ();
DROP TYPE test_type_empty;
+--
+-- Cached plans for queries that scan a set-returning function returning a
+-- named composite type must be invalidated when ALTER TYPE ADD ATTRIBUTE
+-- widens that type. The fix records the composite's underlying relation OID
+-- (pg_type.typrelid) in the plan's invalItems so the relcache invalidation
+-- broadcast by ALTER TYPE reaches the cached plan.
+--
+-- Without the fix the plan is never marked stale. A PL/pgSQL function whose
+-- RETURN QUERY calls such an SRF raises "structure of query does not match
+-- function result type" because its SPI plan still expects the old column
+-- count while the SRF (independently replanned via table relcache) already
+-- returns the new one.
+--
+-- SECURITY DEFINER prevents inlining; without inlining the outer plan holds
+-- only the proc OID in invalItems, so only the typrelid dependency added by
+-- the fix triggers its invalidation.
+--
+CREATE TYPE planinv_ct AS (a int, b int);
+CREATE TABLE planinv_tbl (a int, b int);
+INSERT INTO planinv_tbl VALUES (1, 2);
+CREATE FUNCTION planinv_srf() RETURNS SETOF planinv_ct
+ LANGUAGE sql STABLE SECURITY DEFINER AS $$ SELECT * FROM planinv_tbl $$;
+CREATE FUNCTION planinv_caller() RETURNS SETOF planinv_ct LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN QUERY SELECT r.* FROM planinv_srf() r;
+END; $$;
+-- Warm up the plan cache.
+SELECT * FROM planinv_caller();
+ALTER TYPE planinv_ct ADD ATTRIBUTE c int;
+ALTER TABLE planinv_tbl ADD COLUMN c int DEFAULT 99;
+-- Without the fix: ERROR: structure of query does not match function result type
+-- With the fix: the plan is invalidated and the function returns all columns.
+SELECT * FROM planinv_caller();
+DROP FUNCTION planinv_caller();
+DROP FUNCTION planinv_srf();
+DROP TABLE planinv_tbl;
+DROP TYPE planinv_ct;
+
--
-- typed tables: OF / NOT OF
--
--
2.50.1 (Apple Git-155)
[application/octet-stream] 0002-Track-RTE_FUNCTION-composite-rowtype-dependencies.patch (4.1K, 3-0002-Track-RTE_FUNCTION-composite-rowtype-dependencies.patch)
download | inline diff:
From 5e22013d511aa07d3d2b2f70f7842d37e70ea02e Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Tue, 7 Apr 2026 15:28:06 +0500
Subject: [PATCH 2/4] Track RTE_FUNCTION composite rowtype dependencies
Record typrelid dependencies for named composite outputs of RTE_FUNCTION
nodes, so relcache invalidation from ALTER TYPE marks cached plans stale.
This keeps prepared and SPI plans in sync with composite rowshape changes.
---
doc/src/sgml/ref/alter_type.sgml | 9 +++++++
src/backend/optimizer/plan/setrefs.c | 36 ++++++++++++++++++++++++++++
2 files changed, 45 insertions(+)
diff --git a/doc/src/sgml/ref/alter_type.sgml b/doc/src/sgml/ref/alter_type.sgml
index 025a3ee48f5..72b046afd98 100644
--- a/doc/src/sgml/ref/alter_type.sgml
+++ b/doc/src/sgml/ref/alter_type.sgml
@@ -242,6 +242,15 @@ ALTER TYPE <replaceable class="parameter">name</replaceable> SET ( <replaceable
and/or alter the type of several attributes in a single command.
</para>
+ <para>
+ Changing a composite type's attributes also invalidates cached plans for
+ queries that scan <literal>SETOF</literal> functions returning that
+ composite type (including through a domain over the composite), even when
+ no <command>CREATE OR REPLACE FUNCTION</command> is run. This matches the
+ relcache invalidation already sent for the composite type's cataloged
+ rowtype.
+ </para>
+
<para>
You must own the type to use <command>ALTER TYPE</command>.
To change the schema of a type, you must also have
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index ff0e875f2a2..b7e387b7a2a 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -26,6 +26,7 @@
#include "optimizer/subselect.h"
#include "optimizer/tlist.h"
#include "parser/parse_relation.h"
+#include "parser/parse_type.h"
#include "rewrite/rewriteManip.h"
#include "tcop/utility.h"
#include "utils/syscache.h"
@@ -500,6 +501,34 @@ add_rtes_to_flat_rtable(PlannerInfo *root, bool recursing)
}
}
+/*
+ * Record relcache dependencies for RTE_FUNCTION entries whose declared result
+ * is a named composite type (or a domain over one). ALTER TYPE {ADD | DROP |
+ * ALTER} ATTRIBUTE updates the composite type's pg_class row without changing
+ * OIDs of dependent functions, so plans must be rebuilt when the composite
+ * rowtype changes. (Compare extract_query_dependencies_walker, which must
+ * stay in sync.)
+ */
+static void
+add_function_rte_relation_deps(PlannerGlobal *glob, const RangeTblEntry *rte)
+{
+ ListCell *lc;
+
+ Assert(rte->rtekind == RTE_FUNCTION);
+
+ foreach(lc, rte->functions)
+ {
+ RangeTblFunction *rtfunc = (RangeTblFunction *) lfirst(lc);
+ Oid typid;
+ Oid typrelid;
+
+ typid = exprType(rtfunc->funcexpr);
+ typrelid = typeOrDomainTypeRelid(typid);
+ if (OidIsValid(typrelid))
+ glob->relationOids = lappend_oid(glob->relationOids, typrelid);
+ }
+}
+
/*
* Extract RangeTblEntries from a subquery that was never planned at all
*/
@@ -529,6 +558,8 @@ flatten_rtes_walker(Node *node, flatten_rtes_walker_context *cxt)
if (rte->rtekind == RTE_RELATION ||
(rte->rtekind == RTE_SUBQUERY && OidIsValid(rte->relid)))
add_rte_to_flat_rtable(cxt->glob, cxt->query->rteperminfos, rte);
+ else if (rte->rtekind == RTE_FUNCTION)
+ add_function_rte_relation_deps(cxt->glob, rte);
return false;
}
if (IsA(node, Query))
@@ -567,6 +598,9 @@ add_rte_to_flat_rtable(PlannerGlobal *glob, List *rteperminfos,
{
RangeTblEntry *newrte;
+ if (rte->rtekind == RTE_FUNCTION)
+ add_function_rte_relation_deps(glob, rte);
+
/* flat copy to duplicate all the scalar fields */
newrte = palloc_object(RangeTblEntry);
memcpy(newrte, rte, sizeof(RangeTblEntry));
@@ -3800,6 +3834,8 @@ extract_query_dependencies_walker(Node *node, PlannerInfo *context)
(rte->rtekind == RTE_NAMEDTUPLESTORE && OidIsValid(rte->relid)))
context->glob->relationOids =
lappend_oid(context->glob->relationOids, rte->relid);
+ else if (rte->rtekind == RTE_FUNCTION)
+ add_function_rte_relation_deps(context->glob, rte);
}
/* And recurse into the query's subexpressions */
--
2.50.1 (Apple Git-155)
[application/octet-stream] 0003-Add-TAP-test-for-plpgsql-RETURN-QUERY-DDL-race.patch (5.0K, 4-0003-Add-TAP-test-for-plpgsql-RETURN-QUERY-DDL-race.patch)
download | inline diff:
From 63a188921074a0a5cf359687e14f8b46b98d2264 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Tue, 14 Apr 2026 14:29:14 +0500
Subject: [PATCH 3/4] Add TAP test for plpgsql RETURN QUERY DDL race
Add an injection-point TAP reproducer for concurrent ALTER TYPE +
CREATE OR REPLACE FUNCTION during an in-progress RETURN QUERY call.
The test captures the mid-statement rowshape mismatch scenario.
---
src/pl/plpgsql/src/pl_exec.c | 6 ++
src/test/modules/test_misc/meson.build | 1 +
.../t/012_plpgsql_composite_replan_race.pl | 96 +++++++++++++++++++
3 files changed, 103 insertions(+)
create mode 100644 src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 65b0fd0790f..844f8080dc0 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -41,6 +41,7 @@
#include "utils/builtins.h"
#include "utils/datum.h"
#include "utils/fmgroids.h"
+#include "utils/injection_point.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -3594,6 +3595,11 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,
/* There might be some tuples in the tuplestore already */
tcount = tuplestore_tuple_count(estate->tuple_store);
+ /*
+ * Test-only pause point for RETURN QUERY race conditions.
+ */
+ INJECTION_POINT("plpgsql-return-query-before-exec", NULL);
+
/*
* Set up DestReceiver to transfer results directly to tuplestore,
* converting rowtype if necessary. DestReceiver lives in mcontext.
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 1b25d98f7f3..a4f4f57134b 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -20,6 +20,7 @@ tests += {
't/009_log_temp_files.pl',
't/010_index_concurrently_upsert.pl',
't/011_lock_stats.pl',
+ 't/012_plpgsql_composite_replan_race.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl b/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl
new file mode 100644
index 00000000000..e151409457d
--- /dev/null
+++ b/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl
@@ -0,0 +1,96 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+if ($ENV{enable_injection_points} ne 'yes')
+{
+ plan skip_all => 'Injection points not supported by this build';
+}
+
+my $node = PostgreSQL::Test::Cluster->new('plpgsql_composite_replan_race');
+$node->init;
+$node->start;
+
+if (!$node->check_extension('injection_points'))
+{
+ plan skip_all => 'Extension injection_points not installed';
+}
+
+$node->safe_psql('postgres', 'CREATE EXTENSION injection_points;');
+
+$node->safe_psql('postgres', q[
+CREATE TYPE planinv_ct AS (a int, b int);
+CREATE TABLE planinv_tbl (a int, b int);
+INSERT INTO planinv_tbl VALUES (1, 2);
+CREATE FUNCTION planinv_srf() RETURNS SETOF planinv_ct
+ LANGUAGE sql STABLE SECURITY DEFINER AS $$
+ SELECT a, b FROM planinv_tbl
+ $$;
+CREATE FUNCTION planinv_caller() RETURNS SETOF planinv_ct LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN QUERY SELECT r.* FROM planinv_srf() r;
+END;
+$$;
+]);
+
+# Warm up expression/plan caches first.
+is($node->safe_psql('postgres', 'SELECT * FROM planinv_caller();'), '1|2',
+ 'warmup call returns initial row shape');
+
+my $backend2 = $node->background_psql('postgres', on_error_stop => 0);
+$backend2->query_safe(q[
+SELECT injection_points_set_local();
+SELECT injection_points_attach('plpgsql-return-query-before-exec', 'wait');
+]);
+
+$backend2->query_until(
+ qr/race_started/, q[
+\echo race_started
+BEGIN;
+SELECT * FROM planinv_caller();
+\echo race_done
+]);
+
+$node->poll_query_until('postgres', q[
+SELECT EXISTS (
+ SELECT 1
+ FROM pg_stat_activity
+ WHERE wait_event_type = 'InjectionPoint'
+ AND wait_event = 'plpgsql-return-query-before-exec'
+);
+]) or die 'backend2 did not reach injection point in time';
+
+$node->safe_psql('postgres', q[
+BEGIN;
+ALTER TYPE planinv_ct ADD ATTRIBUTE c int;
+CREATE OR REPLACE FUNCTION planinv_srf() RETURNS SETOF planinv_ct
+ LANGUAGE sql STABLE SECURITY DEFINER AS $$
+ SELECT a, b, 99 FROM planinv_tbl
+ $$;
+COMMIT;
+]);
+
+$node->safe_psql('postgres',
+ "SELECT injection_points_wakeup('plpgsql-return-query-before-exec');");
+
+my $out = $backend2->query_until(qr/race_done/, q[]);
+like($out, qr/^1\|2\|99$/m,
+ 'concurrent ALTER TYPE + CREATE OR REPLACE does not break RETURN QUERY');
+is($backend2->{stderr}, '',
+ 'no tuple shape mismatch reported by RETURN QUERY');
+
+ok($backend2->quit);
+
+$node->safe_psql('postgres', q[
+DROP FUNCTION planinv_caller();
+DROP FUNCTION planinv_srf();
+DROP TABLE planinv_tbl;
+DROP TYPE planinv_ct;
+]);
+
+done_testing();
--
2.50.1 (Apple Git-155)
[application/octet-stream] 0004-Lock-SETOF-composite-return-type-during-plpgsql-exec.patch (3.8K, 5-0004-Lock-SETOF-composite-return-type-during-plpgsql-exec.patch)
download | inline diff:
From 58cda5c1b4bbf9f5a9452d971f365d17163bd9f2 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Tue, 14 Apr 2026 14:29:21 +0500
Subject: [PATCH 4/4] Lock SETOF composite return type during plpgsql execution
For SETOF PL/pgSQL functions returning named composite tuples, acquire
AccessShareLock on the return type's typrelid at function entry.
This keeps rowshape stable for the whole execution under concurrent
ALTER TYPE and avoids mid-statement mismatch errors.
---
src/pl/plpgsql/src/pl_exec.c | 15 +++++++++++++
.../t/012_plpgsql_composite_replan_race.pl | 21 +++++++++++++++----
2 files changed, 32 insertions(+), 4 deletions(-)
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 844f8080dc0..44c2f47506b 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -34,6 +34,7 @@
#include "parser/parse_coerce.h"
#include "parser/parse_type.h"
#include "plpgsql.h"
+#include "storage/lmgr.h"
#include "storage/proc.h"
#include "tcop/cmdtag.h"
#include "tcop/pquery.h"
@@ -4038,6 +4039,20 @@ plpgsql_estate_setup(PLpgSQL_execstate *estate,
estate->retistuple = func->fn_retistuple;
estate->retisset = func->fn_retset;
+ /*
+ * Keep named composite SETOF return types stable for the whole function
+ * execution. This prevents concurrent ALTER TYPE from changing rowshape
+ * between statement setup and RETURN QUERY execution.
+ */
+ if (estate->retisset && estate->retistuple)
+ {
+ Oid typrelid;
+
+ typrelid = typeOrDomainTypeRelid(estate->fn_rettype);
+ if (OidIsValid(typrelid))
+ LockRelationOid(typrelid, AccessShareLock);
+ }
+
estate->readonly_func = func->fn_readonly;
estate->atomic = true;
diff --git a/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl b/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl
index e151409457d..0c5f169000a 100644
--- a/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl
+++ b/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl
@@ -51,7 +51,6 @@ SELECT injection_points_attach('plpgsql-return-query-before-exec', 'wait');
$backend2->query_until(
qr/race_started/, q[
\echo race_started
-BEGIN;
SELECT * FROM planinv_caller();
\echo race_done
]);
@@ -65,7 +64,12 @@ SELECT EXISTS (
);
]) or die 'backend2 did not reach injection point in time';
-$node->safe_psql('postgres', q[
+my $ddl_backend = $node->background_psql('postgres', on_error_stop => 0);
+my $ddl_pid = $ddl_backend->query_safe('SELECT pg_backend_pid()');
+chomp($ddl_pid);
+$ddl_backend->query_until(
+ qr/ddl_started/, q[
+\echo ddl_started
BEGIN;
ALTER TYPE planinv_ct ADD ATTRIBUTE c int;
CREATE OR REPLACE FUNCTION planinv_srf() RETURNS SETOF planinv_ct
@@ -73,19 +77,28 @@ CREATE OR REPLACE FUNCTION planinv_srf() RETURNS SETOF planinv_ct
SELECT a, b, 99 FROM planinv_tbl
$$;
COMMIT;
+\echo ddl_done
]);
$node->safe_psql('postgres',
"SELECT injection_points_wakeup('plpgsql-return-query-before-exec');");
my $out = $backend2->query_until(qr/race_done/, q[]);
-like($out, qr/^1\|2\|99$/m,
- 'concurrent ALTER TYPE + CREATE OR REPLACE does not break RETURN QUERY');
+like($out, qr/^1\|2$/m,
+ 'in-progress statement keeps old row shape across concurrent DDL');
is($backend2->{stderr}, '',
'no tuple shape mismatch reported by RETURN QUERY');
ok($backend2->quit);
+my $ddl_out = $ddl_backend->query_until(qr/ddl_done/, q[]);
+is($ddl_backend->{stderr}, '', 'concurrent DDL session completed cleanly');
+like($ddl_out, qr/ddl_done/m, 'DDL proceeds after RETURN QUERY finishes');
+ok($ddl_backend->quit);
+
+is($node->safe_psql('postgres', 'SELECT * FROM planinv_caller();'), '1|2|99',
+ 'subsequent statement sees new composite row shape');
+
$node->safe_psql('postgres', q[
DROP FUNCTION planinv_caller();
DROP FUNCTION planinv_srf();
--
2.50.1 (Apple Git-155)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Two bugs around ALTER TYPE
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox