On Tue, Aug 11, 2026 at 8:11 PM vignesh C <[email protected]> wrote: > > On Fri, 7 Aug 2026 at 17:26, vignesh C <[email protected]> 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
