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

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 SCHEMA
>
> The 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