Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Jun 16, 2026 at 2:36 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Tue, Jun 16, 2026 at 6:52 AM Peter Smith <smithpb2250(at)gmail(dot)com> 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:
> >
> > ======
> >
> > 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;"
> >
>
> 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?

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