Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Aug 25, 2026 at 12:08 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Aug 24, 2026 at 3:25 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > 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.
> >
> > 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.
> >
>
> Both the above cases seem to fall in the same category though for the
> first one the table is not explicitly mentioned but TABLES IN SCHEMA
> does mean all tables in that schema. The another point I see in making
> both above cases behave similar (follow what you mentioned in 2) is
> that we will follow the same behavior when two different publications
> (pub1 FOR TABLES IN SCHEMA s1 EXCEPT (s1.root) and pub2 FOR TABLES IN
> SCHEMA s2;) that specify the two clauses separately are combined by
> SUBSCRIPTION.

The drawback I see with this approach is that it leaves the user with
no way to exclude an entire root tree (including s2.p2) while still
including everything else in schema s2. If we are relying on a future
EXCEPT (leaf_partition) implementation to address this, that does not
seem practical to me. The 'exclusion on partition' approach already
had numerous issues even for a simple ALL TABLES publication, and with
the additional cross-schema complexities here, I don't see it being
addressed anytime soon.

Additionally, if we adopt the analogy that a single publication with
mixed rules should behave exactly like multiple independent
publications combined on a subscription, then we need to apply that
principle consistently across all cases. Example, for inheritance
case:
FOR TABLE s1.parent, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child);

Currently, this explicitly excludes s2.child. But if we treat this as
equivalent to two independent publications combined together: pub1:
FOR TABLE s1.parent; pub2: TABLES IN SCHEMA s2 EXCEPT (TABLE
s2.child) ; then, under the "inclusion wins" rule, s2.child would be
published. The "inclusion wins" (see [1]) approach had a basic
problem: to achieve it, we had to ignore EXCEPTs in many cases, which
didn't seem acceptable to me. But let me know what do you think.

[1]: https://www.postgresql.org/message-id/CABdArM7javjNBWoS0d8qgESaMqdja-0JG3wr%2BWVn6%3DbRg92m4A%40mail.gmail.com

thanks
Shveta