Re: Support EXCEPT for TABLES IN SCHEMA publications

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.

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.

--
With Regards,
Amit Kapila.