v57-0002-Support-DROP-EXCEPT-TABLE-in-ALTER-PUBLICATION.patch

From 0ff9787dbfb8011ddcb004094a227c8c83828815 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Thu, 5 Mar 2026 14:29:46 +0530
Subject: [PATCH v57 2/2] Support DROP EXCEPT TABLE in ALTER PUBLICATION

Extend ALTER PUBLICATION to support DROP EXCEPT TABLE for
publications defined with FOR ALL TABLES.
---
 doc/src/sgml/ref/alter_publication.sgml   | 18 +++++++++++----
 src/backend/commands/publicationcmds.c    |  2 +-
 src/backend/commands/tablecmds.c          |  2 +-
 src/backend/parser/gram.y                 | 11 ++-------
 src/bin/psql/tab-complete.in.c            |  6 +++--
 src/test/regress/expected/publication.out | 28 +++++++++++++++--------
 src/test/regress/sql/publication.sql      |  3 ++-
 src/test/subscription/t/037_except.pl     | 21 +++++++++++++++++
 8 files changed, 62 insertions(+), 29 deletions(-)

diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 579dd4bfba4..b3467d54549 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -41,7 +41,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
 
 <phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
 
-    TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
+    EXCEPT TABLE [ ONLY ] ( <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ] )
+    TABLE [ ONLY ] ( <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ] )
     TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -63,10 +64,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
    publication.  The <literal>SET</literal> clause will replace the list of
    except tables/tables/schemas in the publication with the specified list; the
    existing except tables/tables/schemas that were present in the publication
-   will be removed.  The <literal>ADD</literal> and <literal>DROP</literal>
-   clauses will add and remove one or more tables/schemas from the publication.
-   Note that adding tables/schemas to a publication that is already subscribed
-   to will require an
+   will be removed.  The <literal>ADD</literal> clauses will add one or more
+   tables/schemas to the publication.  The <literal>DROP</literal> clauses
+   will remove one or more except tables/tables/schemas from the publication.
+   Note that adding tables/schemas or dropping except tables to a publication
+   that is already subscribed to will require an
    <link linkend="sql-altersubscription-params-refresh-publication">
    <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link> action on the
    subscribing side in order to become effective. Note also that
@@ -235,6 +237,12 @@ ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname),
    Replace the publication's EXCEPT table list:
 <programlisting>
 ALTER PUBLICATION mypublication SET EXCEPT TABLE (users, departments);
+</programlisting></para>
+
+  <para>
+   Remove tables from the publication's EXCEPT table list:
+<programlisting>
+ALTER PUBLICATION mypublication DROP EXCEPT TABLE (users, departments);
 </programlisting></para>
 
   <para>
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 166fe3b0c3d..f43edd33098 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1562,7 +1562,7 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
 				errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
 				errmsg("publication \"%s\" is not defined as FOR ALL TABLES",
 					   NameStr(pubform->pubname)),
-				errdetail("EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES."));
+				errdetail("EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES."));
 }
 
 /*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 104b2d74d1d..8c57cfe9592 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20401,7 +20401,7 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
 							  RelationGetRelationName(attachrel),
 							  pubnames.data),
 				errdetail("The publication EXCEPT clause cannot contain tables that are partitions."),
-				errhint("Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE before attaching the table."));
+				errhint("Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE or DROP EXCEPT TABLE before attaching the table."));
 	}
 
 	list_free(exceptpuboids);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7e99824b8b1..71f2e27816e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11031,7 +11031,7 @@ AlterPublicationStmt:
 					if (has_except_table)
 						ereport(ERROR,
 								errcode(ERRCODE_SYNTAX_ERROR),
-								errmsg("EXCEPT TABLE clause allowed only for SET clause"));
+								errmsg("EXCEPT TABLE clause allowed only for SET/DROP clause"));
 
 					n->action = AP_AddObjects;
 					$$ = (Node *) n;
@@ -11048,18 +11048,11 @@ AlterPublicationStmt:
 				}
 			| ALTER PUBLICATION name DROP pub_obj_list
 				{
-					bool has_except_table = false;
 					AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
 
 					n->pubname = $3;
 					n->pubobjects = $5;
-					has_except_table = preprocess_pubobj_list(n->pubobjects,
-															  yyscanner);
-					if (has_except_table)
-						ereport(ERROR,
-								errcode(ERRCODE_SYNTAX_ERROR),
-								errmsg("EXCEPT TABLE clause allowed only for SET clause"));
-
+					preprocess_pubobj_list(n->pubobjects, yyscanner);
 					n->action = AP_DropObjects;
 					$$ = (Node *) n;
 				}
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index b0710a55422..e280a62c586 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2317,8 +2317,10 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH(",");
 	/* ALTER PUBLICATION <name> DROP */
 	else if (Matches("ALTER", "PUBLICATION", MatchAny, "DROP"))
-		COMPLETE_WITH("TABLES IN SCHEMA", "TABLE");
-		/* ALTER PUBLICATION <name> SET */
+		COMPLETE_WITH("EXCEPT", "TABLES IN SCHEMA", "TABLE");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "DROP", "EXCEPT"))
+		COMPLETE_WITH("TABLE");
+	/* ALTER PUBLICATION <name> SET */
 	else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET"))
 		COMPLETE_WITH("(", "EXCEPT", "TABLES IN SCHEMA", "TABLE");
 	else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "EXCEPT"))
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 72480eecf71..ef0bffe97bb 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -128,14 +128,15 @@ Tables from schemas:
 
 -- fail - can't add an EXCEPT TABLE to 'FOR TABLE' publication
 ALTER PUBLICATION testpub_fortable ADD EXCEPT TABLE (testpub_tbl1);
-ERROR:  EXCEPT TABLE clause allowed only for SET clause
+ERROR:  EXCEPT TABLE clause allowed only for SET/DROP clause
 -- fail - can't drop an EXCEPT TABLE from 'FOR TABLE' publication
 ALTER PUBLICATION testpub_fortable DROP EXCEPT TABLE (testpub_tbl1);
-ERROR:  EXCEPT TABLE clause allowed only for SET clause
+ERROR:  publication "testpub_fortable" is not defined as FOR ALL TABLES
+DETAIL:  EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES.
 -- fail - can't set an EXCEPT TABLE to 'FOR TABLE' publication
 ALTER PUBLICATION testpub_fortable SET EXCEPT TABLE (testpub_tbl1);
 ERROR:  publication "testpub_fortable" is not defined as FOR ALL TABLES
-DETAIL:  EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES.
+DETAIL:  EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES.
 SET client_min_messages = 'ERROR';
 CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
 -- should be able to create publication with schema and table of the same
@@ -225,14 +226,15 @@ Not-null constraints:
 
 -- fail - can't add an EXCEPT TABLE to schema publication
 ALTER PUBLICATION testpub_forschema ADD EXCEPT TABLE (pub_test.testpub_nopk);
-ERROR:  EXCEPT TABLE clause allowed only for SET clause
+ERROR:  EXCEPT TABLE clause allowed only for SET/DROP clause
 -- fail - can't drop an EXCEPT TABLE from schema publication
 ALTER PUBLICATION testpub_forschema DROP EXCEPT TABLE (pub_test.testpub_nopk);
-ERROR:  EXCEPT TABLE clause allowed only for SET clause
+ERROR:  publication "testpub_forschema" is not defined as FOR ALL TABLES
+DETAIL:  EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES.
 -- fail - can't set an EXCEPT TABLE to schema publication
 ALTER PUBLICATION testpub_forschema SET EXCEPT TABLE (pub_test.testpub_nopk);
 ERROR:  publication "testpub_forschema" is not defined as FOR ALL TABLES
-DETAIL:  EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES.
+DETAIL:  EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES.
 ---------------------------------------------
 -- EXCEPT TABLE tests for normal tables
 ---------------------------------------------
@@ -284,12 +286,18 @@ ALTER PUBLICATION testpub_foralltables_excepttable SET EXCEPT TABLE (testpub_tbl
 Except tables:
     "public.testpub_tbl1"
 
--- fail - Dropping EXCEPT table is not supported.
+-- Drop table from the EXCEPT list of a FOR ALL TABLES publication.
 ALTER PUBLICATION testpub_foralltables_excepttable DROP EXCEPT TABLE (testpub_tbl1);
-ERROR:  EXCEPT TABLE clause allowed only for SET clause
+ \dRp+ testpub_foralltables_excepttable
+                                                 Publication testpub_foralltables_excepttable
+          Owner           | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description 
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | t          | f             | t       | t       | t       | t         | none              | f        | 
+(1 row)
+
 -- fail - Adding EXCEPT table is not supported.
 ALTER PUBLICATION testpub_foralltables_excepttable ADD EXCEPT TABLE (testpub_tbl1);
-ERROR:  EXCEPT TABLE clause allowed only for SET clause
+ERROR:  EXCEPT TABLE clause allowed only for SET/DROP clause
 RESET client_min_messages;
 DROP TABLE testpub_tbl2;
 DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
@@ -397,7 +405,7 @@ CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
 ALTER TABLE tab_main ATTACH PARTITION testpub_root FOR VALUES FROM (0) TO (200);
 ERROR:  cannot attach table "testpub_root" as partition because it is referenced in publication "testpub8" EXCEPT clause
 DETAIL:  The publication EXCEPT clause cannot contain tables that are partitions.
-HINT:  Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE before attaching the table.
+HINT:  Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE or DROP EXCEPT TABLE before attaching the table.
 RESET client_min_messages;
 DROP TABLE testpub_root, testpub_part1, tab_main;
 DROP PUBLICATION testpub8;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 3fa86987a34..c0844e276d1 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -137,8 +137,9 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE
 ALTER PUBLICATION testpub_foralltables_excepttable SET EXCEPT TABLE (testpub_tbl1);
 \dRp+ testpub_foralltables_excepttable
 
--- fail - Dropping EXCEPT table is not supported.
+-- Drop table from the EXCEPT list of a FOR ALL TABLES publication.
 ALTER PUBLICATION testpub_foralltables_excepttable DROP EXCEPT TABLE (testpub_tbl1);
+ \dRp+ testpub_foralltables_excepttable
 
 -- fail - Adding EXCEPT table is not supported.
 ALTER PUBLICATION testpub_foralltables_excepttable ADD EXCEPT TABLE (testpub_tbl1);
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 647ae8433ad..45f3707d69e 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -186,6 +186,27 @@ $result =
 is($result, qq(20),
 	'check that the data is copied as the tab1 is removed from EXCEPT TABLE clause');
 
+# Remove tab2 from the publication's EXCEPT list so that it becomes part
+# of the ALL TABLES publication.
+$node_publisher->safe_psql('postgres',
+	"ALTER PUBLICATION tab_pub DROP EXCEPT TABLE (tab2)");
+
+# Refresh the subscription so the subscriber picks up the updated
+# publication definition and initiates table synchronization.
+$node_subscriber->safe_psql('postgres',
+	"ALTER SUBSCRIPTION tab_sub REFRESH PUBLICATION");
+
+# Wait for initial table sync to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher,
+	'tab_sub');
+
+# Verify that table synchronization occurs once tab2 is removed from the
+# EXCEPT TABLE clause via SET EXCEPT TABLE.
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab2");
+is($result, qq(10), 
+	'check that the data is copied as the tab1 is removed from EXCEPT TABLE clause');
+
 # cleanup
 $node_subscriber->safe_psql(
 	'postgres', qq(
-- 
2.43.0