MIME-Version: 1.0
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>
 <CAHut+Pu0-A92MyRxFCLNQFYgAKGrKbYChG_p4ARogEfbAtMm8A@mail.gmail.com>
 <CABdArM4fUnAZ10uyT61tNYu-WWHBiBOz75at9L90jBKLH5YR8g@mail.gmail.com>
 <CAHut+Pv-GA1oGa6+nwn_5AVhBg8NuJThQVUzqhQPXJge49jnew@mail.gmail.com>
 <CAA4eK1LMooRmK3_w_Zo-g3ftB7mDDkLitXjiEjgZY=0LTeZRoQ@mail.gmail.com>
 <CABdArM5j8gX_xYDU6_aPEOuPAiCGtF8QyrpHeqZXQDsQ_wpJKA@mail.gmail.com>
 <CAHut+PvAzsQA7BPKFC2dKWAr_kpAdHhLAaGOJQmZ_7=0paZCZA@mail.gmail.com>
 <CABdArM4peXa8W9AYw6OHWmBuP4kkN3e01TcrUq_arWQLacoiNw@mail.gmail.com>
In-Reply-To: 
 <CABdArM4peXa8W9AYw6OHWmBuP4kkN3e01TcrUq_arWQLacoiNw@mail.gmail.com>
From: Peter Smith <smithpb2250@gmail.com>
Date: Wed, 17 Jun 2026 11:53:40 +1000
Message-ID: 
 <CAHut+PucwGJ4Tyr9kRF4zNstAM_GGheU+-55u3JrKW8epKGSEQ@mail.gmail.com>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
To: Nisha Moond <nisha.moond412@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>,
 Zsolt Parragi <zsolt.parragi@percona.com>,
	pgsql-hackers@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHut%2BPucwGJ4Tyr9kRF4zNstAM_GGheU%2B-55u3JrKW8epKGSEQ%40mail.gmail.com>
Precedence: bulk

Hi Nisha.

Some review comments for v14-0001.

======
src/test/regress/sql/publication.sql

1.
+-- ALTER TABLE ... SET SCHEMA on a table excluded by a schema publication:
+-- the schema-scoped exclusion is no longer meaningful once the table moves
+-- out of its schema, so the exclution is auto-dropped.

1a.
typo: /exclution/exclusion/

~

1b.
Exclusions cannot be "dropped" (at least, not by a user with the DROP
command). So even though this is correct for what is happening
internally, maybe from the user PoV it is better to say something like
"auto-removed" or just "removed".

(Same comment may also apply to the DEBUG logging when this happened).

~~~

2.
+-- Restore so the existing cleanup below still works
+ALTER TABLE public.testpub_tbl_s2 SET SCHEMA pub_test;
+DROP PUBLICATION testpub_schema_except_setsch;

Why not just fix the cleanup code? It's not "existing cleanup" anyway
-- it's part of this same patch.

////////////////////

Some review comments for v14-0004.

======

doc/src/sgml/ref/alter_publication.sgml:

1.
    used with a publication defined with <literal>FOR TABLE</literal> or
    <literal>FOR TABLES IN SCHEMA</literal>, replaces the list of tables/schemas
    in the publication with the specified list; the existing tables or schemas
-   that were present in the publication will be removed.
+   that were present in the publication will be removed.  When
+   <literal>SET TABLES IN SCHEMA</literal> is used with an
+   <literal>EXCEPT</literal> clause, the excluded tables for each schema are
+   replaced with the specified list; if <literal>EXCEPT</literal> is omitted
+   for a schema, any existing exclusions for that schema are cleared.

"are cleared" or "are removed"... not sure what is better. Maybe
"cleared" is fine.

~~~

2.
+  <para>
+   The <literal>EXCEPT</literal> clause can be used with
+   <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from a
+   schema-level publication.
+  </para>
+

This is talking about the 'ADD' variant. So really, it belongs as part
of that 2nd para: "The first two variants modify..." because that is
where ADD is discussed.

~~~

3.
+   <varlistentry>
+    <term><literal>EXCEPT</literal></term>
+    <listitem>
+     <para>
+      When used with <literal>ADD TABLES IN SCHEMA</literal>
+      or <literal>SET TABLES IN SCHEMA</literal>, specifies
+      tables to be excluded from the publication.  Each named
+      table must belong to the schema specified in the same
+      <literal>TABLES IN SCHEMA</literal> clause.  Table names may be
+      schema-qualified or unqualified; unqualified names are implicitly
+      qualified with the schema named in the same clause.  See
+      <xref linkend="sql-createpublication"/> for further details on the
+      semantics of <literal>EXCEPT</literal>.
+     </para>
+     <para>
+      For <literal>FOR TABLES IN SCHEMA</literal> publications, the
+      <literal>EXCEPT</literal> clause is schema-scoped.  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 subsequently moved back to the original
+      schema, the exclusion is not restored, and must be re-established
+      explicitly using <command>ALTER PUBLICATION</command>.  Dropping a table
+      always removes it from the <literal>EXCEPT</literal> clause,
regardless of
+      publication type.
+     </para>
+    </listitem>
+   </varlistentry>

3a.
TBH, I lost track of what the final decision was about keeping this EXCEPT part.
IIUC, you wanted to defer a decision until later, but I did not
understand the plan:
a) Should the EXCEPT part be present for now, but consider removing it later?
b) Should the EXCEPT part be absent for now, but consider adding it later?

I prefer what you have done, but either way, we need to ensure Shlok's
thread uses the same approach.

~

3b.
I think that last "Dropping a table..." sentence should be a separate
<para>, unrelated to renaming/moving tables. Also, you don't need to
say "regardless of publication type" -- that would be the assumption
unless you say otherwise.

======
Kind Regards,
Peter Smith.
Fujitsu Australia