Re: Support EXCEPT for TABLES IN SCHEMA publications

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.

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