MIME-Version: 1.0
References: 
 <CAJpy0uB20MhJJEaPJdm31t4fykJ+fChA_76jU2P9HX5knbJvAA@mail.gmail.com>
 <CAD21AoCC8XuwfX62qKBSfHUAoww_XB3_84HjswgL9jxQy696yw@mail.gmail.com>
 <OS9PR01MB12149EA0C749BC29C7C949E32F544A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
 <CAD21AoBbZEshyaK0PeiF_J4_S75EfF=Gcs=C+X-osoVoUnawuQ@mail.gmail.com>
 <CAHut+PssG+sHeV+Xo0g=S7xBb9FgDPjHYDR4iSuOdYXDq-Psng@mail.gmail.com>
 <CAA4eK1LaSfAG7UAuy1xpnkWKM_YtrPuhbgAxYBFY3Sp_v_KqoQ@mail.gmail.com>
 <CAD21AoAb8E8krN63cY_U7RQs9v-zkqUZyKT_UVKDwKfExtvTBg@mail.gmail.com>
 <CAA4eK1K1GLR7DXSABayQE+pWM=v1ODD6haPYxuDhAYwJN5gjzg@mail.gmail.com>
 <CALDaNm2kvFahDDvdgCNo=Nv-COz_N5Xw8YmzQBN2bd3g=N81fQ@mail.gmail.com>
In-Reply-To: 
 <CALDaNm2kvFahDDvdgCNo=Nv-COz_N5Xw8YmzQBN2bd3g=N81fQ@mail.gmail.com>
From: Peter Smith <smithpb2250@gmail.com>
Date: Mon, 16 Mar 2026 19:38:21 +1100
Message-ID: 
 <CAHut+PsCqTR_kQu5M1TqBjnE6KM5cO22aH8boHfpMa_gSJBmWg@mail.gmail.com>
Subject: Re: Skipping schema changes in publication
To: vignesh C <vignesh21@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>,
 Masahiko Sawada <sawada.mshk@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
 shveta malik <shveta.malik@gmail.com>,
	Shlok Kyal <shlok.kyal.oss@gmail.com>,
 Nisha Moond <nisha.moond412@gmail.com>,
	Ashutosh Sharma <ashu.coek88@gmail.com>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
	Dilip Kumar <dilipbalaut@gmail.com>,
 "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>,
	YeXiu <1518981153@qq.com>, Ian Lawrence Barwick <barwick@gmail.com>,
	Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHut%2BPsCqTR_kQu5M1TqBjnE6KM5cO22aH8boHfpMa_gSJBmWg%40mail.gmail.com>
Precedence: bulk

Hi Vignesh.

Unfortunately, IMO there are some fundamental problems here due to
there being no accounting for publications with FOR ALL SEQUENCES. So
I am posting my review comments for just the docs part so you can see
it from my PoV. Maybe this was already discussed earlier in the thread
but I saw no mentions of it. AFAICT the discussion/posts were mostly
focussed on the setting/resetting of EXCEPT TABLE but seem to be
overlooking the bigger picture.

Below review comments are for v63-0001 docs only.

======
Commit Message

1.
The first form replaces the current EXCEPT TABLE list with the specified
tables. The second form clears the existing except table list.  Like the
creation syntax, only root partitioned tables can be specified in the
exclusion list.

~

IMO the second form is a long-time missing command from Postgres. For
example, it is possible to create an "empty" publication. But without
this "ALTER PUBLICATION name SET ALL TABLES" there was no way to
convert that to be a "FOR ALL TABLES" publication. So really this
feature was independently needed and should be done anyway
irrespective of any side-effect it has for the EXCEPT TABLE list.

Ideally the SET/ADD/DROP ALL TABLES can split out and done ahead of
the EXCEPT TABLE stuff.

Similarly, SET/ADD/DROP should be implemented also for FOR ALL
SEQUENCES otherwise there is no way to manipulate those either.

======
doc/src/sgml/ref/alter_publication.sgml

2.
Implementing the "SET ALL TABLES" is only a start. You also need to
have the other case:

* ADD ALL TABLES
* DROP ALL TABLES

Note you might start out with something like "CREATE PUBLICATION pub
FOR ALL TABLES, FOR ALL SEQUENCES" so you need to be able to be able
to modify/remove the published TABLES part without overwriting
published SEQUENCES part!!

i.e The "SET" means set the publication; it doesn't mean add to the
publication. So SET ALL TABLES obliterates any FOR ALL SEQUENCES.

~~~

3.
+<phrase>where <replaceable
class="parameter">publication_except_tables</replaceable> is:</phrase>
+
+    [ EXCEPT TABLE ( [ ONLY ] <replaceable
class="parameter">table_name</replaceable> [, ... ] ) ]
+

Hmm. Ideally, this should be part of 'publication_object' and
publication_drop_object' because the ADD/DROP are also needed for the
reasons given in my above review commentS.

~~~

4.
   <para>
-   The first three variants change which tables/schemas are part of the
-   publication.  The <literal>SET</literal> clause will replace the list of
-   tables/schemas in the publication with the specified list; the existing
-   tables/schemas that were present in the publication will be removed.  The
+   The first four variants modify which tables/schemas are included in the
+   publication, or which tables are excluded from it.  The
+   <literal>SET ALL TABLES</literal> clause is used to update the
+   <literal>EXCEPT TABLE</literal> list of a <literal>FOR ALL TABLES</literal>
+   publication.  If <literal>EXCEPT TABLE</literal> is specified with a list of
+   tables, the existing except table list is replaced with the
specified tables.
+   If <literal>EXCEPT TABLE</literal> is omitted, the existing except table
+   list is cleared. The <literal>SET</literal> clause, when 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.  The

I find this all a bit dubious because nothing seems to be accounting
for the possibility of "FOR ALL SEQUENCES" also in the publication...
e.g this entire ALTER command should also have

SET ALL SEQUENCES
ADD ALL SEQUENCES
DROP ALL SEQUENCE

and

SET ALL TABLES
ADD ALL TABLES
DROP ALL TABLES

IMO, a user will need to take care when using ALTER PUBLICATION ...
SET ALL TABLES that it does not destroy the publication of sequences
(and vice versa)

-- Start with an "empty" publication and make it a "FOR ALL TABLES"
publication...
CREATE PUBLICATION pub;
ALTER PUBLICATION pub SET ALL TABLES;
-- result is equivalent to "CREATE PUBLICATION ... FOR ALL TABLES"


-- give some table exceptions to it
ALTER PUBLICATION pub SET ALL TABLES EXCEPT TABLE(t1,t2);
-- result is equivalent to "CREATE PUBLICATION ... FOR ALL TABLES
EXCEPT TABLE(t1,t2)"


-- add sequences to this
ALTER PUBLICATION pub ADD ALL SEQUENCES
-- result is equivalent to "CREATE PUBLICATION ... FOR ALL TABLES
EXCEPT TABLE(t1,t2), FOR ALL SEQUENCES"


-- remove the table exception
-- here you cannot simply use SET ALL TABLES because you will lose the
ALL SEQUENCES part of the publication!!
-- So, instead you need to do like below
ALTER PUBLICATION pub DROP ALL TABLES;
ALTER PUBLICATION pub ADD ALL TABLES;
-- result is equivalent to "CREATE PUBLICATION ... FOR ALL TABLES, FOR
ALL SEQUENCES"

~~~

5.
+</programlisting></para>
+
+  <para>
+   Replace the publication's EXCEPT table list:

/EXCEPT table list/EXCEPT TABLE list/

~~~

6.
+  <para>
+   Change the publication to include all tables by removing any existing
+   EXCEPT table list:

The clearing of the EXCEPT TABLES is more like a side-effect, so I
think this can be worded differently.

SUGGESTION
Reset the publication to be a FOR ALL TABLES publication with no
excluded tables.

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