public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Smith <[email protected]>
To: Nisha Moond <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
Date: Tue, 16 Jun 2026 11:21:51 +1000
Message-ID: <CAHut+Pv=rXFatEQzHa6at41k0oe+GM6u-Mnsjp8RernOaXaW6g@mail.gmail.com> (raw)
In-Reply-To: <CABdArM5j8gX_xYDU6_aPEOuPAiCGtF8QyrpHeqZXQDsQ_wpJKA@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>
<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>
On Mon, Jun 15, 2026 at 9:31 PM Nisha Moond <[email protected]> wrote:
>
> On Mon, Jun 15, 2026 at 11:50 AM Amit Kapila <[email protected]> wrote:
> >
> > On Thu, Jun 11, 2026 at 12:17 PM Peter Smith <[email protected]> wrote:
> > >
> > > //////////
> > > v12-0002
> > >
> > > ======
> > > doc/src/sgml/ref/alter_publication.sgml
> > >
> > > 1.
> > > + <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>
> > >
> > >
> > > I think the sentence "If the table is subsequently moved back..." is
> > > overkill, and does not need to be said. The prior info "the exclusion
> > > is removed" already tells me the exclusion is gone, and I think is
> > > reasonable to assume "removed" means that it is gone for good, with no
> > > ambiguity that it might magically come back.
> > >
> > > YMMV. Leave it as-is if you prefer.
> > >
> >
> > I feel it is okay to keep the proposed sentence to avoid any ambiguity
> > by the user to consider the schema-scope state is symmetric.
> >
>
> Okay, I have kept this para as-it-is.
>
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:
======
test_pub=# CREATE SCHEMA myschema;
CREATE SCHEMA
test_pub=# CREATE TABLE t1(A INT);
CREATE TABLE
test_pub=# CREATE TABLE t2(A INT);
CREATE TABLE
test_pub=# CREATE TABLE myschema.myt1(A INT);
CREATE TABLE
test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT (TABLE myschema.myt1);
CREATE PUBLICATION
test_pub=# \d myschema.myt1
Table "myschema.myt1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Excluded from publications:
"pub1"
test_pub=# \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=# ALTER TABLE myschema.myt1 SET SCHEMA public;
ALTER TABLE
test_pub=# \d myt1
Table "public.myt1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Excluded from publications:
"pub1"
test_pub=# \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"
======
This experiment shows that moving the table did *not* remove the exclusion.
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;"
Thoughts?
======
Kind Regards,
Peter Smith.
Fujitsu Australia.
view thread (41+ 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], [email protected]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
In-Reply-To: <CAHut+Pv=rXFatEQzHa6at41k0oe+GM6u-Mnsjp8RernOaXaW6g@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