On Mon, Aug 24, 2026 at 11:10 AM 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. > > 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. > > How this resolves the cases from your email: > > a). Clean Cross-Schema Separation (Cases 1, 3, 5, 7) > ---------------------------------- > Partitions: > 1. FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), s2; > --s1.root excluded, everything under s2 even if it is parition of > s1.root is published > > 3. FOR TABLE s2.p2, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root); > --s1.root excluded, s2.p2 published > > Inheritance: > 5. FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent), s2; > --s1.parent excluded, everything under s2 even if it is child of > s1.parent is published. > > 7. FOR TABLE s2.child, TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent); > --s1.parent excluded, s2.child is published as it is explcilty mentioned. > > > b. Local Exceptions are Respected (Cases 6 & 8) > ---------------------------------- > 6. FOR TABLE s1.parent, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child); > --s1.parent published with all its children present in s1, > s2.child is excluded.
Based on experiments done by Peter in [1], I would like to revise this behavious. In this case: --s1.parent and all its children belonging to any schema should be published because inclusion of s1.parent is not schema-bound. But s2.child should be excluded as explicitly given by the user. > > 8. FOR TABLES IN SCHEMA s1, TABLES IN SCHEMA s2 EXCEPT (TABLE > s2.child); > --s1.parent published with all its children present in s1, > s2.child is excluded. Here s1.s1.parent is schema-bound as it is included by schema s1, and thus the behaviour I stated above remains the same. [1] https://www.postgresql.org/message-id/CAHut%2BPvpFR0%3D-AT_0QfjtWFov5ZT4yrfJgbAd-N7w%3DCzSm708g%40mail.gmail.com > > c. Local Table Precedence within the Same Schema (Cases 2 & 4) > ---------------------------------- > 2. FOR TABLE s1.p1, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root); > -- parition s1.p1 is published. s1.root and other paritions in the > same schema are excluded. The same rule applies to inherited tables > too. > > 4. FOR TABLE s1.root, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root); > --This is clearly an ERROR scenario as user is giving exact same > inclusion and exclusion for the same schema. > > > NOTE: > The case where parition is included while ROOT is excluded and > publish_via_partition_root is TRUE (e.g., case a.1), we may not even > publish parition itself. Check this once please. But this case will > not be any different from existing case on HEAD where partition alone > is included in publication with publish_via_partition_root=true. > Example: > > create publication pub1 for table s2.part with > (publish_via_partition_root = true); > > ~~ > > > Benefits I see: > 1) We avoid any other issues requiring DDL blocking. Let me know if > you see any. > b) We follow what the user explicitly asked to EXCLUDE without any > notice/warning to override their choice. > c) We can clearly document it: 'Inclusion/exclusion rules do not bleed > over to other schema. > > Nisha, can you please review this in detail? I might have overlooked > something. > > thanks > Shveta
