Re: Support EXCEPT for TABLES IN SCHEMA publications

On Thu, Aug 13, 2026 at 3:11 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Aug 12, 2026 at 11:42 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > I thought some more. Please find my analysis and opinion on all
> > related scenarios:
> >
> > Metadata:
> > schema s1: parent
> > schema s2: parition/child of parent.
> >
> > Scenarios:
> > a) s1.s1.parent is a partitioned table, and its partitions live in
> > another schema. Or
> > b) s1.parent is a parent table, and its descendants/inherited tables
> > live in another schema.
> >
> > ~~
> >
> > Now consider the exclusion cases:
> >
> > Case 1: No contradiction in EXCEPT
> > -----------------------------------------
> > CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent*);
> >
> > My opinion:
> > In both cases a) and b), s1.parent will be included in the
> > publication. Its partitions or descendants, however, will not be
> > included because they belong to another schema (s2), which is not
> > included in the publication. Thus, they are effectively excluded due
> > to the absence of a schema inclusion rule, rather than by the EXCEPT
> > clause.
> >
> >
> > Case 2: Indirect contradiction in EXCEPT
> > --------------------------------------------------------
> > CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT (TABLE
> > s1.parent*), TABLES IN SCHEMA s2;
> >
> > The above command creates an indirect logical contradiction. The
> > child/partition is explicitly brought into the publication pool
> > through the schema inclusion rule (TABLES IN SCHEMA s2), while at the
> > same time being excluded by the recursive EXCEPT (TABLE s1.parent*)
> > clause of another schema. I believe this should result in the same
> > error that we have already concluded and implemented in the following
> > case:
> >
> >
> > Case 3: Direct contradiction in EXCEPT
> > --------------------------------------------------------
> > CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT (TABLE
> > s1.parent*), s2.partition/child;
> >
> > It gives error as per current implementation. See [3].
> >
> > ~~
> >
> > If we implement what I suggested above, Case 2's implementation for
> > partitions would be contrary to what we decided at [2] earlier for the
> > scenario at [1]. However, after reconsidering all scenarios, I feel
> > the new approach (suggested above) makes more sense and is also
> > consistent with Case 3. Case 2 and Case 3 are logically the same and
> > should have the same behaviour, IMO. Thoughts?
> >
>
> Though this will block the case where one has partition/inheritance
> hierarchy across schema's especially if the user wants to exclude just
> those partitions/inherited_tables and allow other tables from those
> schemas, still I feel it is okay to be restrictive in the first
> version of the feature for this case and follow what you are
> proposing.

Okay.

> BTW, I have also considered diverging the behavior of partition and
> inheritance tables for this case (such that for partitions, all
> hierarchy to be excluded irrespective of the schema in which they are
> and for inherit tables give error as you suggested) because inherit
> tables could be excluded via following syntax:
> CREATE PUBLICATION pub FOR
> TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent),
> TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child);
> But I feel this could even be done separately if there is a real need
> in the field for such cases. So, let's follow consistency and
> simplicity of behavior for both partition and inherited tables.
>

I had also considered having partitions and inherited tables behave
differently as they are logically distinct. But it may cause some
confusion in first version and thus as you suggested it will be better
to stick to consistent behaviour in version1 and then decide based on
user's feedback.

thanks
Shveta