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?
>
> 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
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?
>
> 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.
The suggested rules might still be OK -- I just don't know yet.
~~~
Finally, there is another nasty hand-grenade to be lobbed into this
thread, otherwise it might get overlooked...
AFAIK, it is possible to have diamond-shaped inheritance:
e.g.
CREATE TABLE vehicle ( name text );
CREATE TABLE boat () INHERITS (vehicle);
CREATE TABLE car () INHERITS (vehicle);
CREATE TABLE boatcar () INHERITS (boat,car);
INSERT INTO boatcar (name) VALUES ('amphibious car');
vehicle
/\
/ \
/ \
boat car
\ /
\ /
\/
boatcar
I have no idea what problems that's going to cause for SCHEMA EXCEPT,
but it can't be good, and it needs to be handled somehow.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
