Re: Support EXCEPT for TABLES IN SCHEMA publications

On Mon, Aug 24, 2026 at 11:10 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Fri, Aug 21, 2026 at 11:00 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> 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