Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, 14 Apr 2026 at 12:00, Nisha Moond <nisha(dot)moond412(at)gmail(dot)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 SCHEMA
>
> The 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_clause

Regards,
Vignesh