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

On Thu, Apr 16, 2026 at 5:44 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> On Wed, Apr 15, 2026 at 8:15 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Wed, Apr 15, 2026 at 2:22 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
> > >
> > > On Tue, Apr 14, 2026 at 3:22 PM shveta malik <shveta.malik@gmail.com> wrote:
> > > >
> > > > On Tue, Apr 14, 2026 at 3:15 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
> > > > >
> > > > > On Tue, Apr 14, 2026 at 1:03 PM Peter Smith <smithpb2250@gmail.com> wrote:
> > > > > >
> > > > > > On Tue, Apr 14, 2026 at 4:30 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
> > > > > > >
> > > > > > > Hi hackers,
> > > > > > >
> > > > > > > Following earlier work to support EXCEPT for FOR ALL TABLES [1]
> > > > > > > publications, starting this thread to extend the same capability to
> > > > > > > schema-level publications (TABLES IN SCHEMA).
> > > > > >
> > > > > > Hi Nisha.
> > > > > >
> > > > > > +1 for adding this new kind of exclusion clause to CREATE PUBLICATION command.
> > > > > >
> > > > > > >
> > > > > > > Currently, TABLES IN SCHEMA publishes all tables in a schema with no
> > > > > > > way to exclude a subset. Users who want to skip a few tables must
> > > > > > > switch to an explicit FOR TABLE list, which loses the convenience of
> > > > > > > schema-level publishing and requires ongoing maintenance as tables are
> > > > > > > added.
> > > > > > >
> > > > > > > Proposed syntax:
> > > > > > > ------------------------
> > > > > > > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1, s1.t2);
> > > > > > > ALTER PUBLICATION pub ADD TABLES IN SCHEMA s1 EXCEPT (s1.t1);
> > > > > > > ALTER PUBLICATION pub SET TABLES IN SCHEMA s1 EXCEPT (s1.t1);
> > > > > > >
> > > > > > > Note: Tables in the EXCEPT clause must be schema-qualified to avoid
> > > > > > > ambiguity and must belong to the published schema; otherwise, an error
> > > > > > > is raised.
> > > > > > >
> > > > > >
> > > > > > The proposed syntax is almost, but not quite, what I was anticipating.
> > > > > > IMO the syntax shouldn't just be similar to the FOR ALL TABLES EXCEPT;
> > > > > > It can be *identical* to it. e.g., your examples are missing the
> > > > > > 'TABLE' keyword necessary to achieve the same command flexibility.
> > > > > >
> > > > >
> > > > > I intentionally didn’t use the TABLE keyword inside EXCEPT.
> > > > > IIUC, for FOR ALL TABLES EXCEPT, there may be a future need to
> > > > > distinguish object types (e.g., tables vs schemas), which is why
> > > > > specifying TABLE makes sense there.
> > > > >
> > > > > However, for TABLES IN SCHEMA, only tables can be specified, so
> > > > > omitting TABLE keeps the syntax simpler and more intuitive.
> > > > >
> > > > > Let's hear others’ opinions too on this and I'll adjust if there’s a
> > > > > preference for including it.
> > > > >
> > > >
> > > > Nisha, please see pt 1 in [1]. If we plan to support that, then we
> > > > need TABLE keyword, otherwise we are fine.
> > > >
> > > > [1]:
https://www.postgresql.org/message-id/CAJpy0uB%3DJxTYXOB7VmrhVLR%2B1PG0%3DTtHuGekaqibOPpo2UBLiQ%40mail.gmail.com
> > > >
> > >
> > > Thank you Shveta for pointing to the discussion.
> > > So, if we were to extend CREATE PUBLICATION to support something like:
> > >  "CREATE PUBLICATION pub FOR TABLES, SEQUENCES IN SCHEMA sch1 EXCEPT
> > > (TABLE t1,  SEQUENCE s1);"
> > > then, introducing TABLE inside EXCEPT could make sense, but at this
> > > stage we’re not sure how the syntax for supporting SEQUENCES IN SCHEMA
> > > will evolve. The overall grammar may require broader changes rather
> > > than a simple extension. So, we can always introduce keywords like
> > > TABLE or SEQUENCE later, if and when they are actually needed. Just my
> > > thoughts.
> > >
> >
> > It works for me Nisha. I also agree that the 'TABLE' keyword seems
> > redundant in the case you proposed:
> > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1, s1.t2);
> >
> > But I am just worried this might confuse users, as one command
> > requires specifying the TABLE with EXCEPT, while another requires
> > omitting it. But we can first focus on implementation and come back to
> > it later when more people join and share their thoughts.
> >
>
> I agree with Shveta that inconsistency will confuse.
>
> We already have FOR ALL TABLES EXCEPT (TABLE ...) [1]
> And another place is currently introducing FOR ALL SEQUENCES EXCEPT
> (SEQUENCE ...) [2]
> IMO, this schema thread should match [1].
>
> I think syntax *consistency* should take priority here. While some
> keywords may end up being unnecessary in certain cases, having
> different rules for each EXCEPT variation seems harder to maintain and
> reason about.
>
> I also agree with you that we are not sure how the command syntax
> might evolve in future. That is another reason why requiring these
> keywords (like 'TABLE' and 'SEQUENCE') is the best/safest first
> approach, IMO, because it will be backward-compatible. For example, if
> it turns out we can make (some of) these keywords optional in future,
> then user code will still be fine, but if they are absent now, then we
> cannot make them mandatory in future PostgreSQL versions without
> breaking user code.
>

Okay, to maintain consistency in the EXCEPT syntax across publication
commands and keeping future compatibility in mind, I’m fine updating
the syntax.
I’ll share an updated patch soon.

--
Thanks,
Nisha