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) ~~~ So you can't make this a "Core Rule" without changing the existing HEAD behaviour for partition inclusion. I doubt that will be acceptable.Perhaps you meant the "Strict Schema-Bound Isolation" rule is only for exclusions. Maybe that is ok. OTOH making the inclusion/exclusion cross-schema rules different might not be good either. ====== Kind Regards, Peter Smith. Fujitsu Australia
