v1-0003-Add-EXCEPT-support-to-ALTER-PUBLICATION-SET-TABLE.patch

From 197d69c52b7946a1e17d122fa037db5d5194ed4f Mon Sep 17 00:00:00 2001
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Tue, 14 Apr 2026 11:34:07 +0530
Subject: [PATCH v1 3/3] Add EXCEPT support to ALTER PUBLICATION SET TABLES IN
 SCHEMA

Extend AlterPublicationExceptTables() with the AP_SetObjects case,
which redefine the publication and replaces the entire EXCEPT list.

Syntax:
ALTER PUBLICATION pub SET TABLES IN SCHEMA s EXCEPT (s.t1, s.t2);
---
 src/backend/catalog/pg_publication.c      |   2 +-
 src/backend/commands/publicationcmds.c    | 145 +++++++++++++++++++++-
 src/test/regress/expected/publication.out |  21 ++++
 src/test/regress/sql/publication.sql      |  12 ++
 src/test/subscription/t/037_except.pl     |  90 ++++++++++++++
 5 files changed, 267 insertions(+), 3 deletions(-)

diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 8e6c4015d86..c03548413e8 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -986,7 +986,7 @@ GetIncludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
 List *
 GetExcludedPublicationTables(Oid pubid, PublicationPartOpt pub_partopt)
 {
-	Assert(GetPublication(pubid)->alltables);
+	Assert(GetPublication(pubid)->alltables || is_schema_publication(pubid));
 
 	return get_publication_relations(pubid, pub_partopt, true);
 }
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index fd73511220d..bde99d6bb90 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1611,9 +1611,11 @@ AlterPublicationExceptTables(AlterPublicationStmt *stmt,
 	Oid			pubid = pubform->oid;
 
 	/*
-	 * Nothing to do if no EXCEPT entries.
+	 * Nothing to do if no EXCEPT entries, except in SET: for that it is
+	 * quite possible that the user has removed all exceptions, in which case
+	 * we need to drop any existing ones.
 	 */
-	if (!exceptrelations)
+	if (!exceptrelations && stmt->action != AP_SetObjects)
 		return;
 
 	/*
@@ -1700,6 +1702,106 @@ AlterPublicationExceptTables(AlterPublicationStmt *stmt,
 
 		PublicationAddTables(pubid, rels, false, stmt);
 
+		CloseTableList(rels);
+	}
+	else						/* AP_SetObjects */
+	{
+		List	   *oldexceptrelids = NIL;
+		List	   *newexceptrelids = NIL;
+		List	   *delrelids = NIL;
+		List	   *rels;
+		List	   *explicitrelids;
+		List	   *pubschemaidlist;
+		ListCell   *lc;
+
+		rels = OpenTableList(exceptrelations);
+
+		/* Collect OIDs of the desired new except list. */
+		foreach(lc, rels)
+		{
+			PublicationRelInfo *pri = (PublicationRelInfo *) lfirst(lc);
+
+			newexceptrelids = lappend_oid(newexceptrelids,
+										  RelationGetRelid(pri->relation));
+		}
+
+		/*
+		 * Build the effective schema list: schemas already in the publication
+		 * plus any being set in this same command.  The latter may not yet be
+		 * visible via a catalog scan, 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)));
+		}
+
+		/*
+		 * Get the current set of except entries.  Only FOR ALL TABLES and
+		 * schema-level publications can have except entries; for any other
+		 * publication type oldexceptrelids stays NIL.
+		 *
+		 * Note: we check is_schema_publication() against the current catalog
+		 * state (before AlterPublicationSchemas has run), so if the caller is
+		 * doing SET TABLE t1 to convert a schema publication into a plain table
+		 * publication, is_schema_publication() still returns true here.  That
+		 * is intentional: it lets us discover and clean up any stale except
+		 * entries that belong to the old schema definition.
+		 */
+		if (GetPublication(pubid)->alltables || is_schema_publication(pubid))
+			oldexceptrelids = GetExcludedPublicationTables(pubid,
+														   PUBLICATION_PART_ROOT);
+
+		/* Build a list of old except entries not present in the new list. */
+		foreach(lc, oldexceptrelids)
+		{
+			Oid			oldrelid = lfirst_oid(lc);
+
+			if (!list_member_oid(newexceptrelids, oldrelid))
+				delrelids = lappend_oid(delrelids, oldrelid);
+		}
+
+		/* Drop old except entries not present in the new list. */
+		foreach(lc, delrelids)
+		{
+			Oid			relid = lfirst_oid(lc);
+			Oid			proid;
+			ObjectAddress obj;
+
+			proid = GetSysCacheOid2(PUBLICATIONRELMAP,
+									Anum_pg_publication_rel_oid,
+									ObjectIdGetDatum(relid),
+									ObjectIdGetDatum(pubid));
+			if (!OidIsValid(proid))
+				continue;		/* already gone */
+
+			ObjectAddressSet(obj, PublicationRelRelationId, proid);
+			performDeletion(&obj, DROP_CASCADE, 0);
+		}
+
+		/* Add new except entries, skipping any already present. */
+		PublicationAddTables(pubid, rels, true, stmt);
+
 		CloseTableList(rels);
 	}
 }
@@ -2354,6 +2456,8 @@ PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok)
 	foreach(lc, schemas)
 	{
 		Oid			schemaid = lfirst_oid(lc);
+		List	   *exceptoids;
+		ListCell   *elc;
 
 		psid = GetSysCacheOid2(PUBLICATIONNAMESPACEMAP,
 							   Anum_pg_publication_namespace_oid,
@@ -2370,8 +2474,45 @@ PublicationDropSchemas(Oid pubid, List *schemas, bool missing_ok)
 							get_namespace_name(schemaid))));
 		}
 
+		/*
+		 * Collect prexcept rows for tables belonging to this schema before
+		 * removing the schema entry.  GetExcludedPublicationTables relies on
+		 * is_schema_publication(), which scans pg_publication_namespace; if
+		 * this is the last schema in the publication, performDeletion() below
+		 * would remove that row and make is_schema_publication() return
+		 * false, tripping the assertion.
+		 */
+		exceptoids = GetExcludedPublicationTables(pubid, PUBLICATION_PART_ROOT);
+
 		ObjectAddressSet(obj, PublicationNamespaceRelationId, psid);
 		performDeletion(&obj, DROP_CASCADE, 0);
+
+		/*
+		 * Drop any prexcept rows for tables belonging to this schema. These
+		 * rows have no pg_depend entry pointing at the
+		 * pg_publication_namespace row, so they are not cascaded by the
+		 * performDeletion() call above and must be cleaned up explicitly.
+		 */
+		foreach(elc, exceptoids)
+		{
+			Oid			relid = lfirst_oid(elc);
+			Oid			proid;
+
+			if (get_rel_namespace(relid) != schemaid)
+				continue;
+
+			proid = GetSysCacheOid2(PUBLICATIONRELMAP,
+									Anum_pg_publication_rel_oid,
+									ObjectIdGetDatum(relid),
+									ObjectIdGetDatum(pubid));
+			if (!OidIsValid(proid))
+				continue;		/* already gone */
+
+			ObjectAddressSet(obj, PublicationRelRelationId, proid);
+			performDeletion(&obj, DROP_CASCADE, 0);
+		}
+
+		list_free(exceptoids);
 	}
 }
 
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index e563553e8eb..f346e77ee57 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -517,6 +517,27 @@ Tables from schemas:
 Except tables:
     "pub_test.testpub_tbl_s1"
 
+-- SET: replace the except list (keep same schema, different except table)
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (pub_test.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_s2"
+
+-- error: EXCEPT is not allowed with DROP
+ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_tbl_s2);
+ERROR:  EXCEPT clause is not supported with DROP in ALTER PUBLICATION
+-- error: except table must be schema-qualified
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (testpub_tbl_s1);
+ERROR:  table "testpub_tbl_s1" in EXCEPT clause must be schema-qualified
+-- error: except table's schema (public) not in the publication's schema list (pub_test)
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (public.testpub_tbl1);
+ERROR:  table "public.testpub_tbl1" in EXCEPT clause does not belong to any schema listed in the publication
 RESET client_min_messages;
 DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
 DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_alter_except;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 3436b832eca..6de78800aa5 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -249,6 +249,18 @@ CREATE PUBLICATION testpub_alter_except;
 ALTER PUBLICATION testpub_alter_except ADD TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_tbl_s1);
 \dRp+ testpub_alter_except
 
+-- SET: replace the except list (keep same schema, different except table)
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_tbl_s2);
+\dRp+ testpub_alter_except
+
+-- error: EXCEPT is not allowed with DROP
+ALTER PUBLICATION testpub_alter_except DROP TABLES IN SCHEMA pub_test EXCEPT (pub_test.testpub_tbl_s2);
+
+-- error: except table must be schema-qualified
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (testpub_tbl_s1);
+-- error: except table's schema (public) not in the publication's schema list (pub_test)
+ALTER PUBLICATION testpub_alter_except SET TABLES IN SCHEMA pub_test EXCEPT (public.testpub_tbl1);
+
 RESET client_min_messages;
 DROP TABLE pub_test.testpub_tbl_s1, pub_test.testpub_tbl_s2;
 DROP PUBLICATION testpub_schema_except1, testpub_schema_except2, testpub_alter_except;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 9675db18bef..e622bfb3d7f 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -378,6 +378,66 @@ $result =
 is($result, qq(0),
 	'ALTER ... ADD TABLES IN SCHEMA EXCEPT: excluded table not synced');
 
+# SET: replace the except list; tab_exc is now included and tab_pub is excluded.
+$node_publisher->safe_psql('postgres',
+	"ALTER PUBLICATION sch_pub SET TABLES IN SCHEMA sch1 EXCEPT (sch1.tab_pub)"
+);
+$node_subscriber->safe_psql('postgres',
+	"ALTER SUBSCRIPTION sch_sub REFRESH PUBLICATION");
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$node_publisher->safe_psql(
+	'postgres', qq(
+	INSERT INTO sch1.tab_pub VALUES (7);
+	INSERT INTO sch1.tab_exc VALUES (7);
+));
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_exc");
+is($result, qq(1),
+	'ALTER ... SET TABLES IN SCHEMA EXCEPT: newly included table is replicated'
+);
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_pub");
+is($result, qq(6),
+	'ALTER ... SET TABLES IN SCHEMA EXCEPT: now-excluded table is not replicated'
+);
+
+# SET without EXCEPT: clears the except list; both tables are now published.
+# tab_pub will be re-synced because REFRESH removed its entry when it was
+# excluded.  Truncate the subscriber copy beforehand so the re-sync produces
+# a predictable count: publisher has 7 rows (6 original + INSERT(7)), so the
+# subscriber ends up with 7 after re-sync, then 8 after INSERT(8).
+$node_subscriber->safe_psql('postgres', 'TRUNCATE sch1.tab_pub');
+$node_publisher->safe_psql('postgres',
+	"ALTER PUBLICATION sch_pub SET TABLES IN SCHEMA sch1");
+$node_subscriber->safe_psql('postgres',
+	"ALTER SUBSCRIPTION sch_sub REFRESH PUBLICATION");
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'sch_sub');
+
+$node_publisher->safe_psql(
+	'postgres', qq(
+	INSERT INTO sch1.tab_pub VALUES (8);
+	INSERT INTO sch1.tab_exc VALUES (8);
+));
+$node_publisher->wait_for_catchup('sch_sub');
+
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_pub");
+is($result, qq(8),
+	'ALTER ... SET TABLES IN SCHEMA (no EXCEPT): tab_pub replicated after except list cleared'
+);
+$result =
+  $node_subscriber->safe_psql('postgres',
+	"SELECT count(*) FROM sch1.tab_exc");
+is($result, qq(2),
+	'ALTER ... SET TABLES IN SCHEMA (no EXCEPT): tab_exc replicated after except list cleared'
+);
+
 $node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION sch_sub');
 $node_publisher->safe_psql('postgres', 'DROP PUBLICATION sch_pub');
 
@@ -445,6 +505,36 @@ $node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
 $node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1');
 $node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
 
+# OK when a table is excluded by a TABLES IN SCHEMA EXCEPT publication,
+# but is included by another publication.
+$node_publisher->safe_psql('postgres', 'TRUNCATE tab1');
+$node_subscriber->safe_psql('postgres', 'TRUNCATE tab1');
+
+$node_publisher->safe_psql(
+	'postgres', qq(
+	CREATE PUBLICATION tap_pub1 FOR TABLES IN SCHEMA public EXCEPT (public.tab1);
+	CREATE PUBLICATION tap_pub2 FOR TABLE tab1;
+	INSERT INTO tab1 VALUES(1);
+));
+$node_subscriber->psql('postgres',
+	"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub1, tap_pub2"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub');
+
+$node_publisher->safe_psql('postgres', qq(INSERT INTO tab1 VALUES(2)));
+$node_publisher->wait_for_catchup('tap_sub');
+
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY a");
+is( $result, qq(1
+2),
+	"TABLES IN SCHEMA EXCEPT: table excluded in schema pub but included by another pub is replicated"
+);
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
+
 $node_publisher->stop('fast');
 
 done_testing();
-- 
2.50.1 (Apple Git-155)