Re: Support EXCEPT for TABLES IN SCHEMA publications

On Thu, Aug 20, 2026 at 5:05 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Thu, Aug 20, 2026 at 8:47 AM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> >
> > On Wed, Aug 19, 2026 at 8:17 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > > On Tue, 18 Aug 2026 at 13:34, Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> > > >
> > > > Hi Vignesh/Nisha,
> > > >
> > > > IMO, it feels like these patches are doing too much work trying to
> > > > protect the user from themselves, and it is even making some
> > > > combinations difficult to specify.
> > > >
> > > > Also, there is a lot of logic and many lines of code now just for
> > > > checking publication command "inconsistencies".
> > >
> > > I still feel we should throw the error at CREATE PUBLICATION itself.
> > > That would make the conflict clear to the user and allow them to
> > > modify the publication accordingly. Otherwise, in a conflicting case
> > > like the following, it may be unclear whether the table will actually
> > > be published:
> > > CREATE TABLE s1.parent (a int);
> > > CREATE TABLE s2.child (b int) INHERITS (s1.parent);
> > > CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent), s2;
> > >
> >
> > Yeah, but is that even a conflict? AFAICT, it is considered a
> > "conflict" only because you assume the user has made a mistake. OTOH,
> > if you trust the user really wanted a publication with "everything
> > from schemas s1 and s2 but excluding the parent/children tree", then
> > the command could be considered valid, right?
> >
> > > Here, it may not be obvious whether s2.child will be published because
> > > schema s2 is included, or whether it will be skipped because its
> > > parent s1.parent is specified in the EXCEPT clause.
> >
> > It is unambiguous by this simple rule:
> > "RULE 1: Fine-grained clauses take precedence (so anything the user
> > says about a TABLE takes precedence over whatever they say about a
> > SCHEMA)"
> > In your example, the exclusion of the TABLE `parent` and its children
> > takes precedence over the inclusion of all tables from SCHEMA s2.
> >
> > ~
> >
> > The patches have grown a lot with the recent conflict checking code:
> > v25-0001 -- 2001 lines
> > v26-0001 -- 2085 lines
> > v27-0001 -- 2514 lines
> > v28-0001 -- 2597 lines
> > v29-0001 -- 2541 lines
> >
> > Perhaps my proposed rule has unknown flaws in it, but I'd hoped to
> > explore how much simpler the patch implementation would be if we just
> > assumed users know what they are doing.
> >
>
> Hmm... One problem with my "fine-grained TABLE versus coarse-grained
> SCHEMA" rule is exposed in this trickier example:
> Ex1. ... FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent), s2;
>
> I'd earlier suggested the parent INHERITS should reach across into the
> schema s2 and exclude s2.child there too, because that looks like what
> the user was asking for.
>
> But for this:
> Ex2. ... FOR TABLE s1.parent, TABLES IN SCHEMA s2 EXCEPT (TABLE s2.child);
>
> Saying the INHERITS reaches into schema s2 is new behaviour, and it
> may be ambiguous
> - Is s2.child included because parent INHERITS reached into schema s2
> and included it?
> - Or is s2.child excluded because of the EXCEPT (TABLE s2.child)?
>

Yes, it is certainly an ambiguous scenario, and we cannot reliably
guess the user's intent. IMO, throwing an error in such cases is the
most appropriate approach. If we do that, we should apply the same
principle to the other cases as well, which would bring us back to the
approach implemented in v28 (optimized further in v29). What do you
say?

thanks
Shveta