On Mon, Apr 27, 2026 at 9:02 AM Nisha Moond <[email protected]> wrote: > > On Fri, Apr 24, 2026 at 11:29 AM shveta malik <[email protected]> wrote: > > > > On Fri, Apr 24, 2026 at 10:16 AM shveta malik <[email protected]> > > wrote: > > > > > > On Thu, Apr 16, 2026 at 4:24 PM Nisha Moond <[email protected]> > > > wrote: > > > > > > > > On Wed, Apr 15, 2026 at 7:35 AM Peter Smith <[email protected]> > > > > wrote: > > > > > > > > > > On Tue, Apr 14, 2026 at 7:37 PM Amit Kapila <[email protected]> > > > > > wrote: > > > > > > > > > > > > On Tue, Apr 14, 2026 at 2:05 PM shveta malik > > > > > > <[email protected]> wrote: > > > > > > > > > > > > > > On Tue, Apr 14, 2026 at 1:03 PM Peter Smith > > > > > > > <[email protected]> wrote: > > > > > > > > > > > > > > > > On Tue, Apr 14, 2026 at 4:30 PM Nisha Moond > > > > > > > > <[email protected]> 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.
This case seems further confusing. > > Let me make the changes to restrict the mixed style. Will share the > updated patch soon. > Yes, that will be better approach. thanks Shveta
