v2-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABLE.patch

From 3b92c58c977a7f449373f5f2c2e8d95e712af2ae Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Wed, 15 Apr 2026 18:27:36 +0530
Subject: [PATCH v2 2/3] Add EXCEPT support to ALTER PUBLICATION ADD TABLES IN
 SCHEMA

Extend the EXCEPT clause support to allow tables to be excluded when
adding a schema to a publication via ALTER PUBLICATION ... ADD:

Syntax:
  ALTER PUBLICATION pub ADD TABLES IN SCHEMA s EXCEPT (s.t1, s.t2);
---
 doc/src/sgml/ref/alter_publication.sgml   |  41 ++++++-
 src/backend/commands/publicationcmds.c    | 125 +++++++++++++++++++++-
 src/bin/psql/tab-complete.in.c            |  26 +++++
 src/test/regress/expected/publication.out |  18 +++-
 src/test/regress/sql/publication.sql      |  11 +-
 src/test/subscription/t/037_except.pl     |  28 +++++
 6 files changed, 245 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index aa32bb169e9..e901163a89b 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -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> [, ... ] ) ] [, ... ]
 
 <phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
 
@@ -54,6 +54,10 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
 <phrase>and <replaceable class="parameter">table_object</replaceable> is:</phrase>
 
    [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
+
+<phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase>
+
+   [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -110,6 +114,17 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
    <literal>ADD TABLE</literal>.
   </para>
 
+  <para>
+   The <literal>EXCEPT</literal> clause can be used with
+   <literal>ADD TABLES IN SCHEMA</literal> to exclude specific tables from a
+   schema-level publication.  Each excluded table must belong to the schema
+   named in the same <literal>TABLES IN SCHEMA</literal> clause and must be
+   schema-qualified.  <literal>EXCEPT</literal> is not supported with
+   <literal>DROP TABLES IN SCHEMA</literal>; instead, dropping a schema from
+   the publication automatically removes all of its associated
+   <literal>EXCEPT</literal> entries.
+  </para>
+
   <para>
    The fourth variant of this command listed in the synopsis can change
    all of the publication properties specified in
@@ -198,6 +213,20 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>EXCEPT ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] )</literal></term>
+    <listitem>
+     <para>
+      Specifies tables to be excluded from a schema-level publication entry.
+      This clause may be used with <literal>ADD TABLES IN SCHEMA</literal>
+      and not with <literal>DROP TABLES IN SCHEMA</literal>.  Each named
+      table must belong to the schema specified in the same <literal>TABLES IN SCHEMA</literal>
+      clause and must be schema-qualified.  See <xref linkend="sql-createpublication"/>
+      for further details on the semantics of <literal>EXCEPT</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
     <listitem>
@@ -288,6 +317,16 @@ ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
 </programlisting>
   </para>
 
+  <para>
+   Add schema <structname>sales</structname> to the publication
+   <structname>sales_publication</structname>, excluding the
+   <structname>sales.internal</structname> and
+   <structname>sales.drafts</structname> tables:
+<programlisting>
+ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA sales EXCEPT (sales.internal, sales.drafts);
+</programlisting>
+  </para>
+
   <para>
    Add tables <structname>users</structname>,
    <structname>departments</structname> and schema
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 064e35accbb..b26706b5d1d 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -70,6 +70,9 @@ static void PublicationDropTables(Oid pubid, List *rels, bool missing_ok);
 static void PublicationAddSchemas(Oid pubid, List *schemas, bool if_not_exists,
 								  AlterPublicationStmt *stmt);
 static void PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok);
+static void AlterPublicationExceptTables(AlterPublicationStmt *stmt,
+										 HeapTuple tup, List *exceptrelations,
+										 List *schemaidlist);
 static char defGetGeneratedColsOption(DefElem *def);
 
 
@@ -1536,6 +1539,13 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
 		}
 
 		PublicationAddSchemas(pubform->oid, schemaidlist, false, stmt);
+
+		/*
+		 * Increment the command counter so that is_schema_publication() in
+		 * GetExcludedPublicationTables() can see the just-inserted schema
+		 * rows when AlterPublicationExceptTables runs next.
+		 */
+		CommandCounterIncrement();
 	}
 	else if (stmt->action == AP_DropObjects)
 		PublicationDropSchemas(pubform->oid, schemaidlist, false);
@@ -1561,6 +1571,112 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
 		 * skip existing ones when doing catalog update.
 		 */
 		PublicationAddSchemas(pubform->oid, schemaidlist, true, stmt);
+
+		/*
+		 * Increment the command counter so that is_schema_publication() in
+		 * GetExcludedPublicationTables() can see the just-inserted schema
+		 * rows when AlterPublicationExceptTables runs next.
+		 */
+		CommandCounterIncrement();
+	}
+}
+
+/*
+ * Alter the EXCEPT list of a publication.
+ *
+ * Adds, removes, or replaces except-table entries in pg_publication_rel
+ * (rows with prexcept = true).  These entries suppress publication of the
+ * named tables that would otherwise be covered by a FOR TABLES IN SCHEMA
+ * or FOR ALL TABLES clause.
+ */
+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;
+
+	/*
+	 * This function handles EXCEPT entries for schema-level publications
+	 * only.  For FOR ALL TABLES publications, EXCEPT entries are already
+	 * processed by AlterPublicationTables().
+	 */
+	if (schemaidlist == NIL && !is_schema_publication(pubid))
+		return;
+
+	/*
+	 * EXCEPT is not meaningful with DROP: dropping a schema from a
+	 * publication already removes all its except entries via cascade, and
+	 * there is no sensible interpretation of "drop only the except entry but
+	 * keep the schema".
+	 */
+	if (stmt->action == AP_DropObjects)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("EXCEPT clause is not supported with DROP in ALTER PUBLICATION")));
+
+	/*
+	 * EXCEPT with SET is not supported: SET replaces the schema list but does
+	 * not have a well-defined semantics for merging or replacing existing
+	 * except entries.  Users should DROP and re-ADD the schema with the
+	 * desired EXCEPT list instead.
+	 */
+	if (stmt->action == AP_SetObjects)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("EXCEPT clause is not supported with SET in ALTER PUBLICATION")));
+
+	if (stmt->action == AP_AddObjects)
+	{
+		List	   *rels;
+		List	   *explicitrelids;
+		List	   *pubschemaidlist;
+		ListCell   *lc;
+
+		rels = OpenTableList(exceptrelations);
+
+		/*
+		 * Build the effective schema list: schemas already in the publication
+		 * plus any being added in this same command.  The latter may not yet
+		 * be visible via a catalog scan if CommandCounterIncrement has not
+		 * been issued, so we include them explicitly.
+		 */
+		pubschemaidlist = GetPublicationSchemas(pubid);
+		pubschemaidlist = list_concat_unique_oid(pubschemaidlist, schemaidlist);
+		explicitrelids = GetIncludedPublicationRelations(pubid,
+														 PUBLICATION_PART_ROOT);
+
+		foreach(lc, rels)
+		{
+			PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
+			Oid			relid = RelationGetRelid(pri->relation);
+			Oid			relns = RelationGetNamespace(pri->relation);
+
+			if (!list_member_oid(pubschemaidlist, relns))
+				ereport(ERROR,
+						errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						errmsg("table \"%s.%s\" in EXCEPT clause does not belong to any schema listed in the publication",
+							   get_namespace_name(relns),
+							   RelationGetRelationName(pri->relation)));
+
+			if (list_member_oid(explicitrelids, relid))
+				ereport(ERROR,
+						errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+						errmsg("table \"%s.%s\" cannot appear in both the table list and the EXCEPT clause",
+							   get_namespace_name(relns),
+							   RelationGetRelationName(pri->relation)));
+		}
+
+		PublicationAddTables(pubid, rels, false, stmt);
+
+		CloseTableList(rels);
 	}
 }
 
@@ -1771,10 +1887,17 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
 					errmsg("publication \"%s\" does not exist",
 						   stmt->pubname));
 
-		relations = list_concat(relations, exceptrelations);
+		/*
+		 * For FOR ALL TABLES, EXCEPT entries are processed by
+		 * AlterPublicationTables(), so merge them in.  For TABLES IN SCHEMA,
+		 * they are handled separately by AlterPublicationExceptTables().
+		 */
+		if (stmt->for_all_tables)
+			relations = list_concat(relations, exceptrelations);
 		AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
 							   schemaidlist != NIL);
 		AlterPublicationSchemas(stmt, tup, schemaidlist);
+		AlterPublicationExceptTables(stmt, tup, exceptrelations, schemaidlist);
 		AlterPublicationAllFlags(stmt, rel, tup);
 	}
 
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 12271f7e2b1..15b7551dcdc 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2363,6 +2363,32 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_schemas
 								 " AND nspname NOT LIKE E'pg\\\\_%%'",
 								 "CURRENT_SCHEMA");
+	/* After a single schema name in ADD context, offer EXCEPT ( */
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny) &&
+			 !ends_with(prev_wd, ','))
+		COMPLETE_WITH("EXCEPT (");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT"))
+		COMPLETE_WITH("(");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "("))
+	{
+		if (strchr(prev3_wd, ',') == NULL)
+		{
+			set_completion_reference(prev3_wd);
+			COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_schema);
+		}
+	}
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", MatchAnyN) && ends_with(prev_wd, ','))
+	{
+		char	   *schema_word = previous_words[previous_words_count - 8];
+
+		if (strchr(schema_word, ',') == NULL)
+		{
+			set_completion_reference(schema_word);
+			COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_schema);
+		}
+	}
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", MatchAnyN) && !ends_with(prev_wd, ','))
+		COMPLETE_WITH(")");
 	/* ALTER PUBLICATION <name> SET ( */
 	else if (Matches("ALTER", "PUBLICATION", MatchAny, MatchAnyN, "SET", "("))
 		COMPLETE_WITH("publish", "publish_generated_columns", "publish_via_partition_root");
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 50b3a819d14..e563553e8eb 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -501,9 +501,25 @@ CREATE PUBLICATION testpub_except_err
 ERROR:  EXCEPT is not allowed for TABLE publication objects
 LINE 2:     FOR TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2 EXCEPT...
                                                ^
+---------------------------------------------
+-- EXCEPT tests for ALTER PUBLICATION
+---------------------------------------------
+CREATE PUBLICATION testpub_alter_except;
+-- ADD: add a schema with an excepted table
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_tbl_s1);
+\dRp+ testpub_alter_except
+                                                       Publication testpub_alter_except
+          Owner           | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description 
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | f          | f             | t       | t       | t       | t         | none              | f        | 
+Tables from schemas:
+    "pub_test"
+Except tables:
+    "pub_test.testpub_tbl_s1"
+
 RESET client_min_messages;
 DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
-DROP PUBLICATION testpub_schema_except1, testpub_schema_except2;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_alter_except;
 CREATE SEQUENCE regress_pub_seq0;
 CREATE SEQUENCE pub_test.regress_pub_seq1;
 -- FOR ALL SEQUENCES
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 3f46e8c5e03..3436b832eca 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -240,9 +240,18 @@ CREATE PUBLICATION testpub_schema_except2
 CREATE PUBLICATION testpub_except_err
     FOR TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2 EXCEPT (pub_test.testpub_nopk);
 
+---------------------------------------------
+-- EXCEPT tests for ALTER PUBLICATION
+---------------------------------------------
+CREATE PUBLICATION testpub_alter_except;
+
+-- ADD: add a schema with an excepted table
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_tbl_s1);
+\dRp+ testpub_alter_except
+
 RESET client_min_messages;
 DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
-DROP PUBLICATION testpub_schema_except1, testpub_schema_except2;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_alter_except;
 
 CREATE SEQUENCE regress_pub_seq0;
 CREATE SEQUENCE pub_test.regress_pub_seq1;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 0801d3a77c7..9675db18bef 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -353,6 +353,34 @@ $node_publisher->safe_psql('postgres',
 $node_subscriber->safe_psql('postgres',
 	'TRUNCATE sch1.par, sch1.chi, sch1.tab_pub, sch1.tab_exc');
 
+# ============================================
+# ALTER PUBLICATION EXCEPT for TABLES IN SCHEMA
+# ============================================
+
+# ADD: add a schema with an excepted table; verify the except entry takes effect.
+$node_publisher->safe_psql('postgres', "CREATE PUBLICATION sch_pub");
+$node_publisher->safe_psql('postgres',
+	"ALTER PUBLICATION sch_pub ADD TABLES IN SCHEMA sch1 EXCEPT (sch1.tab_exc)"
+);
+$node_subscriber->safe_psql('postgres',
+	"CREATE SUBSCRIPTION sch_sub CONNECTION '$publisher_connstr' PUBLICATION sch_pub"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_pub");
+is($result, qq(6),
+	'ALTER ... ADD TABLES IN SCHEMA EXCEPT: included table synced');
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_exc");
+is($result, qq(0),
+	'ALTER ... ADD TABLES IN SCHEMA EXCEPT: excluded table not synced');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
+
 # Cleanup schema tables before the multi-publication section.
 $node_publisher->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
 $node_subscriber->safe_psql('postgres', 'DROP SCHEMA sch1 CASCADE');
-- 
2.50.1 (Apple Git-155)