Re: Support EXCEPT for TABLES IN SCHEMA publications

On Mon, Jun 15, 2026 at 9:31 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
>
> On Mon, Jun 15, 2026 at 11:50 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Thu, Jun 11, 2026 at 12:17 PM Peter Smith <smithpb2250@gmail.com> 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.