Re: Support EXCEPT for TABLES IN SCHEMA publications - Mailing list pgsql-hackers

On Thu, Aug 20, 2026 at 8:47 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> On Wed, Aug 19, 2026 at 8:17 PM vignesh C <vignesh21@gmail.com> wrote:
> >
> > On Tue, 18 Aug 2026 at 13:34, Peter Smith <smithpb2250@gmail.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)?

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