Re: Support EXCEPT for TABLES IN SCHEMA publications

On Thu, Aug 20, 2026 at 7:04 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> 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?

Yes, my goal was to make things better, If, as it appears to, my
proposal just ends up trading one set of issues for a different set,
of issues then we can forget about it and stick with what we already
have with v28/29.

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