Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Aug 11, 2026 at 8:11 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Fri, 7 Aug 2026 at 17:26, vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > I noticed what appears to be an unexpected behavior with publications
> > involving inherited tables and wanted to check whether this is
> > intentional. The following steps reproduce the behavior:
> > CREATE SCHEMA s1;
> > CREATE SCHEMA s2;
> > CREATE TABLE s1.parent (a int);
> > CREATE TABLE s2.child (b int) INHERITS (s1.parent);
> >
> > -- Create a publication excluding the parent table
> > CREATE PUBLICATION p FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent);
> >
> > The publication excludes both the parent and child tables:
> > postgres=# \dRp+
> > Publication p
> > Owner | All tables | All sequences | Inserts | Updates |
> > Deletes | Truncates | Generated columns | Via root | Description
> > ----------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
> > test | f | f | t | t | t
> > | t | none | f |
> > Tables from schemas:
> > "s1"
> > Except tables:
> > "s1.parent"
> > "s2.child"
>
> For this issue, I compared the behavior of inherited tables across
> schemas with the behavior of the existing 'TABLES IN SCHEMA'
> publications.
> -- Inherited tables across different schemas
> CREATE SCHEMA s1;
> CREATE SCHEMA s2;
> CREATE TABLE s1.parent (a int);
> CREATE TABLE s2.child (b int) INHERITS (s1.parent);
>
> -- Initial data
> INSERT INTO s2.child VALUES (10, 20);
> INSERT INTO s1.parent VALUES (100);
>
> ### Test 1
> Create a publication that includes only schema s1:
> CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1;
>
> On the subscriber, the initial synchronization gives:
> postgres=# SELECT * FROM s1.parent;
> a
> -----
> 100
> (1 row)
>
> postgres=# SELECT * FROM s2.child;
> a | b
> ---+---
> (0 rows)
>
> Here, s1.parent is included because it belongs to schema s1, which is
> part of the publication pub1. s2.child is not replicated because it
> belongs to schema s2, which is not part of the publication pub1.
>
> Similarly, in the proposed patch for the following scenario-1:
> CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent);
>
> We can exclude s1.parent. The resulting publication state would have
> s1.parent represented with prexcept = true, while s2.child would not
> be present in pg_publication_rel because schema s2 was not selected.

Yes, this looks like the obvious choice since the user included only
s1. But even though it's the obvious choice, it could be slightly
misleading because users expect all children to be excluded too
(default * behaviour for inherited tables), which isn't the case.

The behaviour you mentioned on HEAD does not resemble our case because
the user specified 'TABLES IN SCHEMA s1' which doesn't clarify the
expected behaviour when an inherited child table exists in another
schema. The more obvious choice seems to be including only the tables
from the schema as instructed by the user, and thus the behavior is
not misleading. However, in our case, the behaviour could be
misleading.

> I was not sure about the behavior when both schemas are explicitly
> selected in the following scenario-2:
> CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT (TABLE
> s1.parent), TABLES IN SCHEMA s2;

Ideally, this should result in an error: 'conflicting except'. We are
trying to exclude s1.parent and all its descendants explicitly through
the first using EXCEPT s1.parent* while we are trying to include
schema s2 and all its tables which include the excluded children. But
could it complicate implementation and understanding of behaviour? I
am thinking about it.

This is somewhat similar to case discussed in [1], where we concluded
that all partitions should also be excluded. But I am rethinking on
all these scenarios now wrt inherited tables. Parition and inherited
tables are different in a way that user may give direct instruction
for inherited tables by giving */ONLY for the depth of
inclusion/exclusion while that is not the case for paritions and thus
2 can not be compared directly.

[1]: https://www.postgresql.org/message-id/CABdArM4nVk-umQ_VXoGCrb7gQ_VcM5gHFcDOOa9EQbiUC-0EsA%40mail.gmail.com

thanks
Shveta