Re: Support EXCEPT for TABLES IN SCHEMA publications

On Sat, Aug 22, 2026 at 3:30 AM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
>
> 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.

Any combination of clauses used to describe behaviour also needs to
show what happens for table inheritance using the "ONLY" keyword. I
think you've omitted that combination.

>
> 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.

No. You wrote “publish the full s1.parent tree, since s1 is included,
so s2.child is published “, but that is incorrect AFAIK.

Cross-schema inclusion behaves differently for inheritance and for
partitioning. For “TABLES IN SCHEMA s1” the s1.parent will *not* reach
out to include descendants from other schemas. (I demonstrated this
already with my SQL CASE 2 example [1])

======
[1] https://www.postgresql.org/message-id/CAHut%2BPvpFR0%3D-AT_0QfjtWFov5ZT4yrfJgbAd-N7w%3DCzSm708g%40mail.gmail.com

Kind Regards,
Peter Smith.
Fujitsu Australia