On Sat, Aug 22, 2026 at 3:30 AM 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. > > 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. > > 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.
Any combination of clauses used to describe behaviour also needs to show what happens for table inheritance using the "ONLY" keyword. I think you've omitted that combination. > > 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. No. You wrote “publish the full s1.parent tree, since s1 is included, so s2.child is published “, but that is incorrect AFAIK. Cross-schema inclusion behaves differently for inheritance and for partitioning. For “TABLES IN SCHEMA s1” the s1.parent will *not* reach out to include descendants from other schemas. (I demonstrated this already with my SQL CASE 2 example [1]) ====== [1] https://www.postgresql.org/message-id/CAHut%2BPvpFR0%3D-AT_0QfjtWFov5ZT4yrfJgbAd-N7w%3DCzSm708g%40mail.gmail.com Kind Regards, Peter Smith. Fujitsu Australia
