On Mon, Aug 24, 2026 at 12:55 PM Peter Smith <[email protected]> wrote: > > On Mon, Aug 24, 2026 at 3:40 PM shveta malik <[email protected]> wrote: > > > > On Fri, Aug 21, 2026 at 11:00 PM Nisha Moond <[email protected]> > > wrote: > > > > > > Hi, > > > After considering the discussion upthread, I think it is difficult to > > > handle all combinations with a simple rule without making the code > > > more complex. The main problematic cases seem to be when a > > > partition/inherited child is in a different schema from its root. > > > > > > For example, s1.root has two partitions: s1.p1 and s2.p2. > > > With the current patch v28/v29, if we do not allow: > > > CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), s2; > > > (ERROR) > > > then, we would also need to block several DDL operations to ensure the > > > publication can never reach this state later. Say if s2.p2 does not > > > exist yet and the creation of pub1 succeeds, then: > > > > > > 1. A partition of s1.root cannot later be created in another schema: > > > Block: CREATE TABLE s2.p2 PARTITION OF s1.root ... > > > > > > 2. A partition from a published schema cannot be attached to an excluded > > > root: > > > Block: ALTER TABLE s1.root ATTACH PARTITION s2.p2 ... > > > > > > 3. A partition of the excluded root cannot be moved to another schema: > > > Block: ALTER TABLE s1.p1 SET SCHEMA s2; > > > > > > 4. Another schema s3 having s3.p3 (root's part) cannot later be added > > > to the publication: > > > Block: ALTER PUBLICATION pub1 ADD TABLES IN SCHEMA s3; > > > > > > IMO, especially for cases 1 and 3, table DDL should not depend on > > > publication metadata. So I don't think adding these DDL restrictions > > > is a good approach. > > > > I agree with the analysis. > > > > > > > > Another option is to simply allow FOR TABLES IN SCHEMA s1 EXCEPT > > > (s1.root), s2; and exclude the full s1.root tree along with s2.p2. as > > > suggested at [1] > > > However, this conflicts with the other case where FOR TABLES IN SCHEMA > > > s1 EXCEPT (s1.root), TABLE s2.p2; is rejected, as discussed earlier. > > > > > > So the question is whether we can simplify the rule further. > > > On HEAD, I tested combinations where multiple publications in the same > > > subscription have conflicting rules. > > > For example: > > > pub1: FOR ALL TABLES EXCEPT (s1.root); > > > pub2: FOR TABLE s1.root; > > > > > > If the subscription includes both publications, s1.root is still > > > replicated through pub2. > > > > > > Similarly: > > > pub1: FOR ALL TABLES EXCEPT (s1.root); > > > pub2: FOR TABLE s1.p1; > > > > > > and > > > > > > pub1: FOR ALL TABLES EXCEPT (s1.root); > > > pub2: FOR TABLE s2.p2; > > > > > > In both cases, the subscriber receives s1.p1 / s2.p2 through pub2. > > > > Okay. I see these cases but I don't think we can comapre these cases > > with single publication case. > > > > > This is because pgoutput makes the publication decision independently > > > for each publication. So effectively, INCLUSION wins over EXCLUSION. I > > > think we could apply the same rule to the publication definition > > > itself. > > > > > > For example: (Partitions case) > > > > > > 1. FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), s2; > > > -- Exclude s1.root and its children in s1, such as s1.p1. > > > -- Publish s2.p2 because s2 is explicitly included. > > > > > > The limitation is that there is still no way to exclude the complete > > > s1.root tree when publishing both s1 and s2. This could potentially be > > > addressed later by allowing partition children in the EXCEPT clause. > > > > > > 2. FOR TABLE s1.p1, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root); > > > -- Allow s1.p1 to be published. The EXCEPT only excludes s1.root and > > > other children in the same schema. > > > > > > 3. FOR TABLE s2.p2, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root); > > > -- Same as case 1. Exclude s1.root and its children in s1, while > > > s2.p2 is published. > > > > > > 4. FOR TABLE s1.root, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root); > > > -- Allow s1.root to be published and ignore the EXCEPT entry, with a > > > notice/warning. > > > > > > For partitions, we may need some changes in pgoutput and the relevant > > > ancestor lookup code to ensure inclusion always wins over exclusion. > > > > > > I think the same rule can simplify inheritance trees as well: > > > > > > 5. FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent), s2; > > > -- Exclude s1.parent and its children in s1, but publish s2.child. > > > > > > 6. FOR TABLE s1.parent, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child); > > > -- Publish the full s1.parent tree. The explicit inclusion of > > > s1.parent tree overrides the exclusion of s2.child, with a > > > notice/warning. > > > > > > 7. FOR TABLE s2.child, TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent); > > > -- Same as case 5: publish s2.child, while excluding s1.parent and > > > its children in s1. > > > > > > 8. FOR TABLES IN SCHEMA s1, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child); > > > -- Same as case 6: publish the full s1.parent tree, since s1 is > > > included, so s2.child is published. The inclusion overrides the > > > EXCEPT, with a notice/warning. > > > > > > With this approach, I don't think we need additional DDL restrictions. > > > New tables/partitions added under an explicitly included schema would > > > simply be included. > > > > > > I tried to cover the conflicting combinations including the ones > > > discussed upthread. Please let me know if there are other cases that > > > would still be ambiguous with this rule. > > > > > > Thoughts? > > > > > > > While the proposal to let inclusion win over conflicting rules avoids > > adding complex alter-table restrictions, I think it has a significant > > drawback: relying on a NOTICE/warning to override an explicit EXCEPT > > clause feels risky. > > > > An EXCEPT clause is an explicit data-filtering boundary. If a DBA/user > > explicitly excludes a table or its child, for example, because it > > contains sensitive data, I don't think we should silently override > > that exclusion just because another inclusion rule happens to cover > > the same relation indirectly. A NOTICE or warning could easily be > > missed, and we could end up publishing data that the user explicitly > > intended to exclude. So, to me, the concern is not just that the > > behavior could be surprising; we would actually be publishing > > something that the user explicitly asked us not to publish. > > +1 If the user says EXCEPT then the only safe thing to do is what the > user asked for. To do otherwise is effectively saying: “We see you > wanted to exclude this table but we are going to publish it anyway > because we figure you just made a mistake”. > > > > > An Alternative Solution could be 'Strict Schema-Bound Isolation' > > (Peter also suggested something similar earlier if I am not wrong, > > which did not reach to conclusion). To avoid both DDL-blocking and > > unsafe Exclusion overrides, we could adopt a Strict Schema-Bound > > Isolation model. > > > > Core rule: Schema boundaries act as hard firewalls. An EXCEPT clause > > defined for a specific schema applies only to objects physically > > residing in that schema. Inclusion or exclusion through one schema > > does not recursively "bleed over" into another schema or affect > > partitions or inherited children that reside there. > > Something seems off here. The inclusion rules are already > well-established. Partitioning really *does* bleed already into other > unpublished schemas currently on HEAD. > > e.g. > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1; > > Inheritance: > s1.parent INCLUDED > s1.child INCLUDED > s2.child NOT INCLUDED > > Partitioning: > s1.root INCLUDED > s1.part INCLUDED > s2.part INCLUDED (it cascades to other schemas) >
Right I found this case specially documented also in [1]: See: "When a partitioned table is published via a schema-level publication, all of its existing and future partitions are implicitly considered to be part of the publication, regardless of whether they are from the publication schema or not." Thus, on re-thinking, we can apply a similar EXCLUSION rule to partitions: excluding a partition root excludes all of its partitions, even if their schemas are included separately. The only addition I would make is that if a partition is explicitly included by name, then it remains included. Partition Case Rules: ----------------------------------- a) By default, mentioning a 'partition root 'means that its entire partition tree is included/excluded, irrespective of schema boundaries, consistent with HEAD. b) An explicitly mentioned partition is allowed and takes precedence over the partition-tree exclusion. Going through the cases again: 1. FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), TABLES IN SCHEMA s2; Excludes root: By default all its parition gets excluded, even the ones present in s2. 2. FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), FOR TABLE s1.p1; Excludes s1.root: By default all its partitions get excluded except s1.p1. s1.p1 is still published as the user has explicitly mentioned it. 3. FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), FOR TABLE s2.p2; Excludes s1.root: By default all its partitions get excluded except s2.p2. s2.p2 is still published as the user has explicitly mentioned it. 4. FOR TABLE s1.root, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root); ERROR scenario: We cannot have the exact same table (root in this case) included and excluded. Inheritance Case Rules: ----------------------------------- a) An EXCEPT clause associated with a schema does not follow the inheritance hierarchy across schema boundaries. It only excludes the parent and its descendants selected through that schema. b) An explicitly mentioned child is allowed and takes precedence over an exclusion inherited through the parent. Going through the cases again: 5. FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent), TABLES IN SCHEMA s2; --s1.parent and its children in s1 are excluded; while s2.child is published. --if 'ONLY' is mentioned in EXCEPT, only s1.parent is excluded; all its children in s1 and s2 are published. 6. FOR TABLE s1.parent, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child); --s1.parent and all its children belonging to any schema should be published because inclusion of s1.parent is not schema-bound. This would mean, any child present in s3 would also be published, similar to HEAD (see [2]). s2.child will be excluded, as explicitly given by the user. --if 'ONLY' s1.parent is mentioned, only s1.parent is published. s2.child is excluded by EXCEPT rule of s2. 7. FOR TABLE s2.child, TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent); -- s1.parent and all its children in s1 are excluded; s2.child is published. -- if 'ONLY' is mentioned in EXCEPT, only s1.parent is excluded; all its children in s1 are published along with s2.child. 8. FOR TABLES IN SCHEMA s1, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child); --s1.parent is published with all its children present in s1 alone; s2.child is excluded. This would mean unlike case 6, if any child is present in s3, that will not be published. Taking the behaviour of HEAD as the base (see [2]). 9. A new case: FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent), TABLE s1.child; --s1.parent and its children in s1 are excluded; while s1.child is still published. [1]: https://www.postgresql.org/docs/19/sql-createpublication.html [2]: https://www.postgresql.org/message-id/CAHut%2BPvpFR0%3D-AT_0QfjtWFov5ZT4yrfJgbAd-N7w%3DCzSm708g%40mail.gmail.com ~~ Please reveiw this and let me know. thanks Shveta
