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>
In-Reply-To: 
 <CAD21AoBbZEshyaK0PeiF_J4_S75EfF=Gcs=C+X-osoVoUnawuQ@mail.gmail.com>
From: Peter Smith <smithpb2250@gmail.com>
Date: Fri, 13 Mar 2026 14:24:45 +1100
Message-ID: 
 <CAHut+PssG+sHeV+Xo0g=S7xBb9FgDPjHYDR4iSuOdYXDq-Psng@mail.gmail.com>
Subject: Re: Skipping schema changes in publication
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
 shveta malik <shveta.malik@gmail.com>,
	Amit Kapila <amit.kapila16@gmail.com>, vignesh C <vignesh21@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"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAHut%2BPssG%2BsHeV%2BXo0g%3DS7xBb9FgDPjHYDR4iSuOdYXDq-Psng%40mail.gmail.com>
Precedence: bulk

On Fri, Mar 13, 2026 at 3:46=E2=80=AFAM Masahiko Sawada <sawada.mshk@gmail.=
com> wrote:
>
> On Thu, Mar 12, 2026 at 4:50=E2=80=AFAM Hayato Kuroda (Fujitsu)
> <kuroda.hayato@fujitsu.com> wrote:
> >
> > Dear Sawada-san,
> >
> > > I'm still unsure that the syntax like TABLE (t1, t2) for the exclusio=
n
> > > list is syntactically correct. The syntax of TABLE (...) is already
> > > used in a quite different way as follows (borrowed an example from
> > > stats_import.sql):
> > >
> > > CREATE FUNCTION stats_import.pg_statistic_get_difference(a text, b te=
xt)
> > > RETURNS TABLE (relname text, stats stats_import.pg_statistic_flat_t)
> > > BEGIN ATOMIC
> > >   WITH aset AS (SELECT * FROM stats_import.pg_statistic_flat(a)),
> > >        bset AS (SELECT * FROM stats_import.pg_statistic_flat(b))
> > >   SELECT a AS relname, a_minus_b::stats_import.pg_statistic_flat_t
> > >   FROM (TABLE aset EXCEPT TABLE bset) AS a_minus_b
> > >   UNION ALL
> > >   SELECT b AS relname, b_minus_a::stats_import.pg_statistic_flat_t
> > >   FROM (TABLE bset EXCEPT TABLE aset) AS b_minus_a;
> > > END;
> > >
> > > Wouldn't it be more appropriate to use a plural form or the IN
> > > keyword, such as EXCEPT TABLES (t1, t2) or EXCEPT TABLES IN (t1, t2)?
> >
> > But there is an existing syntax to list target tables, FOR TABLE t1, t2=
, t3...,
> > which does not have the plural. Current rule is to use the singular for
> > specifying the exact table, and we follow.
>
> "TABLE t1, t2, t3" and "TABLE (t1, t2, t3)" are different to me since
> the latter reminds me of TABLE (...) syntax for defining a returning
> table.
>
> > > Or if we might want to add multiple items in the EXCEPT clause in the
> > > future we can have parentheses around all exclusion items as follow:
> > >
> > > CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE t1, TABLE t2,
> > > TABLES IN SCHEMA s1);
> > > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1,
> > > TABLE
> > > t2), TABLE t3;
> >
> > I agree those alternatives could work, but one downside is the redundan=
t use of
> > "TABLE" when many tables are excluded. If we want to stay consistent wi=
th the
> > existing style, perhaps we could write it as:
> >
> > ```
> > CREATE PUBLICATION pub FOR ALL TABLES EXCPET (TABLE t1, t2, t3);
> > ```
> >
> > Because we have already been accepting the syntax like "FOR TABLE t1, t=
2, t3".
> >
>
> Yeah, we can omit the TABLE keyword for the second and subsequent
> tables. My whole point is that using different syntaxes for the
> inclusion list and the exclusion list would quite confuse users.
>

Hou-San (off-list) gave an interesting example of how it might look
when the example is combined with the hypothetical future ability to
exclude all schema tables from a "FOR ALL TABLES"
CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE t1, t2, TABLES IN
SCHEMA s1);

The syntax starts to look more complicated when there are multiple
schemas, but IMO it is still perfectly fine.
e.g.  FOR ALL TABLES EXCEPT (TABLE t1, t2, TABLES IN SCHEMA s1, s2);

AFAICT, it is doable. The synopsis syntax changes are something like this:
------
and publication_all_object is one of:
    ALL TABLES [ EXCEPT ( except_table_list_object [, ... ] ) ]
    ALL SEQUENCES

and except_table_list_object is:
    TABLE except_table_object [, ...]
    TABLES IN SCHEMA schema_name [, ...]

and except_table_object is:
    [ ONLY ] table_name [ * ]
------

This gives the user flexibility to name the type however they want to:
e.g. EXCEPT (TABLE t1, TABLE t2, TABLE t3)
e.g. EXCEPT (TABLE t1, t2, t3)
e.g. EXCEPT (TABLE t1, t2, TABLE t3)
e.g. EXCEPT (TABLE t1, TABLES IN SCHEMA s1, s2, TABLE t2, t3);

You get all the flexibility (e.g. which ellipses are expanded) for
free from the grammar, and it is already very typical lists in the
existing CREATE PUBLICATION command, which allow things like:
e.g. CREATE PUBLICATION pub FOR TABLE t1,t2, TABLE t3;

This syntax LGTM because
* User is not required to repeat TABLE keyword multiple times (but
they can if they want to)
* The exclusion lists inside the EXCEPT look and work the same as the
inclusion lists of CREATE PUBLICATION

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