Subject: Two bugs around ALTER TYPE

From: Andrey Borodin <[email-masked]>
Date: Tue, 14 Apr 2026 16:53:09 +0500

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.

---

From: surya poondla <[email-masked]>
Date: Wed, 24 Jun 2026 16:56:10 -0700

Hi Andrey,

Thanks for the patch series. I was able to reproduce both bugs on master at
HEAD and verified each fix end-to-end:

A couple of points:
Bug 1: "Number of returned columns (2) does not match expected column count
(3) on the second call.
With 0002 patch, it returns the new 3-column shape as expected.

Bug 2 (with only 0004 patch): Session A returns (1,2), DDL commits after,
next call returns (1,2,99). Mechanism works as designed.

I also confirmed why glob->relationOids is the right channel rather than
glob->invalItems via record_plan_type_dependency(): ALTER TYPE ADD
ATTRIBUTE
on a composite goes through ALTER TABLE on typrelid and modifies
pg_class/pg_attribute but not the composite's pg_type row, so no TYPEOID
syscache invalidation fires
(CacheInvalidateHeapTuple in AlterDomainAddConstraint).
relcache-on-typrelid is the only channel that actually delivers, which is
what the patch hooks into.

A few comments:
1. 0003 patch alone leaves a permanently-failing TAP test (its expected
1|2|99 is only producible by 0004's lock forcing the DDL to
wait). Maybe equash 0003 patch into 0004 patch.

2. alter_table.sql comment says the fix records typrelid in "the plan's
invalItems"; it actually goes into glob->relationOids
different list, different callback (PlanCacheRelCallback vs
PlanCacheObjectCallback).

3. TAP test in 0004 captures $ddl_pid via pg_backend_pid() (lines 68-69)
but never uses it.
Either we can drop, or use it to assert the DDL session is genuinely
waiting on the typrelid in pg_locks
(granted=false on AccessExclusiveLock). The latter would directly verify
the lock instead of just observing the symptom.

Regards,
Surya Poondla