Support EXCEPT for TABLES IN SCHEMA publications
Hi hackers,
Following earlier work to support EXCEPT for FOR ALL TABLES [1]/messages/by-id/CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com
publications, starting this thread to extend the same capability to
schema-level publications (TABLES IN SCHEMA).
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.
Rules and behavior:
----------------------------
1) TABLES IN SCHEMA can be combined with FOR TABLE, but EXCEPT applies
only to the schema clause and must appear immediately after it.
Supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), TABLE s2.t1;
CREATE PUBLICATION pub FOR TABLE s2.t1, TABLES IN SCHEMA s1 EXCEPT (s1.t1);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), s2
EXCEPT (s2.t1)
Not supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, TABLE s2.t1 EXCEPT (s1.t1);
-- This same rule applies to ALTER PUBLICATION ... ADD/SET.
2) Conflicting definitions
Specifying the same table both in the EXCEPT clause and explicitly in
the TABLE clause results in an error, as this creates a conflicting
definition for the publication.
3) "ALTER PUBLICATION ... DROP TABLES IN SCHEMA" does not support
EXCEPT clause. Whereas, dropping a schema also removes any associated
entries from the EXCEPT list of the publication.
-- To only remove/update except list entries, use SET instead.
4) Consistency with ALL TABLES EXCEPT rules:
4a) Excluding a partitioned root excludes all its partitions
4b) Individual partitions cannot be excluded directly; exclude the root table.
4c) Excluding an inheritance parent (without ONLY) also excludes its children.
The patches are divided into three parts to simplify review:
Patch-001: Basic framework to support EXCEPT in CREATE PUBLICATION
... TABLES IN SCHEMA
Patch-002: Extend support to ALTER PUBLICATION ... ADD TABLES IN SCHEMA
Patch-003: Extend support to ALTER PUBLICATION ... SET TABLES IN SCHEMA
The patches are attached, feedback and suggestions are welcome.
[1]: /messages/by-id/CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com
--
Thanks,
Nisha
Attachments:
v1-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patchapplication/octet-stream; name=v1-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patchDownload+527-33
v1-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patchapplication/octet-stream; name=v1-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patchDownload+239-5
v1-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patchapplication/octet-stream; name=v1-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patchDownload+267-4
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.
e.g.
-- Syntax can be *identical* to the "EXCEPT (TABLE ...)" clause already pushed.
-- Both of these below are equivalent.
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2, t3);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, TABLE
t2, TABLE t3);
-- Below is an example of multiple schemas and multiple except clauses:
-- publish all tables of schema s1 except s1.t1 and s1.t2
-- publish all tables of schema s2
-- publish all tables of schema s3 except table s3.t2 (how is this
ambiguous with the excluded s1.t2?)
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2),
s2, s3 EXCEPT (TABLE t2);
======
Kind Regards,
Peter Smith.
Fujitsu Australia.
On Tue, Apr 14, 2026 at 1:03 PM Peter Smith <smithpb2250@gmail.com> wrote:
-- Syntax can be *identical* to the "EXCEPT (TABLE ...)" clause already pushed.
-- Both of these below are equivalent.
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2, t3);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, TABLE
t2, TABLE t3);-- Below is an example of multiple schemas and multiple except clauses:
-- publish all tables of schema s1 except s1.t1 and s1.t2
-- publish all tables of schema s2
-- publish all tables of schema s3 except table s3.t2 (how is this
ambiguous with the excluded s1.t2?)
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2),
s2, s3 EXCEPT (TABLE t2);
We can go in the direction as proposed by you but my preference would
be to avoid using EXCEPT keyword multiple times.
--
With Regards,
Amit Kapila.
On Tue, Apr 14, 2026 at 5:52 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
On Tue, Apr 14, 2026 at 1:03 PM Peter Smith <smithpb2250@gmail.com> wrote:
-- Syntax can be *identical* to the "EXCEPT (TABLE ...)" clause already pushed.
-- Both of these below are equivalent.
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2, t3);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, TABLE
t2, TABLE t3);-- Below is an example of multiple schemas and multiple except clauses:
-- publish all tables of schema s1 except s1.t1 and s1.t2
-- publish all tables of schema s2
-- publish all tables of schema s3 except table s3.t2 (how is this
ambiguous with the excluded s1.t2?)
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2),
s2, s3 EXCEPT (TABLE t2);We can go in the direction as proposed by you but my preference would
be to avoid using EXCEPT keyword multiple times.--
Using the "EXCEPT keyword multiple times" wasn't anything different
proposed by me. That was already part of the original post.
e.g. in the 3rd example.
------
Supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), TABLE s2.t1;
CREATE PUBLICATION pub FOR TABLE s2.t1, TABLES IN SCHEMA s1 EXCEPT (s1.t1);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), s2
EXCEPT (s2.t1);
------
======
Kind Regards,
Peter Smith.
Fujitsu Australia
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?
Show quoted text
e.g.
-- Syntax can be *identical* to the "EXCEPT (TABLE ...)" clause already pushed.
-- Both of these below are equivalent.
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2, t3);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, TABLE
t2, TABLE t3);-- Below is an example of multiple schemas and multiple except clauses:
-- publish all tables of schema s1 except s1.t1 and s1.t2
-- publish all tables of schema s2
-- publish all tables of schema s3 except table s3.t2 (how is this
ambiguous with the excluded s1.t2?)
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (TABLE t1, t2),
s2, s3 EXCEPT (TABLE t2);======
Kind Regards,
Peter Smith.
Fujitsu Australia.
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)?
--
With Regards,
Amit Kapila.
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.
--
Thanks,
Nisha
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]/messages/by-id/CAJpy0uB=JxTYXOB7VmrhVLR+1PG0=TtHuGekaqibOPpo2UBLiQ@mail.gmail.com. If we plan to support that, then we
need TABLE keyword, otherwise we are fine.
[1]: /messages/by-id/CAJpy0uB=JxTYXOB7VmrhVLR+1PG0=TtHuGekaqibOPpo2UBLiQ@mail.gmail.com
thanks
Shveta
On Tue, 14 Apr 2026 at 12:00, 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).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.Rules and behavior:
----------------------------
1) TABLES IN SCHEMA can be combined with FOR TABLE, but EXCEPT applies
only to the schema clause and must appear immediately after it.Supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), TABLE s2.t1;
CREATE PUBLICATION pub FOR TABLE s2.t1, TABLES IN SCHEMA s1 EXCEPT (s1.t1);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), s2
EXCEPT (s2.t1)Not supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, TABLE s2.t1 EXCEPT (s1.t1);-- This same rule applies to ALTER PUBLICATION ... ADD/SET.
2) Conflicting definitions
Specifying the same table both in the EXCEPT clause and explicitly in
the TABLE clause results in an error, as this creates a conflicting
definition for the publication.3) "ALTER PUBLICATION ... DROP TABLES IN SCHEMA" does not support
EXCEPT clause. Whereas, dropping a schema also removes any associated
entries from the EXCEPT list of the publication.
-- To only remove/update except list entries, use SET instead.4) Consistency with ALL TABLES EXCEPT rules:
4a) Excluding a partitioned root excludes all its partitions
4b) Individual partitions cannot be excluded directly; exclude the root table.
4c) Excluding an inheritance parent (without ONLY) also excludes its children.The patches are divided into three parts to simplify review:
Patch-001: Basic framework to support EXCEPT in CREATE PUBLICATION
... TABLES IN SCHEMA
Patch-002: Extend support to ALTER PUBLICATION ... ADD TABLES IN SCHEMA
Patch-003: Extend support to ALTER PUBLICATION ... SET TABLES IN SCHEMAThe patches are attached, feedback and suggestions are welcome.
+1 for this.
Few comments for the first patch:
1) This should be collected only if except relation was specified, we
can skip it if it is not specified:
+ /*
+ * Collect explicit table OIDs now, before we
close the relation
+ * list, so that except-table validation below
can check for
+ * contradictions without relying on a catalog
scan that might not
+ * yet see the just-inserted rows.
+ */
+ foreach(lc, rels)
+ {
+ PublicationRelInfo *pri =
(PublicationRelInfo *) lfirst(lc);
+
+ explicitrelids = lappend_oid(explicitrelids,
+
RelationGetRelid(pri->relation));
+ }2) Tab completion for except table of that particular schema lists
other schema, but currently specifying other schema tables is not
allowed:
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR",
"TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "("))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);Can we list only the tables from the specified schema.
3) Can this check be done at parser itself, it can be done in
preprocess_pubobj_list parser function to detect the error early:
+ /*
+ * For TABLES IN SCHEMA publications,
require schema-qualified
+ * names to avoid ambiguity when
multiple schemas in the
+ * publication have identically-named tables.
+ */
+ foreach(lc, exceptrelations)
+ {
+ PublicationTable *t =
(PublicationTable *) lfirst(lc);
+
+ if (t->relation->schemaname == NULL)
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+
errmsg("table \"%s\" in EXCEPT clause must be schema-qualified",
+
t->relation->relname));
+ }4) This error message does not seems to be conveying the correct error message:
postgres=# create publication pub1 for tables in schema sch3 except (
sch3.t1 ) ;
CREATE PUBLICATION
postgres=# alter publication pub1 add table sch3.t1 ;
ERROR: relation "t1" is already member of publication "pub1"
How about an error message like:
ERROR: table "sch3.t1" cannot be added explicitly because it is listed
in the EXCEPT clause of schema "sch3" in publication "pub1"
5) This change is not related to this patch:
@@ -5279,7 +5315,7 @@ foreach my $run (sort keys %pgdump_runs)
#
# Either "all_runs" should be set or there should be a
"like" list,
# even if it is empty. (This makes the test more
self-documenting.)
- if (!defined($tests{$test}->{all_runs})
+ if ( !defined($tests{$test}->{all_runs})
&& !defined($tests{$test}->{like}))
{
die "missing \"like\" in test \"$test\"";6) There is an indentation issue here:
- pub_except_obj_list opt_pub_except_clause
+ pub_except_obj_list pub_schema_except_obj_list
+ opt_pub_except_clause opt_pub_schema_except_clauseRegards,
Vignesh
On Tue, 14 Apr 2026 at 12:00, 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).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.Rules and behavior:
----------------------------
1) TABLES IN SCHEMA can be combined with FOR TABLE, but EXCEPT applies
only to the schema clause and must appear immediately after it.Supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), TABLE s2.t1;
CREATE PUBLICATION pub FOR TABLE s2.t1, TABLES IN SCHEMA s1 EXCEPT (s1.t1);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), s2
EXCEPT (s2.t1)Not supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, TABLE s2.t1 EXCEPT (s1.t1);-- This same rule applies to ALTER PUBLICATION ... ADD/SET.
2) Conflicting definitions
Specifying the same table both in the EXCEPT clause and explicitly in
the TABLE clause results in an error, as this creates a conflicting
definition for the publication.3) "ALTER PUBLICATION ... DROP TABLES IN SCHEMA" does not support
EXCEPT clause. Whereas, dropping a schema also removes any associated
entries from the EXCEPT list of the publication.
-- To only remove/update except list entries, use SET instead.4) Consistency with ALL TABLES EXCEPT rules:
4a) Excluding a partitioned root excludes all its partitions
4b) Individual partitions cannot be excluded directly; exclude the root table.
4c) Excluding an inheritance parent (without ONLY) also excludes its children.The patches are divided into three parts to simplify review:
Patch-001: Basic framework to support EXCEPT in CREATE PUBLICATION
... TABLES IN SCHEMA
Patch-002: Extend support to ALTER PUBLICATION ... ADD TABLES IN SCHEMA
Patch-003: Extend support to ALTER PUBLICATION ... SET TABLES IN SCHEMAThe patches are attached, feedback and suggestions are welcome.
When an EXCEPT table is specified together with TABLES IN SCHEMA sch1,
the EXCEPT entry is correctly created:
postgres=# create publication pub1 for tables in schema sch1 except (sch1.t1);
CREATE PUBLICATION
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Tables from schemas:
"sch1"
Except tables:
"sch1.t1"
However, after dropping the schema from the publication, the
previously recorded EXCEPT table entry is still retained:
postgres=# alter publication pub1 drop TABLES IN SCHEMA sch1 ;
ALTER PUBLICATION
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Except tables:
"sch1.t1"
This seems incorrect, because once sch1 is no longer part of the
publication, retaining "sch1.t1" as an EXCEPT entry no longer has any
semantic meaning and leaves behind stale catalog state.
Regards,
Vignesh
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)
======
Kind Regards,
Peter Smith.
Fujitsu Australia
On Tue, 14 Apr 2026 at 12:00, 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).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.Rules and behavior:
----------------------------
1) TABLES IN SCHEMA can be combined with FOR TABLE, but EXCEPT applies
only to the schema clause and must appear immediately after it.Supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), TABLE s2.t1;
CREATE PUBLICATION pub FOR TABLE s2.t1, TABLES IN SCHEMA s1 EXCEPT (s1.t1);
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1 EXCEPT (s1.t1), s2
EXCEPT (s2.t1)Not supported:
CREATE PUBLICATION pub FOR TABLES IN SCHEMA s1, TABLE s2.t1 EXCEPT (s1.t1);-- This same rule applies to ALTER PUBLICATION ... ADD/SET.
2) Conflicting definitions
Specifying the same table both in the EXCEPT clause and explicitly in
the TABLE clause results in an error, as this creates a conflicting
definition for the publication.3) "ALTER PUBLICATION ... DROP TABLES IN SCHEMA" does not support
EXCEPT clause. Whereas, dropping a schema also removes any associated
entries from the EXCEPT list of the publication.
-- To only remove/update except list entries, use SET instead.4) Consistency with ALL TABLES EXCEPT rules:
4a) Excluding a partitioned root excludes all its partitions
4b) Individual partitions cannot be excluded directly; exclude the root table.
4c) Excluding an inheritance parent (without ONLY) also excludes its children.The patches are divided into three parts to simplify review:
Patch-001: Basic framework to support EXCEPT in CREATE PUBLICATION
... TABLES IN SCHEMA
Patch-002: Extend support to ALTER PUBLICATION ... ADD TABLES IN SCHEMA
Patch-003: Extend support to ALTER PUBLICATION ... SET TABLES IN SCHEMAThe patches are attached, feedback and suggestions are welcome.
Few comments for the second patch:
1) This patch adds support only for:
ALTER PUBLICATION pub ADD TABLES IN SCHEMA s EXCEPT (s.t1, s.t2);
But documentation mentions for both add and set:
@@ -31,7 +31,7 @@ ALTER PUBLICATION <replaceable
class="parameter">name</replaceable> RENAME TO <r
<phrase>where <replaceable
class="parameter">publication_object</replaceable> is one of:</phrase>
TABLE <replaceable
class="parameter">table_and_columns</replaceable> [, ... ]
- TABLES IN SCHEMA { <replaceable
class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...
]
+ TABLES IN SCHEMA { <replaceable
class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [
EXCEPT ( <replaceable
class="parameter">except_table_object</replaceable> [, ... ] ) ] [,
... ]
2) Tab completion missing for:
alter publication pub1 add TABLES IN SCHEMA sch1
3) Currently Set tables in schema sch1 also works partially with the
second patch without adding the except tables:
postgres=# alter publication pub1 set tables in schema sch1 except (sch1.t1);
ALTER PUBLICATION
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Tables from schemas:
"sch1"
Should there be a check here to throw an error:
+static void
+AlterPublicationExceptTables(AlterPublicationStmt *stmt,
+ HeapTuple
tup, List *exceptrelations,
+ List *schemaidlist)
+{
+ Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+ Oid pubid = pubform->oid;
+
+ /*
+ * Nothing to do if no EXCEPT entries.
+ */
+ if (!exceptrelations)
+ return;
+4) Can this check be done at parser itself, it can be done in
preprocess_pubobj_list parser function to detect the error early:
+ foreach(lc, exceptrelations)
+ {
+ PublicationTable *t = (PublicationTable *) lfirst(lc);
+
+ if (t->relation->schemaname == NULL)
+ ereport(ERROR,
+
errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("table \"%s\"
in EXCEPT clause must be schema-qualified",
+
t->relation->relname));
+ }Regards,
Vignesh
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]: /messages/by-id/CAJpy0uB=JxTYXOB7VmrhVLR+1PG0=TtHuGekaqibOPpo2UBLiQ@mail.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.
--
Thanks,
Nisha
On Tue, Apr 14, 2026 at 8:46 PM vignesh C <vignesh21@gmail.com> wrote:
When an EXCEPT table is specified together with TABLES IN SCHEMA sch1,
the EXCEPT entry is correctly created:
postgres=# create publication pub1 for tables in schema sch1 except (sch1.t1);
CREATE PUBLICATIONpostgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Tables from schemas:
"sch1"
Except tables:
"sch1.t1"
However, after dropping the schema from the publication, the
previously recorded EXCEPT table entry is still retained:
postgres=# alter publication pub1 drop TABLES IN SCHEMA sch1 ;
ALTER PUBLICATION
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Except tables:
"sch1.t1"This seems incorrect, because once sch1 is no longer part of the
publication, retaining "sch1.t1" as an EXCEPT entry no longer has any
semantic meaning and leaves behind stale catalog state.
This is handled in v1-003 (last) patch along with other ALTER
PUBLICATION related modifications.
--
Thanks,
Nisha
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]: /messages/by-id/CAJpy0uB=JxTYXOB7VmrhVLR+1PG0=TtHuGekaqibOPpo2UBLiQ@mail.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.
thanks
Shveta
Import Notes
Reply to msg id not found: CABdArM43x8dDHXHKJ2T9WA6HywGkWY47_-MqXZ3Y1j0C7Zi01A@mail.gmail.com
On Wed, 15 Apr 2026 at 14:22, Nisha Moond <nisha.moond412@gmail.com> wrote:
On Tue, Apr 14, 2026 at 8:46 PM vignesh C <vignesh21@gmail.com> wrote:
When an EXCEPT table is specified together with TABLES IN SCHEMA sch1,
the EXCEPT entry is correctly created:
postgres=# create publication pub1 for tables in schema sch1 except (sch1.t1);
CREATE PUBLICATIONpostgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Tables from schemas:
"sch1"
Except tables:
"sch1.t1"
However, after dropping the schema from the publication, the
previously recorded EXCEPT table entry is still retained:
postgres=# alter publication pub1 drop TABLES IN SCHEMA sch1 ;
ALTER PUBLICATION
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Except tables:
"sch1.t1"This seems incorrect, because once sch1 is no longer part of the
publication, retaining "sch1.t1" as an EXCEPT entry no longer has any
semantic meaning and leaves behind stale catalog state.This is handled in v1-003 (last) patch along with other ALTER
PUBLICATION related modifications.
I was reviewing the patches individually and noticed this. If it is
handled in the 003 ignore that comment.
Regards,
Vignesh
Import Notes
Reply to msg id not found: CABdArM6P9g8qpA9tye3CkzrwXdJeMjHz_VJjpTf+w8_v8k+_WQ@mail.gmail.com
On Tue, Apr 14, 2026 at 5:01 PM vignesh C <vignesh21@gmail.com> wrote:
+1 for this.
Few comments for the first patch:
Thank you Vignesh for the review.
1) This should be collected only if except relation was specified, we can skip it if it is not specified: + /* + * Collect explicit table OIDs now, before we close the relation + * list, so that except-table validation below can check for + * contradictions without relying on a catalog scan that might not + * yet see the just-inserted rows. + */ + foreach(lc, rels) + { + PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc); + + explicitrelids = lappend_oid(explicitrelids, + RelationGetRelid(pri->relation)); + }
Fixed
2) Tab completion for except table of that particular schema lists other schema, but currently specifying other schema tables is not allowed: + else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);Can we list only the tables from the specified schema.
Fixed. Required a new query to fetch schema tables.
3) Can this check be done at parser itself, it can be done in preprocess_pubobj_list parser function to detect the error early: + /* + * For TABLES IN SCHEMA publications, require schema-qualified + * names to avoid ambiguity when multiple schemas in the + * publication have identically-named tables. + */ + foreach(lc, exceptrelations) + { + PublicationTable *t = (PublicationTable *) lfirst(lc); + + if (t->relation->schemaname == NULL) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("table \"%s\" in EXCEPT clause must be schema-qualified", + t->relation->relname)); + }
Fixed
4) This error message does not seems to be conveying the correct error message:
postgres=# create publication pub1 for tables in schema sch3 except (
sch3.t1 ) ;
CREATE PUBLICATION
postgres=# alter publication pub1 add table sch3.t1 ;
ERROR: relation "t1" is already member of publication "pub1"How about an error message like:
ERROR: table "sch3.t1" cannot be added explicitly because it is listed
in the EXCEPT clause of schema "sch3" in publication "pub1"
Fixed with a bit short error message -
postgres=# alter publication pub8 add table s1.t1;
ERROR: table "s1.t1" cannot be added because it is listed in EXCEPT
clause of publication "pub8"
5) This change is not related to this patch: @@ -5279,7 +5315,7 @@ foreach my $run (sort keys %pgdump_runs) # # Either "all_runs" should be set or there should be a "like" list, # even if it is empty. (This makes the test more self-documenting.) - if (!defined($tests{$test}->{all_runs}) + if ( !defined($tests{$test}->{all_runs}) && !defined($tests{$test}->{like})) { die "missing \"like\" in test \"$test\"";
Fixed
6) There is an indentation issue here: - pub_except_obj_list opt_pub_except_clause + pub_except_obj_list pub_schema_except_obj_list + opt_pub_except_clause opt_pub_schema_except_clause
Fixed
Please find the updated patches (v2) attached.
Patch-001: addressed above comments
Patch-002: addressed Vignesh's comments from [1]/messages/by-id/CALDaNm3pBWnJJ9ynVj3KeZ+NQQXboU-goObvF9fZ0GYzyuJFhQ@mail.gmail.com
Patch-003: updated the documentation and also added tab-completion for
the SET TABLES IN SCHEMA EXCEPT
[1]: /messages/by-id/CALDaNm3pBWnJJ9ynVj3KeZ+NQQXboU-goObvF9fZ0GYzyuJFhQ@mail.gmail.com
--
Thanks,
Nisha
Attachments:
v2-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patchapplication/octet-stream; name=v2-0001-Support-EXCEPT-clause-for-schema-level-publicatio.patchDownload+573-38
v2-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patchapplication/octet-stream; name=v2-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patchDownload+245-5
v2-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patchapplication/octet-stream; name=v2-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patchDownload+328-22
On Wed, Apr 15, 2026 at 11:18 AM vignesh C <vignesh21@gmail.com> wrote:
On Tue, 14 Apr 2026 at 12:00, Nisha Moond <nisha.moond412@gmail.com> wrote:
Few comments for the second patch:
Thanks for the review.
1) This patch adds support only for:
ALTER PUBLICATION pub ADD TABLES IN SCHEMA s EXCEPT (s.t1, s.t2);But documentation mentions for both add and set:
@@ -31,7 +31,7 @@ ALTER PUBLICATION <replaceable
class="parameter">name</replaceable> RENAME TO <r
<phrase>where <replaceable
class="parameter">publication_object</replaceable> is one of:</phrase>TABLE <replaceable
class="parameter">table_and_columns</replaceable> [, ... ]
- TABLES IN SCHEMA { <replaceable
class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...
]
+ TABLES IN SCHEMA { <replaceable
class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [
EXCEPT ( <replaceable
class="parameter">except_table_object</replaceable> [, ... ] ) ] [,
... ]
Fixed the doc descriptions where SET was mentioned in patch-002.
But for the synopsis, I feel it will be unnecessary to first separate
the ADD and SET publication objects like "publication_add_object" and
"publication_set_object" in patch-002 and then remove them in
patch-003. Please let me know if you think otherwise.
2) Tab completion missing for:
alter publication pub1 add TABLES IN SCHEMA sch1
Done.
3) Currently Set tables in schema sch1 also works partially with the
second patch without adding the except tables:
postgres=# alter publication pub1 set tables in schema sch1 except (sch1.t1);
ALTER PUBLICATION
postgres=# \dRp+ pub1
Publication pub1
Owner | All tables | All sequences | Inserts | Updates | Deletes |
Truncates | Generated columns | Via root | Description
---------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
vignesh | f | f | t | t | t |
t | none | f |
Tables from schemas:
"sch1"Should there be a check here to throw an error: +static void +AlterPublicationExceptTables(AlterPublicationStmt *stmt, + HeapTuple tup, List *exceptrelations, + List *schemaidlist) +{ + Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup); + Oid pubid = pubform->oid; + + /* + * Nothing to do if no EXCEPT entries. + */ + if (!exceptrelations) + return; +
Fixed
4) Can this check be done at parser itself, it can be done in preprocess_pubobj_list parser function to detect the error early: + foreach(lc, exceptrelations) + { + PublicationTable *t = (PublicationTable *) lfirst(lc); + + if (t->relation->schemaname == NULL) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("table \"%s\" in EXCEPT clause must be schema-qualified", + t->relation->relname)); + }
Fixed.
All above comments are addressed in v2 attached above.
[1]: -- Thanks, Nisha
--
Thanks,
Nisha
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]: /messages/by-id/CAJpy0uB=JxTYXOB7VmrhVLR+1PG0=TtHuGekaqibOPpo2UBLiQ@mail.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]https://www.postgresql.org/docs/devel/sql-createpublication.html
And another place is currently introducing FOR ALL SEQUENCES EXCEPT
(SEQUENCE ...) [2]/messages/by-id/CANhcyEVSXyQkvmrsOWPdQqnm2J3GMyQQrKhyCJiBQzqs6AvSow@mail.gmail.com
IMO, this schema thread should match [1]https://www.postgresql.org/docs/devel/sql-createpublication.html.
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.
======
[1]: https://www.postgresql.org/docs/devel/sql-createpublication.html
[2]: /messages/by-id/CANhcyEVSXyQkvmrsOWPdQqnm2J3GMyQQrKhyCJiBQzqs6AvSow@mail.gmail.com
Kind Regards,
Peter Smith.
Fujitsu Australia
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]: /messages/by-id/CAJpy0uB=JxTYXOB7VmrhVLR+1PG0=TtHuGekaqibOPpo2UBLiQ@mail.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