Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wCcKd-002A6D-0f for pgsql-bugs@arkaria.postgresql.org; Tue, 14 Apr 2026 11:53:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wCcKb-00BbTv-1i for pgsql-bugs@arkaria.postgresql.org; Tue, 14 Apr 2026 11:53:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wCcKb-00BbTm-0B for pgsql-bugs@lists.postgresql.org; Tue, 14 Apr 2026 11:53:26 +0000 Received: from forwardcorp1b.mail.yandex.net ([178.154.239.136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1wCcKZ-000000011AM-17R6 for pgsql-bugs@lists.postgresql.org; Tue, 14 Apr 2026 11:53:25 +0000 Received: from mail-nwsmtp-smtp-corp-main-34.sas.yp-c.yandex.net (mail-nwsmtp-smtp-corp-main-34.sas.yp-c.yandex.net [IPv6:2a02:6b8:c24:fa2:0:640:41ee:0]) by forwardcorp1b.mail.yandex.net (Yandex) with ESMTPS id 57678807BE for ; Tue, 14 Apr 2026 14:53:21 +0300 (MSK) Received: from smtpclient.apple (unknown [2a02:6bf:8080:55a::1:20]) by mail-nwsmtp-smtp-corp-main-34.sas.yp-c.yandex.net (smtpcorp/Yandex) with ESMTPSA id KrJe820M3W20-Esg41UGi; Tue, 14 Apr 2026 14:53:21 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex-team.ru; s=default; t=1776167601; bh=N8ETdZ0c3tKW1ZZa0FHa5hLcdT9Drq5X2Fb1o3tQzZc=; h=To:Date:Message-Id:From:Subject; b=su57QVHwr05vFIzx9bnpOfdg20fEUVtBTAHuh6CRoeCNDru6yu+tDG4kVCqBQjf+c Llr6pMR9fYpeobzGsjxLiGP3MvxROuE9S5sB5XBQZtmpygTaphVdAdQvhfGQDsFdzY +/z/0bI6JaTiCMd7jTxTz2L0HsHU47AO9gNRDBBc= Authentication-Results: mail-nwsmtp-smtp-corp-main-34.sas.yp-c.yandex.net; dkim=pass header.i=@yandex-team.ru From: Andrey Borodin Content-Type: multipart/mixed; boundary="Apple-Mail=_D41EBC5B-99EB-462D-A4BB-D63D6CD88C83" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.500.181\)) Subject: Two bugs around ALTER TYPE Message-Id: <39977E94-40DD-4176-A191-CE3D120968B2@yandex-team.ru> Date: Tue, 14 Apr 2026 16:53:09 +0500 To: PostgreSQL mailing lists X-Mailer: Apple Mail (2.3864.500.181) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_D41EBC5B-99EB-462D-A4BB-D63D6CD88C83 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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. --Apple-Mail=_D41EBC5B-99EB-462D-A4BB-D63D6CD88C83 Content-Disposition: attachment; filename=0001-Add-regression-test-for-SETOF-composite-invalidation.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="0001-Add-regression-test-for-SETOF-composite-invalidation.patch" Content-Transfer-Encoding: quoted-printable =46rom=20287c3aacc201787521525165e9a19566cda86a43=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Andrey=20Borodin=20=0ADate:=20= Tue,=207=20Apr=202026=2015:27:43=20+0500=0ASubject:=20[PATCH=201/4]=20= Add=20regression=20test=20for=20SETOF=20composite=20invalidation=0A=0A= Add=20an=20alter_table=20regression=20test=20for=20ALTER=20TYPE=20ADD=20= ATTRIBUTE=20on=20a=20named=0Acomposite=20returned=20by=20a=20SECURITY=20= DEFINER=20SQL=20SRF=20used=20from=20PL/pgSQL=0ARETURN=20QUERY.=0A=0A= Before=20the=20fix,=20a=20warmed=20plan=20can=20stay=20stale=20and=20= raise=20a=20column-count=0Amismatch.=20=20The=20test=20captures=20that=20= behavior.=0A---=0A=20src/test/regress/expected/alter_table.out=20|=2047=20= +++++++++++++++++++++++=0A=20src/test/regress/sql/alter_table.sql=20=20=20= =20=20=20|=2038=20++++++++++++++++++=0A=202=20files=20changed,=2085=20= insertions(+)=0A=0Adiff=20--git=20= a/src/test/regress/expected/alter_table.out=20= b/src/test/regress/expected/alter_table.out=0Aindex=20= dad9d36937e..b8136666f05=20100644=0A---=20= a/src/test/regress/expected/alter_table.out=0A+++=20= b/src/test/regress/expected/alter_table.out=0A@@=20-3348,6=20+3348,53=20= @@=20ALTER=20TYPE=20test_type3=20DROP=20ATTRIBUTE=20a,=20ADD=20ATTRIBUTE=20= b=20int;=0A=20CREATE=20TYPE=20test_type_empty=20AS=20();=0A=20DROP=20= TYPE=20test_type_empty;=0A=20--=0A+--=20Cached=20plans=20for=20queries=20= that=20scan=20a=20set-returning=20function=20returning=20a=0A+--=20named=20= composite=20type=20must=20be=20invalidated=20when=20ALTER=20TYPE=20ADD=20= ATTRIBUTE=0A+--=20widens=20that=20type.=20=20The=20fix=20records=20the=20= composite's=20underlying=20relation=20OID=0A+--=20(pg_type.typrelid)=20= in=20the=20plan's=20invalItems=20so=20the=20relcache=20invalidation=0A= +--=20broadcast=20by=20ALTER=20TYPE=20reaches=20the=20cached=20plan.=0A= +--=0A+--=20Without=20the=20fix=20the=20plan=20is=20never=20marked=20= stale.=20=20A=20PL/pgSQL=20function=20whose=0A+--=20RETURN=20QUERY=20= calls=20such=20an=20SRF=20raises=20"structure=20of=20query=20does=20not=20= match=0A+--=20function=20result=20type"=20because=20its=20SPI=20plan=20= still=20expects=20the=20old=20column=0A+--=20count=20while=20the=20SRF=20= (independently=20replanned=20via=20table=20relcache)=20already=0A+--=20= returns=20the=20new=20one.=0A+--=0A+--=20SECURITY=20DEFINER=20prevents=20= inlining;=20without=20inlining=20the=20outer=20plan=20holds=0A+--=20only=20= the=20proc=20OID=20in=20invalItems,=20so=20only=20the=20typrelid=20= dependency=20added=20by=0A+--=20the=20fix=20triggers=20its=20= invalidation.=0A+--=0A+CREATE=20TYPE=20planinv_ct=20AS=20(a=20int,=20b=20= int);=0A+CREATE=20TABLE=20planinv_tbl=20(a=20int,=20b=20int);=0A+INSERT=20= INTO=20planinv_tbl=20VALUES=20(1,=202);=0A+CREATE=20FUNCTION=20= planinv_srf()=20RETURNS=20SETOF=20planinv_ct=0A+=20=20LANGUAGE=20sql=20= STABLE=20SECURITY=20DEFINER=20AS=20$$=20SELECT=20*=20FROM=20planinv_tbl=20= $$;=0A+CREATE=20FUNCTION=20planinv_caller()=20RETURNS=20SETOF=20= planinv_ct=20LANGUAGE=20plpgsql=20AS=20$$=0A+BEGIN=0A+=20=20RETURN=20= QUERY=20SELECT=20r.*=20FROM=20planinv_srf()=20r;=0A+END;=20$$;=0A+--=20= Warm=20up=20the=20plan=20cache.=0A+SELECT=20*=20FROM=20planinv_caller();=0A= +=20a=20|=20b=20=0A+---+---=0A+=201=20|=202=0A+(1=20row)=0A+=0A+ALTER=20= TYPE=20planinv_ct=20ADD=20ATTRIBUTE=20c=20int;=0A+ALTER=20TABLE=20= planinv_tbl=20ADD=20COLUMN=20c=20int=20DEFAULT=2099;=0A+--=20Without=20= the=20fix:=20ERROR:=20structure=20of=20query=20does=20not=20match=20= function=20result=20type=0A+--=20With=20the=20fix:=20the=20plan=20is=20= invalidated=20and=20the=20function=20returns=20all=20columns.=0A+SELECT=20= *=20FROM=20planinv_caller();=0A+=20a=20|=20b=20|=20c=20=20=0A= +---+---+----=0A+=201=20|=202=20|=2099=0A+(1=20row)=0A+=0A+DROP=20= FUNCTION=20planinv_caller();=0A+DROP=20FUNCTION=20planinv_srf();=0A+DROP=20= TABLE=20planinv_tbl;=0A+DROP=20TYPE=20planinv_ct;=0A+--=0A=20--=20typed=20= tables:=20OF=20/=20NOT=20OF=0A=20--=0A=20CREATE=20TYPE=20tt_t0=20AS=20(z=20= inet,=20x=20int,=20y=20numeric(8,2));=0Adiff=20--git=20= a/src/test/regress/sql/alter_table.sql=20= b/src/test/regress/sql/alter_table.sql=0Aindex=20= f5f13bbd3e7..f4216ceb2e1=20100644=0A---=20= a/src/test/regress/sql/alter_table.sql=0A+++=20= b/src/test/regress/sql/alter_table.sql=0A@@=20-2055,6=20+2055,44=20@@=20= ALTER=20TYPE=20test_type3=20DROP=20ATTRIBUTE=20a,=20ADD=20ATTRIBUTE=20b=20= int;=0A=20CREATE=20TYPE=20test_type_empty=20AS=20();=0A=20DROP=20TYPE=20= test_type_empty;=0A=20=0A+--=0A+--=20Cached=20plans=20for=20queries=20= that=20scan=20a=20set-returning=20function=20returning=20a=0A+--=20named=20= composite=20type=20must=20be=20invalidated=20when=20ALTER=20TYPE=20ADD=20= ATTRIBUTE=0A+--=20widens=20that=20type.=20=20The=20fix=20records=20the=20= composite's=20underlying=20relation=20OID=0A+--=20(pg_type.typrelid)=20= in=20the=20plan's=20invalItems=20so=20the=20relcache=20invalidation=0A= +--=20broadcast=20by=20ALTER=20TYPE=20reaches=20the=20cached=20plan.=0A= +--=0A+--=20Without=20the=20fix=20the=20plan=20is=20never=20marked=20= stale.=20=20A=20PL/pgSQL=20function=20whose=0A+--=20RETURN=20QUERY=20= calls=20such=20an=20SRF=20raises=20"structure=20of=20query=20does=20not=20= match=0A+--=20function=20result=20type"=20because=20its=20SPI=20plan=20= still=20expects=20the=20old=20column=0A+--=20count=20while=20the=20SRF=20= (independently=20replanned=20via=20table=20relcache)=20already=0A+--=20= returns=20the=20new=20one.=0A+--=0A+--=20SECURITY=20DEFINER=20prevents=20= inlining;=20without=20inlining=20the=20outer=20plan=20holds=0A+--=20only=20= the=20proc=20OID=20in=20invalItems,=20so=20only=20the=20typrelid=20= dependency=20added=20by=0A+--=20the=20fix=20triggers=20its=20= invalidation.=0A+--=0A+CREATE=20TYPE=20planinv_ct=20AS=20(a=20int,=20b=20= int);=0A+CREATE=20TABLE=20planinv_tbl=20(a=20int,=20b=20int);=0A+INSERT=20= INTO=20planinv_tbl=20VALUES=20(1,=202);=0A+CREATE=20FUNCTION=20= planinv_srf()=20RETURNS=20SETOF=20planinv_ct=0A+=20=20LANGUAGE=20sql=20= STABLE=20SECURITY=20DEFINER=20AS=20$$=20SELECT=20*=20FROM=20planinv_tbl=20= $$;=0A+CREATE=20FUNCTION=20planinv_caller()=20RETURNS=20SETOF=20= planinv_ct=20LANGUAGE=20plpgsql=20AS=20$$=0A+BEGIN=0A+=20=20RETURN=20= QUERY=20SELECT=20r.*=20FROM=20planinv_srf()=20r;=0A+END;=20$$;=0A+--=20= Warm=20up=20the=20plan=20cache.=0A+SELECT=20*=20FROM=20planinv_caller();=0A= +ALTER=20TYPE=20planinv_ct=20ADD=20ATTRIBUTE=20c=20int;=0A+ALTER=20TABLE=20= planinv_tbl=20ADD=20COLUMN=20c=20int=20DEFAULT=2099;=0A+--=20Without=20= the=20fix:=20ERROR:=20structure=20of=20query=20does=20not=20match=20= function=20result=20type=0A+--=20With=20the=20fix:=20the=20plan=20is=20= invalidated=20and=20the=20function=20returns=20all=20columns.=0A+SELECT=20= *=20FROM=20planinv_caller();=0A+DROP=20FUNCTION=20planinv_caller();=0A= +DROP=20FUNCTION=20planinv_srf();=0A+DROP=20TABLE=20planinv_tbl;=0A+DROP=20= TYPE=20planinv_ct;=0A+=0A=20--=0A=20--=20typed=20tables:=20OF=20/=20NOT=20= OF=0A=20--=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_D41EBC5B-99EB-462D-A4BB-D63D6CD88C83 Content-Disposition: attachment; filename=0002-Track-RTE_FUNCTION-composite-rowtype-dependencies.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="0002-Track-RTE_FUNCTION-composite-rowtype-dependencies.patch" Content-Transfer-Encoding: quoted-printable =46rom=205e22013d511aa07d3d2b2f70f7842d37e70ea02e=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Andrey=20Borodin=20=0ADate:=20= Tue,=207=20Apr=202026=2015:28:06=20+0500=0ASubject:=20[PATCH=202/4]=20= Track=20RTE_FUNCTION=20composite=20rowtype=20dependencies=0A=0ARecord=20= typrelid=20dependencies=20for=20named=20composite=20outputs=20of=20= RTE_FUNCTION=0Anodes,=20so=20relcache=20invalidation=20from=20ALTER=20= TYPE=20marks=20cached=20plans=20stale.=0A=0AThis=20keeps=20prepared=20= and=20SPI=20plans=20in=20sync=20with=20composite=20rowshape=20changes.=0A= ---=0A=20doc/src/sgml/ref/alter_type.sgml=20=20=20=20=20|=20=209=20= +++++++=0A=20src/backend/optimizer/plan/setrefs.c=20|=2036=20= ++++++++++++++++++++++++++++=0A=202=20files=20changed,=2045=20= insertions(+)=0A=0Adiff=20--git=20a/doc/src/sgml/ref/alter_type.sgml=20= b/doc/src/sgml/ref/alter_type.sgml=0Aindex=20025a3ee48f5..72b046afd98=20= 100644=0A---=20a/doc/src/sgml/ref/alter_type.sgml=0A+++=20= b/doc/src/sgml/ref/alter_type.sgml=0A@@=20-242,6=20+242,15=20@@=20ALTER=20= TYPE=20name=20SET=20(=20= =0A=20=0A+=20=20= =0A+=20=20=20Changing=20a=20composite=20type's=20attributes=20also=20= invalidates=20cached=20plans=20for=0A+=20=20=20queries=20that=20scan=20= SETOF=20functions=20returning=20that=0A+=20=20=20= composite=20type=20(including=20through=20a=20domain=20over=20the=20= composite),=20even=20when=0A+=20=20=20no=20CREATE=20OR=20= REPLACE=20FUNCTION=20is=20run.=20=20This=20matches=20the=0A+=20= =20=20relcache=20invalidation=20already=20sent=20for=20the=20composite=20= type's=20cataloged=0A+=20=20=20rowtype.=0A+=20=20=0A+=0A=20=20=20= =0A=20=20=20=20You=20must=20own=20the=20type=20to=20use=20= ALTER=20TYPE.=0A=20=20=20=20To=20change=20the=20= schema=20of=20a=20type,=20you=20must=20also=20have=0Adiff=20--git=20= a/src/backend/optimizer/plan/setrefs.c=20= b/src/backend/optimizer/plan/setrefs.c=0Aindex=20= ff0e875f2a2..b7e387b7a2a=20100644=0A---=20= a/src/backend/optimizer/plan/setrefs.c=0A+++=20= b/src/backend/optimizer/plan/setrefs.c=0A@@=20-26,6=20+26,7=20@@=0A=20= #include=20"optimizer/subselect.h"=0A=20#include=20"optimizer/tlist.h"=0A= =20#include=20"parser/parse_relation.h"=0A+#include=20= "parser/parse_type.h"=0A=20#include=20"rewrite/rewriteManip.h"=0A=20= #include=20"tcop/utility.h"=0A=20#include=20"utils/syscache.h"=0A@@=20= -500,6=20+501,34=20@@=20add_rtes_to_flat_rtable(PlannerInfo=20*root,=20= bool=20recursing)=0A=20=09}=0A=20}=0A=20=0A+/*=0A+=20*=20Record=20= relcache=20dependencies=20for=20RTE_FUNCTION=20entries=20whose=20= declared=20result=0A+=20*=20is=20a=20named=20composite=20type=20(or=20a=20= domain=20over=20one).=20=20ALTER=20TYPE=20{ADD=20|=20DROP=20|=0A+=20*=20= ALTER}=20ATTRIBUTE=20updates=20the=20composite=20type's=20pg_class=20row=20= without=20changing=0A+=20*=20OIDs=20of=20dependent=20functions,=20so=20= plans=20must=20be=20rebuilt=20when=20the=20composite=0A+=20*=20rowtype=20= changes.=20=20(Compare=20extract_query_dependencies_walker,=20which=20= must=0A+=20*=20stay=20in=20sync.)=0A+=20*/=0A+static=20void=0A= +add_function_rte_relation_deps(PlannerGlobal=20*glob,=20const=20= RangeTblEntry=20*rte)=0A+{=0A+=09ListCell=20=20=20*lc;=0A+=0A+=09= Assert(rte->rtekind=20=3D=3D=20RTE_FUNCTION);=0A+=0A+=09foreach(lc,=20= rte->functions)=0A+=09{=0A+=09=09RangeTblFunction=20*rtfunc=20=3D=20= (RangeTblFunction=20*)=20lfirst(lc);=0A+=09=09Oid=09=09=09typid;=0A+=09=09= Oid=09=09=09typrelid;=0A+=0A+=09=09typid=20=3D=20= exprType(rtfunc->funcexpr);=0A+=09=09typrelid=20=3D=20= typeOrDomainTypeRelid(typid);=0A+=09=09if=20(OidIsValid(typrelid))=0A+=09= =09=09glob->relationOids=20=3D=20lappend_oid(glob->relationOids,=20= typrelid);=0A+=09}=0A+}=0A+=0A=20/*=0A=20=20*=20Extract=20= RangeTblEntries=20from=20a=20subquery=20that=20was=20never=20planned=20= at=20all=0A=20=20*/=0A@@=20-529,6=20+558,8=20@@=20= flatten_rtes_walker(Node=20*node,=20flatten_rtes_walker_context=20*cxt)=0A= =20=09=09if=20(rte->rtekind=20=3D=3D=20RTE_RELATION=20||=0A=20=09=09=09= (rte->rtekind=20=3D=3D=20RTE_SUBQUERY=20&&=20OidIsValid(rte->relid)))=0A=20= =09=09=09add_rte_to_flat_rtable(cxt->glob,=20cxt->query->rteperminfos,=20= rte);=0A+=09=09else=20if=20(rte->rtekind=20=3D=3D=20RTE_FUNCTION)=0A+=09=09= =09add_function_rte_relation_deps(cxt->glob,=20rte);=0A=20=09=09return=20= false;=0A=20=09}=0A=20=09if=20(IsA(node,=20Query))=0A@@=20-567,6=20= +598,9=20@@=20add_rte_to_flat_rtable(PlannerGlobal=20*glob,=20List=20= *rteperminfos,=0A=20{=0A=20=09RangeTblEntry=20*newrte;=0A=20=0A+=09if=20= (rte->rtekind=20=3D=3D=20RTE_FUNCTION)=0A+=09=09= add_function_rte_relation_deps(glob,=20rte);=0A+=0A=20=09/*=20flat=20= copy=20to=20duplicate=20all=20the=20scalar=20fields=20*/=0A=20=09newrte=20= =3D=20palloc_object(RangeTblEntry);=0A=20=09memcpy(newrte,=20rte,=20= sizeof(RangeTblEntry));=0A@@=20-3800,6=20+3834,8=20@@=20= extract_query_dependencies_walker(Node=20*node,=20PlannerInfo=20= *context)=0A=20=09=09=09=09(rte->rtekind=20=3D=3D=20RTE_NAMEDTUPLESTORE=20= &&=20OidIsValid(rte->relid)))=0A=20=09=09=09=09= context->glob->relationOids=20=3D=0A=20=09=09=09=09=09= lappend_oid(context->glob->relationOids,=20rte->relid);=0A+=09=09=09else=20= if=20(rte->rtekind=20=3D=3D=20RTE_FUNCTION)=0A+=09=09=09=09= add_function_rte_relation_deps(context->glob,=20rte);=0A=20=09=09}=0A=20=0A= =20=09=09/*=20And=20recurse=20into=20the=20query's=20subexpressions=20*/=0A= --=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_D41EBC5B-99EB-462D-A4BB-D63D6CD88C83 Content-Disposition: attachment; filename=0003-Add-TAP-test-for-plpgsql-RETURN-QUERY-DDL-race.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="0003-Add-TAP-test-for-plpgsql-RETURN-QUERY-DDL-race.patch" Content-Transfer-Encoding: quoted-printable =46rom=2063a188921074a0a5cf359687e14f8b46b98d2264=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Andrey=20Borodin=20=0ADate:=20= Tue,=2014=20Apr=202026=2014:29:14=20+0500=0ASubject:=20[PATCH=203/4]=20= Add=20TAP=20test=20for=20plpgsql=20RETURN=20QUERY=20DDL=20race=0A=0AAdd=20= an=20injection-point=20TAP=20reproducer=20for=20concurrent=20ALTER=20= TYPE=20+=0ACREATE=20OR=20REPLACE=20FUNCTION=20during=20an=20in-progress=20= RETURN=20QUERY=20call.=0A=0AThe=20test=20captures=20the=20mid-statement=20= rowshape=20mismatch=20scenario.=0A---=0A=20src/pl/plpgsql/src/pl_exec.c=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20|=20=206=20++=0A=20= src/test/modules/test_misc/meson.build=20=20=20=20=20=20=20=20|=20=201=20= +=0A=20.../t/012_plpgsql_composite_replan_race.pl=20=20=20=20|=2096=20= +++++++++++++++++++=0A=203=20files=20changed,=20103=20insertions(+)=0A=20= create=20mode=20100644=20= src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl=0A=0A= diff=20--git=20a/src/pl/plpgsql/src/pl_exec.c=20= b/src/pl/plpgsql/src/pl_exec.c=0Aindex=2065b0fd0790f..844f8080dc0=20= 100644=0A---=20a/src/pl/plpgsql/src/pl_exec.c=0A+++=20= b/src/pl/plpgsql/src/pl_exec.c=0A@@=20-41,6=20+41,7=20@@=0A=20#include=20= "utils/builtins.h"=0A=20#include=20"utils/datum.h"=0A=20#include=20= "utils/fmgroids.h"=0A+#include=20"utils/injection_point.h"=0A=20#include=20= "utils/lsyscache.h"=0A=20#include=20"utils/memutils.h"=0A=20#include=20= "utils/rel.h"=0A@@=20-3594,6=20+3595,11=20@@=20= exec_stmt_return_query(PLpgSQL_execstate=20*estate,=0A=20=09/*=20There=20= might=20be=20some=20tuples=20in=20the=20tuplestore=20already=20*/=0A=20=09= tcount=20=3D=20tuplestore_tuple_count(estate->tuple_store);=0A=20=0A+=09= /*=0A+=09=20*=20Test-only=20pause=20point=20for=20RETURN=20QUERY=20race=20= conditions.=0A+=09=20*/=0A+=09= INJECTION_POINT("plpgsql-return-query-before-exec",=20NULL);=0A+=0A=20=09= /*=0A=20=09=20*=20Set=20up=20DestReceiver=20to=20transfer=20results=20= directly=20to=20tuplestore,=0A=20=09=20*=20converting=20rowtype=20if=20= necessary.=20=20DestReceiver=20lives=20in=20mcontext.=0Adiff=20--git=20= a/src/test/modules/test_misc/meson.build=20= b/src/test/modules/test_misc/meson.build=0Aindex=20= 1b25d98f7f3..a4f4f57134b=20100644=0A---=20= a/src/test/modules/test_misc/meson.build=0A+++=20= b/src/test/modules/test_misc/meson.build=0A@@=20-20,6=20+20,7=20@@=20= tests=20+=3D=20{=0A=20=20=20=20=20=20=20't/009_log_temp_files.pl',=0A=20=20= =20=20=20=20=20't/010_index_concurrently_upsert.pl',=0A=20=20=20=20=20=20= =20't/011_lock_stats.pl',=0A+=20=20=20=20=20=20= 't/012_plpgsql_composite_replan_race.pl',=0A=20=20=20=20=20],=0A=20=20=20= =20=20#=20The=20injection=20points=20are=20cluster-wide,=20so=20disable=20= installcheck=0A=20=20=20=20=20'runningcheck':=20false,=0Adiff=20--git=20= a/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl=20= b/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl=0A= new=20file=20mode=20100644=0Aindex=2000000000000..e151409457d=0A---=20= /dev/null=0A+++=20= b/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl=0A@@=20= -0,0=20+1,96=20@@=0A+#=20Copyright=20(c)=202026,=20PostgreSQL=20Global=20= Development=20Group=0A+=0A+use=20strict;=0A+use=20warnings=20FATAL=20=3D>=20= 'all';=0A+=0A+use=20PostgreSQL::Test::Cluster;=0A+use=20= PostgreSQL::Test::Utils;=0A+use=20Test::More;=0A+=0A+if=20= ($ENV{enable_injection_points}=20ne=20'yes')=0A+{=0A+=09plan=20skip_all=20= =3D>=20'Injection=20points=20not=20supported=20by=20this=20build';=0A+}=0A= +=0A+my=20$node=20=3D=20= PostgreSQL::Test::Cluster->new('plpgsql_composite_replan_race');=0A= +$node->init;=0A+$node->start;=0A+=0A+if=20= (!$node->check_extension('injection_points'))=0A+{=0A+=09plan=20skip_all=20= =3D>=20'Extension=20injection_points=20not=20installed';=0A+}=0A+=0A= +$node->safe_psql('postgres',=20'CREATE=20EXTENSION=20= injection_points;');=0A+=0A+$node->safe_psql('postgres',=20q[=0A+CREATE=20= TYPE=20planinv_ct=20AS=20(a=20int,=20b=20int);=0A+CREATE=20TABLE=20= planinv_tbl=20(a=20int,=20b=20int);=0A+INSERT=20INTO=20planinv_tbl=20= VALUES=20(1,=202);=0A+CREATE=20FUNCTION=20planinv_srf()=20RETURNS=20= SETOF=20planinv_ct=0A+=20=20LANGUAGE=20sql=20STABLE=20SECURITY=20DEFINER=20= AS=20$$=0A+=20=20=20=20SELECT=20a,=20b=20FROM=20planinv_tbl=0A+=20=20$$;=0A= +CREATE=20FUNCTION=20planinv_caller()=20RETURNS=20SETOF=20planinv_ct=20= LANGUAGE=20plpgsql=20AS=20$$=0A+BEGIN=0A+=20=20RETURN=20QUERY=20SELECT=20= r.*=20FROM=20planinv_srf()=20r;=0A+END;=0A+$$;=0A+]);=0A+=0A+#=20Warm=20= up=20expression/plan=20caches=20first.=0A= +is($node->safe_psql('postgres',=20'SELECT=20*=20FROM=20= planinv_caller();'),=20'1|2',=0A+=09'warmup=20call=20returns=20initial=20= row=20shape');=0A+=0A+my=20$backend2=20=3D=20= $node->background_psql('postgres',=20on_error_stop=20=3D>=200);=0A= +$backend2->query_safe(q[=0A+SELECT=20injection_points_set_local();=0A= +SELECT=20injection_points_attach('plpgsql-return-query-before-exec',=20= 'wait');=0A+]);=0A+=0A+$backend2->query_until(=0A+=09qr/race_started/,=20= q[=0A+\echo=20race_started=0A+BEGIN;=0A+SELECT=20*=20FROM=20= planinv_caller();=0A+\echo=20race_done=0A+]);=0A+=0A= +$node->poll_query_until('postgres',=20q[=0A+SELECT=20EXISTS=20(=0A+=20=20= SELECT=201=0A+=20=20FROM=20pg_stat_activity=0A+=20=20WHERE=20= wait_event_type=20=3D=20'InjectionPoint'=0A+=20=20=20=20AND=20wait_event=20= =3D=20'plpgsql-return-query-before-exec'=0A+);=0A+])=20or=20die=20= 'backend2=20did=20not=20reach=20injection=20point=20in=20time';=0A+=0A= +$node->safe_psql('postgres',=20q[=0A+BEGIN;=0A+ALTER=20TYPE=20= planinv_ct=20ADD=20ATTRIBUTE=20c=20int;=0A+CREATE=20OR=20REPLACE=20= FUNCTION=20planinv_srf()=20RETURNS=20SETOF=20planinv_ct=0A+=20=20= LANGUAGE=20sql=20STABLE=20SECURITY=20DEFINER=20AS=20$$=0A+=20=20=20=20= SELECT=20a,=20b,=2099=20FROM=20planinv_tbl=0A+=20=20$$;=0A+COMMIT;=0A= +]);=0A+=0A+$node->safe_psql('postgres',=0A+=09"SELECT=20= injection_points_wakeup('plpgsql-return-query-before-exec');");=0A+=0A= +my=20$out=20=3D=20$backend2->query_until(qr/race_done/,=20q[]);=0A= +like($out,=20qr/^1\|2\|99$/m,=0A+=09'concurrent=20ALTER=20TYPE=20+=20= CREATE=20OR=20REPLACE=20does=20not=20break=20RETURN=20QUERY');=0A= +is($backend2->{stderr},=20'',=0A+=09'no=20tuple=20shape=20mismatch=20= reported=20by=20RETURN=20QUERY');=0A+=0A+ok($backend2->quit);=0A+=0A= +$node->safe_psql('postgres',=20q[=0A+DROP=20FUNCTION=20= planinv_caller();=0A+DROP=20FUNCTION=20planinv_srf();=0A+DROP=20TABLE=20= planinv_tbl;=0A+DROP=20TYPE=20planinv_ct;=0A+]);=0A+=0A+done_testing();=0A= --=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_D41EBC5B-99EB-462D-A4BB-D63D6CD88C83 Content-Disposition: attachment; filename=0004-Lock-SETOF-composite-return-type-during-plpgsql-exec.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="0004-Lock-SETOF-composite-return-type-during-plpgsql-exec.patch" Content-Transfer-Encoding: quoted-printable =46rom=2058cda5c1b4bbf9f5a9452d971f365d17163bd9f2=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Andrey=20Borodin=20=0ADate:=20= Tue,=2014=20Apr=202026=2014:29:21=20+0500=0ASubject:=20[PATCH=204/4]=20= Lock=20SETOF=20composite=20return=20type=20during=20plpgsql=20execution=0A= =0AFor=20SETOF=20PL/pgSQL=20functions=20returning=20named=20composite=20= tuples,=20acquire=0AAccessShareLock=20on=20the=20return=20type's=20= typrelid=20at=20function=20entry.=0A=0AThis=20keeps=20rowshape=20stable=20= for=20the=20whole=20execution=20under=20concurrent=0AALTER=20TYPE=20and=20= avoids=20mid-statement=20mismatch=20errors.=0A---=0A=20= src/pl/plpgsql/src/pl_exec.c=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20|=2015=20+++++++++++++=0A=20= .../t/012_plpgsql_composite_replan_race.pl=20=20=20=20|=2021=20= +++++++++++++++----=0A=202=20files=20changed,=2032=20insertions(+),=204=20= deletions(-)=0A=0Adiff=20--git=20a/src/pl/plpgsql/src/pl_exec.c=20= b/src/pl/plpgsql/src/pl_exec.c=0Aindex=20844f8080dc0..44c2f47506b=20= 100644=0A---=20a/src/pl/plpgsql/src/pl_exec.c=0A+++=20= b/src/pl/plpgsql/src/pl_exec.c=0A@@=20-34,6=20+34,7=20@@=0A=20#include=20= "parser/parse_coerce.h"=0A=20#include=20"parser/parse_type.h"=0A=20= #include=20"plpgsql.h"=0A+#include=20"storage/lmgr.h"=0A=20#include=20= "storage/proc.h"=0A=20#include=20"tcop/cmdtag.h"=0A=20#include=20= "tcop/pquery.h"=0A@@=20-4038,6=20+4039,20=20@@=20= plpgsql_estate_setup(PLpgSQL_execstate=20*estate,=0A=20=09= estate->retistuple=20=3D=20func->fn_retistuple;=0A=20=09estate->retisset=20= =3D=20func->fn_retset;=0A=20=0A+=09/*=0A+=09=20*=20Keep=20named=20= composite=20SETOF=20return=20types=20stable=20for=20the=20whole=20= function=0A+=09=20*=20execution.=20=20This=20prevents=20concurrent=20= ALTER=20TYPE=20from=20changing=20rowshape=0A+=09=20*=20between=20= statement=20setup=20and=20RETURN=20QUERY=20execution.=0A+=09=20*/=0A+=09= if=20(estate->retisset=20&&=20estate->retistuple)=0A+=09{=0A+=09=09Oid=09= =09=09typrelid;=0A+=0A+=09=09typrelid=20=3D=20= typeOrDomainTypeRelid(estate->fn_rettype);=0A+=09=09if=20= (OidIsValid(typrelid))=0A+=09=09=09LockRelationOid(typrelid,=20= AccessShareLock);=0A+=09}=0A+=0A=20=09estate->readonly_func=20=3D=20= func->fn_readonly;=0A=20=09estate->atomic=20=3D=20true;=0A=20=0Adiff=20= --git=20= a/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl=20= b/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl=0A= index=20e151409457d..0c5f169000a=20100644=0A---=20= a/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl=0A= +++=20= b/src/test/modules/test_misc/t/012_plpgsql_composite_replan_race.pl=0A@@=20= -51,7=20+51,6=20@@=20SELECT=20= injection_points_attach('plpgsql-return-query-before-exec',=20'wait');=0A= =20$backend2->query_until(=0A=20=09qr/race_started/,=20q[=0A=20\echo=20= race_started=0A-BEGIN;=0A=20SELECT=20*=20FROM=20planinv_caller();=0A=20= \echo=20race_done=0A=20]);=0A@@=20-65,7=20+64,12=20@@=20SELECT=20EXISTS=20= (=0A=20);=0A=20])=20or=20die=20'backend2=20did=20not=20reach=20injection=20= point=20in=20time';=0A=20=0A-$node->safe_psql('postgres',=20q[=0A+my=20= $ddl_backend=20=3D=20$node->background_psql('postgres',=20on_error_stop=20= =3D>=200);=0A+my=20$ddl_pid=20=3D=20$ddl_backend->query_safe('SELECT=20= pg_backend_pid()');=0A+chomp($ddl_pid);=0A+$ddl_backend->query_until(=0A= +=09qr/ddl_started/,=20q[=0A+\echo=20ddl_started=0A=20BEGIN;=0A=20ALTER=20= TYPE=20planinv_ct=20ADD=20ATTRIBUTE=20c=20int;=0A=20CREATE=20OR=20= REPLACE=20FUNCTION=20planinv_srf()=20RETURNS=20SETOF=20planinv_ct=0A@@=20= -73,19=20+77,28=20@@=20CREATE=20OR=20REPLACE=20FUNCTION=20planinv_srf()=20= RETURNS=20SETOF=20planinv_ct=0A=20=20=20=20=20SELECT=20a,=20b,=2099=20= FROM=20planinv_tbl=0A=20=20=20$$;=0A=20COMMIT;=0A+\echo=20ddl_done=0A=20= ]);=0A=20=0A=20$node->safe_psql('postgres',=0A=20=09"SELECT=20= injection_points_wakeup('plpgsql-return-query-before-exec');");=0A=20=0A=20= my=20$out=20=3D=20$backend2->query_until(qr/race_done/,=20q[]);=0A= -like($out,=20qr/^1\|2\|99$/m,=0A-=09'concurrent=20ALTER=20TYPE=20+=20= CREATE=20OR=20REPLACE=20does=20not=20break=20RETURN=20QUERY');=0A= +like($out,=20qr/^1\|2$/m,=0A+=09'in-progress=20statement=20keeps=20old=20= row=20shape=20across=20concurrent=20DDL');=0A=20is($backend2->{stderr},=20= '',=0A=20=09'no=20tuple=20shape=20mismatch=20reported=20by=20RETURN=20= QUERY');=0A=20=0A=20ok($backend2->quit);=0A=20=0A+my=20$ddl_out=20=3D=20= $ddl_backend->query_until(qr/ddl_done/,=20q[]);=0A= +is($ddl_backend->{stderr},=20'',=20'concurrent=20DDL=20session=20= completed=20cleanly');=0A+like($ddl_out,=20qr/ddl_done/m,=20'DDL=20= proceeds=20after=20RETURN=20QUERY=20finishes');=0A= +ok($ddl_backend->quit);=0A+=0A+is($node->safe_psql('postgres',=20= 'SELECT=20*=20FROM=20planinv_caller();'),=20'1|2|99',=0A+=09'subsequent=20= statement=20sees=20new=20composite=20row=20shape');=0A+=0A=20= $node->safe_psql('postgres',=20q[=0A=20DROP=20FUNCTION=20= planinv_caller();=0A=20DROP=20FUNCTION=20planinv_srf();=0A--=20=0A2.50.1=20= (Apple=20Git-155)=0A=0A= --Apple-Mail=_D41EBC5B-99EB-462D-A4BB-D63D6CD88C83--