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

On Fri, Apr 24, 2026 at 10:16 AM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Thu, Apr 16, 2026 at 4:24 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
> >
> > On Wed, Apr 15, 2026 at 7:35 AM Peter Smith <smithpb2250@gmail.com> wrote:
> > >
> > > On Tue, Apr 14, 2026 at 7:37 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > > >
> > > > On Tue, Apr 14, 2026 at 2:05 PM shveta malik <shveta.malik@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.
> > > > > > Furthermore, what is the ambiguity referred to? An excluded table is
> > > > > > clearly associated with the preceding schema. Can't the code infer the
> > > > > > schema internally even when it is not provided by the user? Of course,
> > > > > > the user *can* specify a schema-qualified name if they want to, but I
> > > > > > didn't see why we are forcing that rule upon them.
> > > > >
> > > > > +1. I also feel specifying only the table name is clear enough. Or are
> > > > > we referring to implementation complexity here?
> > > > >
> > > >
> > > > I think it will add complexity. Consider an example:
> > > > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, s2, s3 EXCEPT (t1, t2);
> > > >
> > > > So, which schema's exclusion list will these tables should be
> > > > considered for? Say, if table with name t1 is present in all schemas
> > > > then shall we exclude from all schemas or just consider it excluded
> > > > from the first one (s1)?
> > > >
> > >
> > > The exact pattern is already in common usage for row-filters and
> > > column-lists, yet nobody is confused.
> > >
> > > -- all these tables have the same column names
> > > -- (analogous to multiple schemas having same table names)
> > > CREATE TABLE t1(c1 int, c2 int);
> > > CREATE TABLE t2(c1 int, c2 int);
> > > CREATE TABLE t3(c1 int, c2 int);
> > >
> > > -- all tables have a column c1
> > > -- but this c1 means t3.c1 because the column-list is only for the adjacent t3.
> > > CREATE PUBLICATION pub1 FOR TABLE t1, t2, t3 (c1);
> > >
> > > -- all tables have a column c2
> > > -- but this c2 means t3.c2 because the row-filter is only for the adjacent t3.
> > > CREATE PUBLICATION pub2 FOR TABLE t1, t2, t3 WHERE (c2 > 99);
> > >
> > > \dRp+
> > >                                                        Publication pub1
> > >   Owner   | All tables | All sequences | Inserts | Updates | Deletes |
> > > Truncates | Generated columns | Via root | Description
> > >
----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
> > >  postgres | f          | f             | t       | t       | t       |
> > > t         | none              | f        |
> > > Tables:
> > >     "public.t1"
> > >     "public.t2"
> > >     "public.t3" (c1)
> > >
> > >                                                        Publication pub2
> > >   Owner   | All tables | All sequences | Inserts | Updates | Deletes |
> > > Truncates | Generated columns | Via root | Description
> > >
----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
> > >  postgres | f          | f             | t       | t       | t       |
> > > t         | none              | f        |
> > > Tables:
> > >     "public.t1"
> > >     "public.t2"
> > >     "public.t3" WHERE (c2 > 99)
> > >
> >
> > My intention was to avoid potential ambiguity when tables are not
> > schema-qualified, as mentioned by Amit [1].
> >
> > That said, PostgreSQL generally does not enforce schema qualification
> > and relies on search_path [2][3] for object resolution. Users are
> > typically familiar with this behavior.
> >
> > Thanks for the example. I’ve updated the syntax to allow tables in the
> > EXCEPT list without schema qualification.
> > If a table is specified without a schema, it is resolved against the
> > immediately preceding schema clause.
> >
> > Few examples:
> > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, s2 EXCEPT (TABLE t1);
> > -- table t1 is resolved in schema s2
>
> Okay, looks good.
>
> > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1), s2
> > EXCEPT (TABLE t2);
> > -- if s1 does not contain t1, an error is raised: "s1.t1" does not exist
>
> Okay, looks good.
>
> >
> > CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, s2 EXCEPT (TABLE s1.t1, s2.t1);
> > -- it will create the publication with except list - (s1.t1, s2.t1)
>
> Okay, I thought we will not be supporting EXCEPT at the end and mixed
> schemas inside. What will happen if I give:
>
> CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, s2 EXCEPT (TABLE t1, t2);
>
> and t1, t2 are present in both the schemas?
>
> I feel just like we associate column lists and row filters directly
> with each table, rather than using a mixed style at the end; we should
> also allow EXCEPT to be specified alongside each schema. This would
> avoid added complexity and reduce potential confusion. Thoughts?

One correction: I meant, we should "only"  allow EXCEPT to be
specified alongside each schema.

thanks
Shveta