Re: Support EXCEPT for TABLES IN SCHEMA publications

On Mon, Aug 24, 2026 at 3:40 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Fri, Aug 21, 2026 at 11:00 PM 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.
>
> I agree with the analysis.
>
> >
> > 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.
>
> Okay. I see these cases but I don't think we can comapre these cases
> with single publication case.
>
> > 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?
> >
>
> While the proposal to let inclusion win over conflicting rules avoids
> adding complex alter-table restrictions, I think it has a significant
> drawback: relying on a NOTICE/warning to override an explicit EXCEPT
> clause feels risky.
>
> An EXCEPT clause is an explicit data-filtering boundary. If a DBA/user
> explicitly excludes a table or its child, for example, because it
> contains sensitive data, I don't think we should silently override
> that exclusion just because another inclusion rule happens to cover
> the same relation indirectly. A NOTICE or warning could easily be
> missed, and we could end up publishing data that the user explicitly
> intended to exclude. So, to me, the concern is not just that the
> behavior could be surprising; we would actually be publishing
> something that the user explicitly asked us not to publish.

+1 If the user says EXCEPT then the only safe thing to do is what the
user asked for. To do otherwise is effectively saying: “We see you
wanted to exclude this table but we are going to publish it anyway
because we figure you just made a mistake”.

>
> An Alternative Solution could be 'Strict Schema-Bound Isolation'
> (Peter also suggested something similar earlier if I am not wrong,
> which did not reach to conclusion). To avoid both DDL-blocking and
> unsafe Exclusion overrides, we could adopt a Strict Schema-Bound
> Isolation model.
>
> Core rule: Schema boundaries act as hard firewalls. An EXCEPT clause
> defined for a specific schema applies only to objects physically
> residing in that schema. Inclusion or exclusion through one schema
> does not recursively "bleed over" into another schema or affect
> partitions or inherited children that reside there.

Something seems off here. The inclusion rules are already
well-established. Partitioning really *does* bleed already into other
unpublished schemas currently on HEAD.

e.g.
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1;

Inheritance:
s1.parent INCLUDED
s1.child INCLUDED
s2.child NOT INCLUDED

Partitioning:
s1.root INCLUDED
s1.part INCLUDED
s2.part INCLUDED (it cascades to other schemas)

~~~

So you can't make this a "Core Rule" without changing the existing
HEAD behaviour for partition inclusion. I doubt that will be
acceptable.Perhaps you meant the "Strict Schema-Bound Isolation" rule
is only for exclusions. Maybe that is ok. OTOH making the
inclusion/exclusion cross-schema rules different might not be good
either.

======
Kind Regards,
Peter Smith.
Fujitsu Australia