Re: Support EXCEPT for TABLES IN SCHEMA publications

On Mon, Aug 24, 2026 at 3:25 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Mon, Aug 24, 2026 at 12:55 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> >
> > On Mon, Aug 24, 2026 at 3:40 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > >
> > > 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.
>

To me, keeping the PARTITION and INHERITANCE behavior different for
EXCEPT also seems reasonable, since their behavior is already
different on HEAD.

>
> 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.
>

I’m okay with the proposed behavior. It will avoid the required DDL
blocking mentioned at [1]

> 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.
>

Agree.

>
> 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.
>

As for the TABLES IN SCHEMA, inclusion of an inherited parent is
schema-scoped. I re-analyzed the cases under these rules where the two
clauses in the same command conflict, i.e., one includes and the other
excludes the same table or tree within their respective scopes.

I think cases 5, 7, and 8 are not really conflicting, since s1 and s2
do not cross each other’s schema boundaries. So, no additional
handling should be needed for these cases.

The only conflicting cases I see are 6, 9, and the following new case:
10) FOR TABLE s1.parent, TABLES IN SCHEMA s1 EXCEPT (s1.child1)
-- s1.parent and all its children are published, except s1.child1,
as explicitly given by the user.

The remaining possible conflicts seem to be clear ERROR cases:
- FOR TABLE s1.parent, TABLES IN SCHEMA s1 EXCEPT (s1.parent)
- FOR TABLE s1.parent, TABLES IN SCHEMA s1 EXCEPT (ONLY s1.parent)
- FOR TABLE s1.child, TABLES IN SCHEMA s1 EXCEPT (s1.child)
- FOR TABLE s2.child, TABLES IN SCHEMA s2 EXCEPT (s2.child)

>
> [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.
>

With these rules, I think we can reduce code complexity and avoid the
need for any additional table DDL blocking.

I’ll think about this a bit more and get back.

--
Thanks,
Nisha