Re: Support EXCEPT for TABLES IN SCHEMA publications

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.
>
> ~~~
>
> Finally, there is another nasty hand-grenade to be lobbed into this
> thread, otherwise it might get overlooked...

Yes, I think we completely missed it in our earlier analysis. Thanks
for pointing it out.

> 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

The one tricky scenario I see here is when 'boat' is included while
'car' is excluded: what happens to boatcar? Does it stay or is it
excluded? We need to establish a rule here. I will think more about
this.

thanks
Shveta