Re: Support EXCEPT for TABLES IN SCHEMA publications

Hi,
After considering the discussion upthread, I think it is difficult to
handle all combinations with a simple rule without making the code
more complex. The main problematic cases seem to be when a
partition/inherited child is in a different schema from its root.

For example, s1.root has two partitions: s1.p1 and s2.p2.
With the current patch v28/v29, if we do not allow:
CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), s2; (ERROR)
then, we would also need to block several DDL operations to ensure the
publication can never reach this state later. Say if s2.p2 does not
exist yet and the creation of pub1 succeeds, then:

1. A partition of s1.root cannot later be created in another schema:
Block: CREATE TABLE s2.p2 PARTITION OF s1.root ...

2. A partition from a published schema cannot be attached to an excluded root:
Block: ALTER TABLE s1.root ATTACH PARTITION s2.p2 ...

3. A partition of the excluded root cannot be moved to another schema:
Block: ALTER TABLE s1.p1 SET SCHEMA s2;

4. Another schema s3 having s3.p3 (root's part) cannot later be added
to the publication:
Block: ALTER PUBLICATION pub1 ADD TABLES IN SCHEMA s3;

IMO, especially for cases 1 and 3, table DDL should not depend on
publication metadata. So I don't think adding these DDL restrictions
is a good approach.

Another option is to simply allow FOR TABLES IN SCHEMA s1 EXCEPT
(s1.root), s2; and exclude the full s1.root tree along with s2.p2. as
suggested at [1]
However, this conflicts with the other case where FOR TABLES IN SCHEMA
s1 EXCEPT (s1.root), TABLE s2.p2; is rejected, as discussed earlier.

So the question is whether we can simplify the rule further.
On HEAD, I tested combinations where multiple publications in the same
subscription have conflicting rules.
For example:
pub1: FOR ALL TABLES EXCEPT (s1.root);
pub2: FOR TABLE s1.root;

If the subscription includes both publications, s1.root is still
replicated through pub2.

Similarly:
pub1: FOR ALL TABLES EXCEPT (s1.root);
pub2: FOR TABLE s1.p1;

and

pub1: FOR ALL TABLES EXCEPT (s1.root);
pub2: FOR TABLE s2.p2;

In both cases, the subscriber receives s1.p1 / s2.p2 through pub2.

This is because pgoutput makes the publication decision independently
for each publication. So effectively, INCLUSION wins over EXCLUSION. I
think we could apply the same rule to the publication definition
itself.

For example: (Partitions case)

1. FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), s2;
-- Exclude s1.root and its children in s1, such as s1.p1.
-- Publish s2.p2 because s2 is explicitly included.

The limitation is that there is still no way to exclude the complete
s1.root tree when publishing both s1 and s2. This could potentially be
addressed later by allowing partition children in the EXCEPT clause.

2. FOR TABLE s1.p1, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root);
-- Allow s1.p1 to be published. The EXCEPT only excludes s1.root and
other children in the same schema.

3. FOR TABLE s2.p2, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root);
-- Same as case 1. Exclude s1.root and its children in s1, while
s2.p2 is published.

4. FOR TABLE s1.root, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root);
-- Allow s1.root to be published and ignore the EXCEPT entry, with a
notice/warning.

For partitions, we may need some changes in pgoutput and the relevant
ancestor lookup code to ensure inclusion always wins over exclusion.

I think the same rule can simplify inheritance trees as well:

5. FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent), s2;
-- Exclude s1.parent and its children in s1, but publish s2.child.

6. FOR TABLE s1.parent, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child);
-- Publish the full s1.parent tree. The explicit inclusion of
s1.parent tree overrides the exclusion of s2.child, with a
notice/warning.

7. FOR TABLE s2.child, TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent);
-- Same as case 5: publish s2.child, while excluding s1.parent and
its children in s1.

8. FOR TABLES IN SCHEMA s1, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child);
-- Same as case 6: publish the full s1.parent tree, since s1 is
included, so s2.child is published. The inclusion overrides the
EXCEPT, with a notice/warning.

With this approach, I don't think we need additional DDL restrictions.
New tables/partitions added under an explicitly included schema would
simply be included.

I tried to cover the conflicting combinations including the ones
discussed upthread. Please let me know if there are other cases that
would still be ambiguous with this rule.

Thoughts?

[1] https://www.postgresql.org/message-id/CAHut%2BPvTPcEFtOZ-bVi8Z%3DNLqti1%3DqyYrQDhu-5oDt6RJ4UBhA%40mail.gmail.com

--
Thanks,
Nisha