On Fri, Aug 21, 2026 at 11:00 PM Nisha Moond <[email protected]> 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.
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.
How this resolves the cases from your email:
a). Clean Cross-Schema Separation (Cases 1, 3, 5, 7)
----------------------------------
Partitions:
1. FOR TABLES IN SCHEMA s1 EXCEPT (s1.root), s2;
--s1.root excluded, everything under s2 even if it is parition of
s1.root is published
3. FOR TABLE s2.p2, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root);
--s1.root excluded, s2.p2 published
Inheritance:
5. FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent), s2;
--s1.parent excluded, everything under s2 even if it is child of
s1.parent is published.
7. FOR TABLE s2.child, TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent);
--s1.parent excluded, s2.child is published as it is explcilty mentioned.
b. Local Exceptions are Respected (Cases 6 & 8)
----------------------------------
6. FOR TABLE s1.parent, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child);
--s1.parent published with all its children present in s1,
s2.child is excluded.
8. FOR TABLES IN SCHEMA s1, TABLES IN SCHEMA s2 EXCEPT (TABLE
s2.child);
--s1.parent published with all its children present in s1,
s2.child is excluded.
c. Local Table Precedence within the Same Schema (Cases 2 & 4)
----------------------------------
2. FOR TABLE s1.p1, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root);
-- parition s1.p1 is published. s1.root and other paritions in the
same schema are excluded. The same rule applies to inherited tables
too.
4. FOR TABLE s1.root, FOR TABLES IN SCHEMA s1 EXCEPT (s1.root);
--This is clearly an ERROR scenario as user is giving exact same
inclusion and exclusion for the same schema.
NOTE:
The case where parition is included while ROOT is excluded and
publish_via_partition_root is TRUE (e.g., case a.1), we may not even
publish parition itself. Check this once please. But this case will
not be any different from existing case on HEAD where partition alone
is included in publication with publish_via_partition_root=true.
Example:
create publication pub1 for table s2.part with
(publish_via_partition_root = true);
~~
Benefits I see:
1) We avoid any other issues requiring DDL blocking. Let me know if
you see any.
b) We follow what the user explicitly asked to EXCLUDE without any
notice/warning to override their choice.
c) We can clearly document it: 'Inclusion/exclusion rules do not bleed
over to other schema.
Nisha, can you please review this in detail? I might have overlooked something.
thanks
Shveta
