v17-0002-Add-EXCEPT-support-to-ALTER-PUBLICATION-ADD-TABL.patch

From 582e33e1a4f545c69cb3603167e198cc47fa72ce Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Wed, 1 Jul 2026 14:06:28 +0530
Subject: [PATCH v17 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 (TABLE s.t1);

Since pg_dump uses ALTER PUBLICATION ... ADD, support for it is
included in this patch.
---
 src/backend/catalog/pg_publication.c      | 19 +++--
 src/backend/commands/publicationcmds.c    | 98 ++++++++++++++++++++++-
 src/bin/pg_dump/pg_dump.c                 | 30 ++++++-
 src/bin/pg_dump/t/002_pg_dump.pl          | 24 ++++++
 src/bin/psql/tab-complete.in.c            | 17 ++++
 src/test/regress/expected/publication.out | 32 +++++++-
 src/test/regress/sql/publication.sql      | 20 ++++-
 src/test/subscription/t/037_except.pl     | 32 ++++++++
 8 files changed, 258 insertions(+), 14 deletions(-)

diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index e7018ff565f..16a3b50711b 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -657,15 +657,18 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
 	 * here, as CreatePublication() function invalidates all relations as part
 	 * of defining a FOR ALL TABLES publication.
 	 *
-	 * For ALTER PUBLICATION, invalidation is needed only when adding an
-	 * EXCEPT table to a publication already marked as ALL TABLES. For
-	 * publications that were originally empty or defined as ALL SEQUENCES and
-	 * are being converted to ALL TABLES, invalidation is skipped here, as
-	 * AlterPublicationAllFlags() function invalidates all relations while
-	 * marking the publication as ALL TABLES publication.
+	 * For ALTER PUBLICATION, invalidation is needed when adding an EXCEPT
+	 * table to either a FOR ALL TABLES publication (pub->alltables is true)
+	 * or a FOR TABLES IN SCHEMA publication (is_schema_publication is true).
+	 * The exception: when a publication is being converted to FOR ALL TABLES
+	 * (pub->alltables is still false at this point),
+	 * AlterPublicationAllFlags() will perform a full invalidation, so we skip
+	 * it here.
 	 */
-	inval_except_table = (alter_stmt != NULL) && pub->alltables &&
-		(alter_stmt->for_all_tables && pri->except);
+	inval_except_table = (alter_stmt != NULL) && pri->except &&
+		(pub->alltables
+		 ? alter_stmt->for_all_tables
+		 : is_schema_publication(pubid));
 
 	if (!pri->except || inval_except_table)
 	{
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 45a8a5c3f71..542579dae86 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -70,6 +70,13 @@ 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 AlterPublicationSchemas(AlterPublicationStmt *stmt,
+									HeapTuple tup, List *schemaidlist,
+									List *except_pubtables);
+static void AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt,
+											   HeapTuple tup,
+											   List *except_pubtables,
+											   List *schemaidlist);
 static char defGetGeneratedColsOption(DefElem *def);
 static void CheckExceptNotInTableList(List *except_rels, List *explicitrelids);
 
@@ -1509,7 +1516,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
  */
 static void
 AlterPublicationSchemas(AlterPublicationStmt *stmt,
-						HeapTuple tup, List *schemaidlist)
+						HeapTuple tup, List *schemaidlist,
+						List *except_pubtables)
 {
 	Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
 
@@ -1586,6 +1594,88 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
 		 */
 		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 AlterPublicationSchemaExceptTables runs next.
+	 */
+	if (stmt->action == AP_AddObjects || stmt->action == AP_SetObjects)
+		CommandCounterIncrement();
+
+	AlterPublicationSchemaExceptTables(stmt, tup, except_pubtables, schemaidlist);
+}
+
+/*
+ * Alter the EXCEPT list of a schema-level 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
+ * clause.
+ */
+static void
+AlterPublicationSchemaExceptTables(AlterPublicationStmt *stmt,
+							 HeapTuple tup, List *except_pubtables,
+							 List *schemaidlist)
+{
+	Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
+	Oid			pubid = pubform->oid;
+
+	/*
+	 * Nothing to do if no EXCEPT entries.
+	 */
+	if (!except_pubtables)
+		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;
+
+	/*
+	 * Dropping a schema from a publication removes all its EXCEPT entries via
+	 * cascade. The concept of "drop all schema tables from the publication
+	 * EXCEPT these ones" is not supported.
+	 */
+	if (stmt->action == AP_DropObjects)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("EXCEPT clause is not supported with DROP in ALTER PUBLICATION")));
+
+	/*
+	 * XXX EXCEPT with SET is not currently implemented.  Workaround: DROP and
+	 * re-ADD the schema with the desired EXCEPT list.
+	 */
+	if (stmt->action == AP_SetObjects)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("EXCEPT clause is not supported with SET in ALTER PUBLICATION"),
+				 errhint("Drop and re-add the schema with the desired EXCEPT list.")));
+
+	if (stmt->action == AP_AddObjects)
+	{
+		List	   *rels;
+		List	   *explicitrelids;
+
+		rels = OpenTableList(except_pubtables);
+
+		explicitrelids = GetIncludedPublicationRelations(pubid,
+														 PUBLICATION_PART_ROOT);
+
+		/*
+		 * Validate that each excluded table is not also in the explicit table
+		 * list (which would be contradictory).
+		 */
+		CheckExceptNotInTableList(rels, explicitrelids);
+
+		PublicationAddTables(pubid, rels, false, stmt);
+
+		CloseTableList(rels);
+	}
 }
 
 /*
@@ -1795,10 +1885,12 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
 					errmsg("publication \"%s\" does not exist",
 						   stmt->pubname));
 
-		relations = list_concat(relations, except_pubtables);
+		if (stmt->for_all_tables)
+			relations = list_concat(relations, except_pubtables);
+
 		AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
 							   schemaidlist != NIL);
-		AlterPublicationSchemas(stmt, tup, schemaidlist);
+		AlterPublicationSchemas(stmt, tup, schemaidlist, except_pubtables);
 		AlterPublicationAllFlags(stmt, rel, tup);
 	}
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c56437d6057..c2b2b88bb6c 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5019,6 +5019,7 @@ dumpPublicationNamespace(Archive *fout, const PublicationSchemaInfo *pubsinfo)
 	PublicationInfo *pubinfo = pubsinfo->publication;
 	PQExpBuffer query;
 	char	   *tag;
+	bool		has_except = false;
 
 	/* Do nothing if not dumping schema */
 	if (!dopt->dumpSchema)
@@ -5029,7 +5030,34 @@ dumpPublicationNamespace(Archive *fout, const PublicationSchemaInfo *pubsinfo)
 	query = createPQExpBuffer();
 
 	appendPQExpBuffer(query, "ALTER PUBLICATION %s ", fmtId(pubinfo->dobj.name));
-	appendPQExpBuffer(query, "ADD TABLES IN SCHEMA %s;\n", fmtId(schemainfo->dobj.name));
+	appendPQExpBuffer(query, "ADD TABLES IN SCHEMA %s", fmtId(schemainfo->dobj.name));
+
+	/*
+	 * Append EXCEPT clause for any tables that belong to this schema
+	 * and are excluded from the publication.
+	 */
+	for (SimplePtrListCell *cell = pubinfo->except_tables.head; cell; cell = cell->next)
+	{
+		TableInfo  *tbinfo = (TableInfo *) cell->ptr;
+
+		if (strcmp(tbinfo->dobj.namespace->dobj.name, schemainfo->dobj.name) == 0)
+		{
+			if (!has_except)
+			{
+				appendPQExpBufferStr(query, " EXCEPT (");
+				has_except = true;
+			}
+			else
+				appendPQExpBufferStr(query, ", ");
+
+			appendPQExpBuffer(query, "TABLE ONLY %s", fmtId(tbinfo->dobj.name));
+		}
+	}
+
+	if (has_except)
+		appendPQExpBufferStr(query, ")");
+
+	appendPQExpBufferStr(query, ";\n");
 
 	/*
 	 * There is no point in creating drop query as the drop is done by schema
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 9258948b583..d18b3b0d7b9 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3283,6 +3283,30 @@ my %tests = (
 		like => { %full_runs, section_post_data => 1, },
 	},
 
+	'CREATE PUBLICATION pub11' => {
+		create_order => 50,
+		create_sql =>
+		  'CREATE PUBLICATION pub11 FOR TABLES IN SCHEMA dump_test EXCEPT (TABLE test_table);',
+		regexp => qr/^
+			\QCREATE PUBLICATION pub11 WITH (publish = 'insert, update, delete, truncate');\E
+			.*?
+			\QALTER PUBLICATION pub11 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE ONLY test_table);\E
+			/xms,
+		like => { %full_runs, section_post_data => 1, },
+	},
+
+	'CREATE PUBLICATION pub12' => {
+		create_order => 50,
+		create_sql =>
+		  'CREATE PUBLICATION pub12 FOR TABLES IN SCHEMA dump_test EXCEPT (TABLE test_table, dump_test.test_second_table);',
+		regexp => qr/^
+			\QCREATE PUBLICATION pub12 WITH (publish = 'insert, update, delete, truncate');\E
+			.*?
+			\QALTER PUBLICATION pub12 ADD TABLES IN SCHEMA dump_test EXCEPT (TABLE ONLY test_table, TABLE ONLY test_second_table);\E
+			/xms,
+		like => { %full_runs, section_post_data => 1, },
+	},
+
 	'CREATE SUBSCRIPTION sub1' => {
 		create_order => 50,
 		create_sql => 'CREATE SUBSCRIPTION sub1
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index c20ba1d8c01..277ae97ad41 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2373,6 +2373,23 @@ 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 ( TABLE */
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny) &&
+			 !ends_with(prev_wd, ','))
+		COMPLETE_WITH("EXCEPT ( TABLE");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT"))
+		COMPLETE_WITH("( TABLE");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "("))
+		COMPLETE_WITH("TABLE");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", "CURRENT_SCHEMA", "EXCEPT", "(", "TABLE"))
+		COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_current_schema);
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE"))
+	{
+		set_completion_reference(prev4_wd);
+		COMPLETE_WITH_QUERY_VERBATIM(Query_for_list_of_tables_in_schema);
+	}
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD", "TABLES", "IN", "SCHEMA", MatchAny, "EXCEPT", "(", "TABLE", 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 6efed0a0cce..c40a8884a8f 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -591,12 +591,42 @@ CREATE PUBLICATION testpub_except_nokw
 ERROR:  syntax error at or near "testpub_nopk"
 LINE 2:     FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
                                                   ^
+---------------------------------------------
+-- EXCEPT tests for ALTER PUBLICATION
+---------------------------------------------
+CREATE PUBLICATION testpub_alter_except;
+-- fail: non-existing table in EXCEPT clause
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table);
+ERROR:  relation "pub_test.nonexistent_table" does not exist
+-- fail: EXCEPT table belongs to a different schema
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1);
+ERROR:  table "public.testpub_tbl1" in EXCEPT clause does not belong to schema "pub_test"
+LINE 1: ...xcept ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.tes...
+                                                             ^
+-- fail: TABLE keyword is required for the first entry in EXCEPT clause
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+ERROR:  syntax error at or near "testpub_nopk"
+LINE 1: ...lter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_no...
+                                                             ^
+-- ADD: qualified and unqualified names; unqualified is implicitly qualified with the schema
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2);
+\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"
+    "pub_test.testpub_tbl_s2"
+
 -- Cleanup
 RESET client_min_messages;
 DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
 DROP TABLE pub_test.testpub_parted_s CASCADE;
 DROP TABLE testpub_nopk, testpub_tbl_s1;
-DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi, testpub_alter_except;
 ---------------------------------------------
 -- Tests for publications with SEQUENCES
 ---------------------------------------------
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 2e79e9e4112..cd5ed06dcad 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -290,12 +290,30 @@ CREATE PUBLICATION testpub_except_partition
 CREATE PUBLICATION testpub_except_nokw
     FOR TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
 
+---------------------------------------------
+-- EXCEPT tests for ALTER PUBLICATION
+---------------------------------------------
+CREATE PUBLICATION testpub_alter_except;
+
+-- fail: non-existing table in EXCEPT clause
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.nonexistent_table);
+
+-- fail: EXCEPT table belongs to a different schema
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE public.testpub_tbl1);
+
+-- fail: TABLE keyword is required for the first entry in EXCEPT clause
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (testpub_nopk);
+
+-- ADD: qualified and unqualified names; unqualified is implicitly qualified with the schema
+ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (TABLE pub_test.testpub_tbl_s1, testpub_tbl_s2);
+\dRp+ testpub_alter_except
+
 -- Cleanup
 RESET client_min_messages;
 DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
 DROP TABLE pub_test.testpub_parted_s CASCADE;
 DROP TABLE testpub_nopk, testpub_tbl_s1;
-DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi;
+DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_schema_except_multi, testpub_alter_except;
 
 ---------------------------------------------
 -- Tests for publications with SEQUENCES
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 18c7b2c1fca..0ba6d6f8bb2 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -347,6 +347,38 @@ is($result, qq(5),
 $node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
 $node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
 
+# ============================================
+# ALTER PUBLICATION EXCEPT for TABLES IN SCHEMA
+# ============================================
+
+# Truncate subscriber tables to remove data accumulated from previous tests.
+$node_subscriber->safe_psql('postgres',
+	'TRUNCATE sch1.tab_published, sch1.tab_excluded, sch1.parent, sch1.child');
+
+# 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 (TABLE sch1.tab_excluded)"
+);
+$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_published");
+is($result, qq(6),
+	'ALTER ... ADD TABLES IN SCHEMA EXCEPT: included table synced');
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_excluded");
+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)