On Tue, Sep 1, 2026 at 12:13 PM Peter Smith <[email protected]> wrote: > > On Tue, Aug 25, 2026 at 6:49 PM shveta malik <[email protected]> wrote: > > > > On Tue, Aug 25, 2026 at 1:05 PM Peter Smith <[email protected]> wrote: > > > > > > On Mon, Aug 24, 2026 at 7:55 PM shveta malik <[email protected]> > > > 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
