public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Smith <[email protected]>
To: Nisha Moond <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
Date: Wed, 10 Jun 2026 14:36:44 +1000
Message-ID: <CAHut+Pu0-A92MyRxFCLNQFYgAKGrKbYChG_p4ARogEfbAtMm8A@mail.gmail.com> (raw)
In-Reply-To: <CABdArM5qHR=Zmyfqs6AuCn-Crj=_w3CXA-1K9rQ53_zismXCNQ@mail.gmail.com>
References: <CABdArM5sw4Q1ZU8HGdo4BSc1A_+8xtUNq17j6wcir=yMUy19Cg@mail.gmail.com>
<CAHut+PvnH8QHa035Skoh1e9jm_H08DO9fQ=F-NAMsEpYf0RZ2Q@mail.gmail.com>
<CAJpy0uDu0LcNXcZCP0cR_LHqo+sau33KwPFHemmGVYf_JTxRBQ@mail.gmail.com>
<CAA4eK1KbCWBmEXH-rhQjKgNwq=onZp8vRR-QkRhPpbKwL-kQdw@mail.gmail.com>
<CAHut+Pvj4=GWoJEd4EBdp4pi6KxXQ46ioW=PV+=UktiXr2gCvg@mail.gmail.com>
<CABdArM75F0A+DGP8AOt-_b_XREX40rvFid1jRjnr_+S5b51t8Q@mail.gmail.com>
<CAJpy0uDTshb243L5yEYWB3uO-JrwSoRqQDNovh03K2GZuuR3Pg@mail.gmail.com>
<CAJpy0uDy97ULmJUwPacAzc5u2seuPK6RXgCS1rnsW2MfR4eeSw@mail.gmail.com>
<CABdArM6oXXXSAxxXFktTTfBf4kyxJCvdNtTbUZtSwJ=CepN+Xw@mail.gmail.com>
<CAJpy0uBqM+fq7+g1ZRATuY16H10MFP9i25wfFCYCE5MGu+PE0Q@mail.gmail.com>
<CABdArM4uKaS1coCQj6rAwMmHqU_cCJyEWNic-PFF1_ZjDDM82Q@mail.gmail.com>
<CAHut+Pu5VNakf5JAhKM7T-P_q37eN1Qgv5nvZUe+8RAAT41y4g@mail.gmail.com>
<CABdArM6WTm2gP4pcjVdHT1Nx6zdLKTq7nLPUkzZOhprc95a6Rw@mail.gmail.com>
<CAHut+Ptthc1X-UA8-6zG-iFeCDuoNd+oJRBZ1eCnJ9RNOXjfBQ@mail.gmail.com>
<CABdArM79m7-CTf6KGGGU2QBydFtuonGgfxRSqk-vhwTsH8z1ow@mail.gmail.com>
<CAHut+PuiK_Pa=BkSgBxYzqf1PYh+mcUcUQCr8r1e69-y1r+hhw@mail.gmail.com>
<CABdArM7rH+3GekRgufEOwrJxUeQk=LB182CQwJD35e0oN7q8ZA@mail.gmail.com>
<CAHut+PtP1mbQT==xo=G-37dV9Lt3q7YO2eMEAqSbZuszy93LcQ@mail.gmail.com>
<CABdArM6XRpUR86a-daYMXFqhH-spJQiQAVfJ2+GFiAqeup2jyA@mail.gmail.com>
<CAHut+PuDB=doKUSf94cs8hOo2d5mOc+GxxPOf57xGhdE6e-Aog@mail.gmail.com>
<CABdArM76MZ4dYC87zCv67fVY9t5aFLd2nD5tNBxFFizESyBOSg@mail.gmail.com>
<CAN4CZFM9xXkw8FvHOTan+m4rASSoxbJTWXMbZ_-v2M-84w5Wzg@mail.gmail.com>
<CABdArM5wj0dtieSVMvzvJDh5fJ-GGwh3PxV+E_MbPEntCk4KRg@mail.gmail.com>
<CAN4CZFMaB1a89NZjRT9bWFZ8-XN02amye_fnYAg0NM_23DAwQw@mail.gmail.com>
<CABdArM5qHR=Zmyfqs6AuCn-Crj=_w3CXA-1K9rQ53_zismXCNQ@mail.gmail.com>
Hi Nisha.
Some review comments for v11-0001.
(I had no new review comments for v11-0002, v11-0003)
======
doc/src/sgml/ref/create_publication.sgml
1.
+ <para>
+ For <literal>FOR TABLES IN SCHEMA</literal> publications, the
+ <literal>EXCEPT</literal> clause is schema-scoped: the exclusion applies
+ only in the context of the schema to which it was attached. If a table
+ listed in the <literal>EXCEPT</literal> clause is later moved to a
+ different schema using <command>ALTER TABLE ... SET SCHEMA</command>,
+ the exclusion is removed. The table will then be published if its new
+ schema is part of a publication. If the table is moved back to
+ the original schema, the exclusion is not restored; the user must
+ re-establish it explicitly using <command>ALTER PUBLICATION</command>.
+ Dropping a table always removes it from the <literal>EXCEPT</literal>
+ list regardless of publication type.
+ </para>
1a.
I felt this should be moved up to be the 2nd paragraph of the "EXCEPT"
part. Subsequent information about
inheritance/partitions/multi-publications is common for both EXCEPTS.
~
1b.
All that info about "If a table..." seemed more relevant to ALTER
PUBLICATION than to CREATE PUBLICATION, so I didn't think we needed
those details here.
======
src/backend/commands/publicationcmds.c
RemovePublicationExceptForRelation:
2.
+/*
+ * Remove any EXCEPT clause entries for a relation from schema publications.
+ * Called when a table changes schema (ALTER TABLE ... SET SCHEMA), so that
+ * a schema-scoped exclusion does not silently follow the table to its new
+ * schema. FOR ALL TABLES publications are skipped because their EXCEPT
+ * clause is publication-scoped, not schema-scoped, so that exclusion should
+ * persist regardless of what schema the table is in.
+ */
Instead of saying "FOR ALL TABLES publications are skipped", rephrase
that to be something like: "This problem does not apply to FOR ALL
TABLES publications because..."
Anyway, I think you can remove that note from the function comment,
and instead put it here:
+ if (!is_schema_publication(pubid))
+ continue;
~~~
3.
+{
+ List *pubids;
+ ListCell *lc;
+ ObjectAddress obj;
+
+ pubids = GetRelationExcludedPublications(relid);
+
+ foreach(lc, pubids)
Using a `foreach_oid` loop might be tidier here.
======
src/backend/commands/tablecmds.c
4.
table_close(classRel, RowExclusiveLock);
+
+ /*
+ * Remove any EXCEPT clause entries for this relation from schema
+ * publications. A schema-scoped exclusion is no longer meaningful once
+ * the table moves to a different schema.
+ */
+ if (rel->rd_rel->relkind == RELKIND_RELATION ||
+ rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ RemovePublicationExceptForRelation(RelationGetRelid(rel));
Should this code be put prior to the table_close, where the other
dependent stuff is also removed?
======
src/backend/parser/gram.y
5.
+ /* For TABLES_IN_CUR_SCHEMA: leave except_tables for execution time */
Isn't this repeating exactly what you already said in the other
comment ("For TABLES_IN_CUR_SCHEMA the schema name is not yet
known...")?
======
src/test/regress/sql/publication.sql
6.
+-- test for EXCEPT clause with schema publication (bug: excluded
table was incorrectly returned)
+SELECT * FROM test_gpt(ARRAY['pub_schema_except'],
'gpt_test_sch.tbl_sch'); -- no result (excluded)
+SELECT * FROM test_gpt(ARRAY['pub_schema_except'],
'gpt_test_sch.tbl_sch2'); -- one row (included via schema)
+
Is that "(bug: ...)" comment necessary?
======
Kind Regards,
Peter Smith.
Fujitsu Australia
view thread (24+ messages) latest in thread
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], [email protected], [email protected]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
In-Reply-To: <CAHut+Pu0-A92MyRxFCLNQFYgAKGrKbYChG_p4ARogEfbAtMm8A@mail.gmail.com>
* 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