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
