Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Sep 1, 2026 at 12:13 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Tue, Aug 25, 2026 at 6:49 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Tue, Aug 25, 2026 at 1:05 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> > >
> > > On Mon, Aug 24, 2026 at 7:55 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.
> > >
> > > OK.
> > > Also (c). A partition cannot be mentioned as an explicit *exclusion*
> > > because we only allow partition roots in EXCEPT.
> > > Also (d). "EXCEPT (TABLE ONLY root)" -- the ONLY is completely ignored
> > > for partitions so that is identical to "EXCEPT (TABLE root)", right?
> >
> > Right.
> >
> > > >
> > > > 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.
> > > >
> > > > 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.
> > > >
> > > >
> > > > 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.
> > >
> > > OK.
> > > For (b) you said explicit child takes precedence over exclusion
> > > inherited by parent.
> > > I think example is: TABLE IN SCHEMA s1 (EXCEPT s1.parent), TABLE
> > > s1.child; --- child stays
> >
> > yes. Right.
> >
> > > But your rules don't say anything what happens for explicit child
> > > taking precedence inherited **inclusion**
> > > e.g. TABLE s1.parent, TABLES IN SCHEMA s1 EXCEPT (TABLE s1.child); --
> > > I think child excluded, right?
> >
> > Yes. The child is excluded because the user explicitly mentioned it.
> >
> > > >
> > > > Please reveiw this and let me know.
> > > >
> > >
> > > AFAICT all your example SQL results LGTM, but, IMO there are many
> > > dozens more combinations (I have itemized over 100 variations so
> > > far...). Sure, all these might all fall into just a few same-behaviour
> > > categories, but OTOH there could be some unaccounted edge-cases. I am
> > > currently trying to make a giant matrix of them all, but it is taking
> > > much longer than expected.
> >
> > yes, the matrix will help; otherwise, we may end up missing some scenarios.
> >
> > > The suggested rules might still be OK -- I just don't know yet.
> > >
> > > ~~~
>
>
> Each time new EXCEPT rules are proposed in this thread, they come with
> a worked examples, but I felt there was still a risk that other
> combinations could be broken and go unnoticed. Then, when a
> problematic combination is later encountered, we're back to square one
> again.
>
> To help avoid that, I made a matrix of *many* combinations (~300),
> covering both inheritance and partitioning.

Thanks Peter. I think that if we come up with a new proposal, we
should refer to this matrix to see that all the cases resolve
correctly.

I have gone through the matrix and it looks good to me. It should
serve as a starting point to finalize any proposal.

> Also, TBH I was getting lost keeping track of what "case 1", "case 2",
> etc. meant. In this matrix, every case can instead be referred to like
> "11x23" — a combo of clauses "11" and "23" (the first digit being the
> schema). This makes it easy to refer to any of the 300 combinations
> unambiguously. Furthermore, the same combination numbers (e.g. 11x23)
> mean the same thing in both the INHERITANCE and PARTITIONING matrices,
> so the two can be directly compared.
>
> PSA the doc containing the matrices, rules, results, diagrams, and a
> key for interpreting them.
>
> My conclusion is that the rules proposed by Shveta ([1] and also
> included in the document) are sound; they can be applied to all these
> combinations to give unambiguous results. FWIW, my AI checking
> confirms the matrix results are consistent.
>
> Of course, there are more possible variations (e.g. diamond
> inheritance shapes), but IMO these 300 test cases give a much more
> solid starting point.
>
> ======
> [1] https://www.postgresql.org/message-id/CAJpy0uAoRpAoWHWBrGmkCXeEEeMa9R0GXPmKmCrrwLHvgmzOfQ%40mail.gmail.com
>
> Kind Regards,
> Peter Smith.
> Fujitsu Australia