Re: Support EXCEPT for TABLES IN SCHEMA publications

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?

[1]: https://www.postgresql.org/message-id/CABdArM4nVk-umQ_VXoGCrb7gQ_VcM5gHFcDOOa9EQbiUC-0EsA%40mail.gmail.com
[2]: https://www.postgresql.org/message-id/CAA4eK1%2BNmQRjSHPLr0X8YBuC6joivFqgsY3_qJ5-RnuuwNGkRQ%40mail.gmail.com

[3]: Case 3 tests:

CREATE SCHEMA s1;
CREATE SCHEMA s2;
--partition case
CREATE TABLE s1.parent (id int) PARTITION BY LIST (id);
CREATE TABLE s2.part PARTITION OF s1.parent FOR VALUES IN (1, 2, 3);

postgres=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT
(TABLE s1.parent), TABLE s2.part;
ERROR: partition "s2.part" cannot be both published and excluded
DETAIL: Partition root "s1.parent" is named in the publication's
EXCEPT clause for schema "s1".

--inheritance case
drop table s1.parent;
CREATE TABLE s1.parent (a int);
CREATE TABLE s2.child (b int) INHERITS (s1.parent);

postgres=# CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT
(TABLE s1.parent*), TABLE s2.child;
ERROR: table "s2.child" cannot be both published and excluded

~~

Are there any other similar cases that need discussion along with the above?

thanks
Shveta