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+Pv=rXFatEQzHa6at41k0oe+GM6u-Mnsjp8RernOaXaW6g@mail.gmail.com>
 <CAA4eK1J_QP8CXCshaCy_01ALQQrDSzoTHfXKcAbjiEvV-RkOOw@mail.gmail.com>
In-Reply-To: 
 <CAA4eK1J_QP8CXCshaCy_01ALQQrDSzoTHfXKcAbjiEvV-RkOOw@mail.gmail.com>
From: Peter Smith <smithpb2250@gmail.com>
Date: Tue, 16 Jun 2026 16:36:10 +1000
Message-ID: 
 <CAHut+Psx=R3kYBiOR-X_MUQtETW5s8pZuXB+J=cWumQjbuXeKQ@mail.gmail.com>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Nisha Moond <nisha.moond412@gmail.com>,
 Zsolt Parragi <zsolt.parragi@percona.com>,
	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%2BPsx%3DR3kYBiOR-X_MUQtETW5s8pZuXB%2BJ%3DcWumQjbuXeKQ%40mail.gmail.com>
Precedence: bulk

On Tue, Jun 16, 2026 at 2:36=E2=80=AFPM Amit Kapila <amit.kapila16@gmail.co=
m> wrote:
>
> On Tue, Jun 16, 2026 at 6:52=E2=80=AFAM Peter Smith <smithpb2250@gmail.co=
m> wrote:
> >
> > This discussion about the impact of ALTER TABLE ... SET SCHEMA made me
> > wonder what happens for the existing PG19 case of FOR ALL TABLES
> > EXCEPT (TABLE ...)
> >
> > It turns out to be quite different:
> >
> > =3D=3D=3D=3D=3D=3D
> >
> > test_pub=3D# CREATE SCHEMA myschema;
> > CREATE SCHEMA
> > test_pub=3D# CREATE TABLE t1(A INT);
> > CREATE TABLE
> > test_pub=3D# CREATE TABLE t2(A INT);
> > CREATE TABLE
> > test_pub=3D# CREATE TABLE myschema.myt1(A INT);
> > CREATE TABLE
> > test_pub=3D# CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT (TABLE mysch=
ema.myt1);
> > CREATE PUBLICATION
> > test_pub=3D# \d myschema.myt1
> >                Table "myschema.myt1"
> >  Column |  Type   | Collation | Nullable | Default
> > --------+---------+-----------+----------+---------
> >  a      | integer |           |          |
> > Excluded from publications:
> >     "pub1"
> >
> > test_pub=3D# \dRp+ pub1
> >                                                        Publication pub1
> >   Owner   | All tables | All sequences | Inserts | Updates | Deletes |
> > Truncates | Generated columns | Via root | Descri
> > ption
> > ----------+------------+---------------+---------+---------+---------+-=
----------+-------------------+----------+-------
> > ------
> >  postgres | t          | f             | t       | t       | t       |
> > t         | none              | f        |
> > Except tables:
> >     "myschema.myt1"
> >
> > test_pub=3D# ALTER TABLE myschema.myt1 SET SCHEMA public;
> > ALTER TABLE
> > test_pub=3D# \d myt1
> >                 Table "public.myt1"
> >  Column |  Type   | Collation | Nullable | Default
> > --------+---------+-----------+----------+---------
> >  a      | integer |           |          |
> > Excluded from publications:
> >     "pub1"
> >
> > test_pub=3D# \dRp+ pub1
> >                                                        Publication pub1
> >   Owner   | All tables | All sequences | Inserts | Updates | Deletes |
> > Truncates | Generated columns | Via root | Descri
> > ption
> > ----------+------------+---------------+---------+---------+---------+-=
----------+-------------------+----------+-------
> > ------
> >  postgres | t          | f             | t       | t       | t       |
> > t         | none              | f        |
> > Except tables:
> >     "public.myt1"
> >
> > =3D=3D=3D=3D=3D=3D
> >
> > This experiment shows that moving the table did *not* remove the exclus=
ion.
> >
> > It is kind of "explainable" in hindsight because the exclusion is by
> > table OID, not name, so it follows the table around when it is moved.
> > I don't think this is what a user would expect, given that they
> > explicitly asked to exclude it from a different schema.
> >
> > Is it a PG19 exclusion bug?
> >
> > Is it behaviour that needs more documenting?
> >
> > ~
> >
> > IMO it seemed like a bug of the PG19 FOR ALL TABLES EXCEPT, because it
> > is the opposite of what the new FOR TABLES IN SCHEMA EXCEPT patch
> > does:
> > "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;"
> >
>
> No, I don't think this is a bug for PG19 and the new behaviour for
> PG20 is intentional and required because in this case, the EXCEPT
> clause is schema-scoped, so once schema is changed, the table should
> be removed from the exclusion list. OTOH, in PG19, the exclusion list
> follows the table-level exclusion based on its OID as we can see in
> the example provided by you. I think we can consider adding a line for
> this in docs if you and others feel that such explicit mention can
> avoid ambiguity around this. How about something like the following as
> a separate para in EXCEPT clause description: Once a table is
> excluded, the exclusion applies to that table itself, regardless of
> its name or schema. Renaming the table or moving it to another schema
> with <command>ALTER TABLE ... SET SCHEMA</command> does not cancel the
> exclusion."
>

OK. The explanatory text LGTM.

What's the next step? Should I create a new thread/patch to deal with this?

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