Re: Support EXCEPT for TABLES IN SCHEMA publications

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.

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;

Should the EXCEPT clause be scoped to the tables selected by the
corresponding TABLES IN SCHEMA s1 clause, or should it apply globally
to related tables selected through other TABLES IN SCHEMA clauses as
well?

In other words, should s2.child remain included because it is
explicitly selected through TABLES IN SCHEMA s2, or should excluding
its parent s1.parent also cause the inherited child s2.child to be
excluded?

Thoughts?

Regards,
Vignesh