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

On Fri, Apr 24, 2026 at 11:29 AM shveta malik <shveta.malik@gmail.com> wrote:
>
> 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?
> >

If no schema is specified, tables are resolved against the immediate
schema. In this case, s2.t1 and s2.t2 will be used in the EXCEPT list,
even if they exist in both s1 and s2.

> > 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.
>

I kept this for user convenience to specify the EXCEPT list in one go.
But as you mentioned, supporting mixed style adds complexity, as
currently, it checks tables against all schemas in the publication
(not just those in the command).

In further testing, I also noticed below confusing behavior due to
allowed mixed style -

CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1;
ALTER PUBLICATION pub ADD TABLES IN SCHEMA s2 EXCEPT (TABLE s1.t1);
 -- here, s1.t1 is allowed in EXCEPT because s1 is already part of the
publication, even though it’s not in the current command.

Let me make the changes to restrict the mixed style. Will share the
updated patch soon.

--
Thanks,
Nisha