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 1vsJPl-00G1YH-2v for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Feb 2026 11:38:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vsJPk-00A0CW-2A for pgsql-hackers@arkaria.postgresql.org; Tue, 17 Feb 2026 11:38:48 +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 1vsJPk-00A0CO-16 for pgsql-hackers@lists.postgresql.org; Tue, 17 Feb 2026 11:38:48 +0000 Received: from mail-pj1-x102a.google.com ([2607:f8b0:4864:20::102a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vsJPi-00000001BhK-1qOJ for pgsql-hackers@lists.postgresql.org; Tue, 17 Feb 2026 11:38:48 +0000 Received: by mail-pj1-x102a.google.com with SMTP id 98e67ed59e1d1-3562e98d533so2612858a91.0 for ; Tue, 17 Feb 2026 03:38:46 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1771328324; cv=none; d=google.com; s=arc-20240605; b=kyt/gyiHIrksQn1XO+1YPJq2GyBARUqazKhqe6b+FoNrJhru3kyk6ukT/D2gZNFHbA luqaetfoflEF1ZzmGsiLS7TSUCH9l+D7NUf/0oHoENZ1V39L/5Wr1IhE48iVoV9fnyPf B6tCaW+T/LttgrqjwzTfgQ6PDBLFtTLHHW4OxjURyYiWGRp7NJ51Lqnk33uu8bqo40um jNOMDfOBV3CbM8vdrHLcstzNJp4SJSQzx/6ansfj+aqGgi7rvfT3cQt3T1ILWvwQku0s O+IfTA/OqgXuhHLlXD7n5eZDVH3Jivooq/Am+a1AKX1QfTd0BBfO2f1pM0M4LQpZ1iGz 5oAA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:dkim-signature; bh=hCeNE5rtVMcVsEZludoLsWNT+9kcdRdW9Bq0Uh5S9+I=; fh=cWTMYw/nrp4VSJmcIDXwTpqgMDs9N704NLcCQ/xAjGQ=; b=bgrgze+tu1cjqKqdmu6Y2FN6bjl343mhIQeAFg2S2xkp0ouplHpywSaIxXZBqXfkCG hOtf9Vg61hbaJ+dE1olgyp9bK4jbSxd/DV8kVtc9JmRCPxq9eNh+YGKILCtUw2+20JNf Ux5xbJrzi3lM/xPHtypbbMKi8YLRmtvoSGvTmQzPIBVJDdRcOswtWsdrkQHIGcSG4i0b uezkqzYr7wrksQwnhdRMGxrErQ1EANglbFlxh+NQEhAbW5WFxQKcnk4IwlnvQfoPXCYy 2pwyK/hnfjLQ0KnkrNCqtwUYlMnnpnpipYUziRCikhs4U0FpSsHOJytqU7qREsk3Re+N A/YQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1771328324; x=1771933124; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=hCeNE5rtVMcVsEZludoLsWNT+9kcdRdW9Bq0Uh5S9+I=; b=lHJVT4rPmbOSxAhxfl7gGeGeI8Xx0FGSLSIk9ov/uqvRF1cnAl1ye3SUc3sK3+C3DN dsBk6pFkyuX4xCjnDokzw1IQTVMP93qep3IBxyy5d5LrITI5mhpHxOjPSIYuTM9Klc+H FsU3aY/yCCSmHaLBkR6fOnp3z8TggZKb6vgC8YY/vsmTPpZ+l9wprB24bh2c0H+3IUdT Gzbrwh6OEEbg7igZhoLMDiiLJkRPgL4AZwqMQliq8eYb90lmMmwSTSzhXaQj4KEvoi4/ YezkJwuuB/O/xHxwtTpgDvwL8mMgZGYy0wEO5REuf08SSZTP3yZUT5xCCyMdIsagxoMK sFeQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1771328324; x=1771933124; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:x-gm-gg:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=hCeNE5rtVMcVsEZludoLsWNT+9kcdRdW9Bq0Uh5S9+I=; b=VblYiP/z9tGdC+ypBYMC4bLi4t/hKX9pRco7HHXqaDJ1NlD3YeCs/N/G/E7qaTUvY7 7X6cEOYs1Lgsc9DoBvem4FvZij3XlEjaZPP4dzq4yl7Eolx4KCLD5KlX0FUQNmEBe6WB vV2MHarVSZ7zCB+LnlQQ9uFm4kLgZepcdHjmT9RbIrNvpuUbPuQMNJEC2hc+ugeKt2si ryC8QpFfeUyzxX9NiAHBqZJgtd0yLvXn5Tv/1JDIAd5U5tM3F0/9fu9vHZIfS/Y+OFmr RQATyU0cfGIpgVEWhK3mTiDvpGplPOXhJZaKBkoaFPVBVjy7liYTAmJfPLZjcGbtP+wA zAnA== X-Forwarded-Encrypted: i=1; AJvYcCXPxdv7gh0Ed+bejY8ESxqJw4ZGjXhmlpA4PbAhk3EFq1RwZ9DxIF8SK1X5ytkCsU5HYJ+H3nAGnXvW28BE@lists.postgresql.org X-Gm-Message-State: AOJu0YyaHGXigLXd2xEdvmiGaWu4PQ1Q+zQ1HmWfD5xSjvSANMJYIulb UcuuZMYNazF5lOGaju13zKqDe31ekHSPefdsVoqZHL6OHwEIZ9Rd+TntEf4uxBI+ZDevZEyyxqB fmFv+Ae0lqCzP4K8vFJ8Ke9llhBldXO/6C8G2 X-Gm-Gg: AZuq6aKuvC1YJCt6khTdgJOqjeocltoHBYfsZr1IG0D1dBHQeTkw/JBRUiFuEv14Zef /AfvQgS5Z9MWYS7pid+Jkk8IkuKUcpwrvuEdFmblzlXKCSapTbyAwQvFpVaCv8hA7czkrPQmsQz gjz3n7QnjZAIUHbFCRVIBJHMP25uDjjlSiGH5cK7p78/AHC2Kgel5C6zTpLtUNnagUvIJpgeF0U piYyWfXOhB3JMM3lnNDsk1Umq9Qzgh535oB1E5XuatQRDw0MP7WA1BMQrbC3u8BqopGkhBv9Jvt 3pG0+xx9fpJDTJqNYUx33sJ9inYhEBsCz/X/SsXWNp1ucdYE6w== X-Received: by 2002:a17:90b:278c:b0:354:9bc2:881f with SMTP id 98e67ed59e1d1-356a7a87fc8mr11089880a91.28.1771328324486; Tue, 17 Feb 2026 03:38:44 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: shveta malik Date: Tue, 17 Feb 2026 17:08:32 +0530 X-Gm-Features: AaiRm52_ZfFEcnDkRLy3KlKNktLeehdj3cFwwPfcAhrctTv185v1XkGuw5yh4B0 Message-ID: Subject: Re: Skipping schema changes in publication To: vignesh C Cc: "David G. Johnston" , Dilip Kumar , Shlok Kyal , Amit Kapila , Peter Smith , "Zhijie Hou (Fujitsu)" , YeXiu <1518981153@qq.com>, Ian Lawrence Barwick , Bharath Rupireddy , PostgreSQL Hackers , shveta malik Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, Feb 17, 2026 at 12:01=E2=80=AFPM shveta malik wrote: > > On Tue, Feb 17, 2026 at 11:13=E2=80=AFAM vignesh C = wrote: > > > > Thanks for the comments. The attached v45 version patch has the > > changes for the same. > > > > Thanks Vignesh, please find a few comments from v44 itself. Please > ignore if already addressed. Will switch to v45 now. > > > 1) > publication_has_any_exception: > + ScanKeyInit(&skey, > + Anum_pg_publication_rel_prpubid, > + BTEqualStrategyNumber, F_OIDEQ, > + ObjectIdGetDatum(pubid)); > + > + scan =3D systable_beginscan(pubRel, > + PublicationRelPrrelidPrpubidIndexId, > + true, NULL, 1, &skey); > > PublicationRelPrrelidPrpubidIndexId is index on relid and pubid, I > don't think it will be used in this case where we have only pubid key. > Shall we use PublicationRelPrpubidIndexId instead? > > 2) > +/* > + * is_relid_published_explicitly > + * > + * Checks if the given relation OID is explicitly part of the publicatio= n. > + * This corresponds to the 'FOR TABLE' syntax. > + */ > +static bool > +is_relid_published_explicitly(Oid relid, Oid pubid) > +{ > + /* > + * Search the syscache for pg_publication_rel using the (relid, pubid) > + * index. > + */ > + return SearchSysCacheExists2(PUBLICATIONRELMAP, > + ObjectIdGetDatum(relid), > + ObjectIdGetDatum(pubid)); > +} > > How are we ensuring that it is not fetching the one with except-flag > as true? Shall we assert when pub is all-tables to rule out that > case/mistake? Or if we code-flow is expected to come to this function > even for 'all-tables' pub (it appears to me t by looking at the > caller), we shall return in such a case instead of Assert. > > > 3) > Shall we rename these: > 'is_relid_published_as_except' as 'is_relid_excepted'. > 'is_relid_published_as_except_with_ancestors' as 'is_relid_or_ancestor_ex= cepted' > > These will be to match tones of: > is_schema_published > is_relid_published_explicitly > > I feel even for 'is_relid_published_explicitly', we can simply say > 'is_relid_published'. Comments (and assert/checks) can explain that it > is for FOR-TABLE pub. > > 4) > + List *except_leaves =3D NIL; > + List *allowed_leaves =3D NIL; > > Similar to allowed_leaves, shall we have excepted_leaves instead of > except_leaves? > > 5) > pg_get_publication_effective_tables(): > > + > + /* > + * Check whether the table itself or its schema is > + * included in this publication. > + */ > + if (is_relid_published_explicitly(curr_relid, pubid) || > + is_schema_published(get_rel_namespace(curr_relid), pubid)) > + { > + allowed_leaves =3D lappend_oid(allowed_leaves, curr_relid); > + } > + else > + { > + List *ancestors =3D get_partition_ancestors(curr_relid); > + > + /* > + * Check whether any ancestor, or its schema, is > + * included in this publication. > + */ > + foreach_oid(anc_oid, ancestors) > + { > + if (is_relid_published_explicitly(anc_oid, pubid) || > + is_schema_published(get_rel_namespace(anc_oid), pubid)) > + allowed_leaves =3D lappend_oid(allowed_leaves, curr_relid); > + } > + } > > Do you think we can convert this code part to > is_relid_or_ancestor_published(), similar to > is_relid_published_as_except_with_ancestors()? > > is_relid_or_ancestor_published() can check both pg_publication_rel and > pg_publication_namespace. I do not see individual use-case scenarios > for is_relid_published_explicitly() and is_schema_published() and thus > it is better to club these in one function. So at the end we will be > left with 2 such functions: > > is_relid_or_ancestor_published > is_relid_or_ancestor_excepted/excluded (choose the name based on > others comments too) > A few more: 6) postgres=3D# CREATE PUBLICATION pub4 for ALL TABLES EXCEPT TABLE (tab1); ERROR: cannot add relation "tab1" to publication DETAIL: This operation is not supported for temporary tables. postgres=3D# CREATE PUBLICATION pub4 for ALL TABLES EXCEPT TABLE (tab2); ERROR: cannot add relation "tab2" to publication DETAIL: This operation is not supported for unlogged tables. Shall we change the error message here as we are not trying to add relation here. 7) Currently the error i s: postgres=3D# create subscription sub1 connection '...' publication pub1,pub= 2,pub3; ERROR: publications "pub1", "pub2", "pub3" are defined with EXCEPT TABLE HINT: Subscription cannot be created using multiple publications that specify EXCEPT TABLE. Hint looks more like DETAIL. Shall we have this: ERROR: cannot create subscription with multiple publications that specify EXCEPT TABLE DETAIL: The publications "pub1", "pub2", and "pub3" define EXCEPT TABLE clauses. 8) pg_get_publication_effective_tables(): + /* Return root immediately if no filtering logic is needed */ + if (has_clean_all_tables_pub || !has_any_except) I think we do not need any additional boolean 'has_any_except' for this purpose. We can simply rely on 'except_pub_names' being Nil. 9) + /* Return root immediately if no filtering logic is needed */ + if (has_clean_all_tables_pub || !has_any_except) + { + oldcontext =3D MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + final_output =3D list_make1_oid(root_relid); + MemoryContextSwitchTo(oldcontext); + } + else Can we please write a comment atop 'else' block to say what it is going to attempt? This is because there is a big chunk of code in 'else' block and it is difficult to construct our logic by reading each part of it. 10) CreatePublication() has changed the way we process publications. Earlier, we had explicit checks for publication types such as 'for_all_tables' and 'for_all_sequences' etc, which made the code easier to follow. That differentiation based on publication type is no longer there. As an example, we now invoke functions like TransformPubWhereClauses() and CheckPubRelationColumnList() even for FOR ALL TABLES ... EXCEPT publications, which are not needed. We could consider restoring the previous structure, where logic was clearly separated based on publication type. thanks Shveta