Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, 14 Apr 2026 at 12:00, Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
>
> Hi hackers,
>
> Following earlier work to support EXCEPT for FOR ALL TABLES [1]
> publications, starting this thread to extend the same capability to
> schema-level publications (TABLES IN SCHEMA).
>
> Currently, TABLES IN SCHEMA publishes all tables in a schema with no
> way to exclude a subset. Users who want to skip a few tables must
> switch to an explicit FOR TABLE list, which loses the convenience of
> schema-level publishing and requires ongoing maintenance as tables are
> added.
>
> Proposed syntax:
> ------------------------
> CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1, s1.t2);
> ALTER PUBLICATION pub ADD TABLES IN SCHEMA s1 EXCEPT (s1.t1);
> ALTER PUBLICATION pub SET TABLES IN SCHEMA s1 EXCEPT (s1.t1);
>
> Note: Tables in the EXCEPT clause must be schema-qualified to avoid
> ambiguity and must belong to the published schema; otherwise, an error
> is raised.
>
> Rules and behavior:
> ----------------------------
> 1) TABLES IN SCHEMA can be combined with FOR TABLE, but EXCEPT applies
> only to the schema clause and must appear immediately after it.
>
> Supported:
> CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), TABLE s2.t1;
> CREATE PUBLICATION pub FOR TABLE s2.t1, TABLES IN SCHEMA s1 EXCEPT (s1.t1);
> CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), s2
> EXCEPT (s2.t1)
>
> Not supported:
> CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, TABLE s2.t1 EXCEPT (s1.t1);
>
> -- This same rule applies to ALTER PUBLICATION ... ADD/SET.
>
> 2) Conflicting definitions
> Specifying the same table both in the EXCEPT clause and explicitly in
> the TABLE clause results in an error, as this creates a conflicting
> definition for the publication.
>
> 3) "ALTER PUBLICATION ... DROP TABLES IN SCHEMA" does not support
> EXCEPT clause. Whereas, dropping a schema also removes any associated
> entries from the EXCEPT list of the publication.
> -- To only remove/update except list entries, use SET instead.
>
> 4) Consistency with ALL TABLES EXCEPT rules:
> 4a) Excluding a partitioned root excludes all its partitions
> 4b) Individual partitions cannot be excluded directly; exclude the root table.
> 4c) Excluding an inheritance parent (without ONLY) also excludes its children.
>
> The patches are divided into three parts to simplify review:
> Patch-001: Basic framework to support EXCEPT in CREATE PUBLICATION
> ... TABLES IN SCHEMA
> Patch-002: Extend support to ALTER PUBLICATION ... ADD TABLES IN SCHEMA
> Patch-003: Extend support to ALTER PUBLICATION ... SET TABLES IN SCHEMA
>
> The patches are attached, feedback and suggestions are welcome.

When an EXCEPT table is specified together with TABLES IN SCHEMA sch1,
the EXCEPT entry is correctly created:
postgres=# create publication pub1 for tables in schema sch1 except (sch1.t1);
CREATE PUBLICATION

postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Tables from schemas:
"sch1"
Except tables:
"sch1.t1"
However, after dropping the schema from the publication, the
previously recorded EXCEPT table entry is still retained:
postgres=# alter publication pub1 drop TABLES IN SCHEMA sch1 ;
ALTER PUBLICATION
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Except tables:
"sch1.t1"

This seems incorrect, because once sch1 is no longer part of the
publication, retaining "sch1.t1" as an EXCEPT entry no longer has any
semantic meaning and leaves behind stale catalog state.

Regards,
Vignesh