v51-0003-Support-DROP-EXCEPT-TABLE-in-ALTER-PUBLICATION.patch

From d7c20aed12283550fe96fd58505ecf3616cd8511 Mon Sep 17 00:00:00 2001
From: Shlok Kyal <shlok.kyal.oss@gmail.com>
Date: Wed, 25 Feb 2026 21:26:18 +0530
Subject: [PATCH v51 3/3] 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       | 11 +++----
 src/backend/parser/gram.y                     | 11 ++-----
 src/bin/psql/tab-complete.in.c                |  6 ++--
 src/test/regress/expected/publication.out     | 22 +++++++++-----
 src/test/regress/sql/publication.sql          |  3 +-
 .../t/037_rep_changes_except_table.pl         | 29 +++++++++++++++++++
 6 files changed, 58 insertions(+), 24 deletions(-)

diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index fefc4fcbf6d..0b4a5785b0f 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -41,7 +41,7 @@ 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> [ * ] [, ... ]
     TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
 
 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -63,10 +63,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
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 32ecbb1cf5a..ae78273426f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11032,7 +11032,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;
@@ -11049,18 +11049,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 0fc16a3e110..0a3705bb8c6 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 a122fe08cee..f301b7489c1 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -128,10 +128,11 @@ 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 defined as NON FOR ALL TABLES
+DETAIL:  EXCEPT Tables cannot be added to or dropped from non FOR ALL TABLES publications.
 -- 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 defined as NON FOR ALL TABLES
@@ -225,10 +226,11 @@ 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 defined as NON FOR ALL TABLES
+DETAIL:  EXCEPT Tables cannot be added to or dropped from non FOR ALL TABLES publications.
 -- 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 defined as NON FOR ALL TABLES
@@ -285,12 +287,18 @@ ALTER PUBLICATION testpub_foralltables_excepttable SET EXCEPT TABLE testpub_tbl1
 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 
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ 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;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 2086539fdc2..023d4bf2ec6 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -138,8 +138,9 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (test
 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_rep_changes_except_table.pl b/src/test/subscription/t/037_rep_changes_except_table.pl
index cadff3d8b6a..7dc6b7693e6 100644
--- a/src/test/subscription/t/037_rep_changes_except_table.pl
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -137,6 +137,35 @@ $result =
   $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.tab1");
 is($result, qq(0), 'check replicated inserts on subscriber');
 
+# Remove sch1.tab1 from the publication's EXCEPT list so that it becomes part
+# of the ALL TABLES publication.
+$node_publisher->safe_psql('postgres',
+	"ALTER PUBLICATION tap_pub_schema DROP EXCEPT TABLE sch1.tab1");
+
+# Refresh the subscription so the subscriber picks up the updated
+# publication definition and initiates table synchronization.
+$node_subscriber->safe_psql('postgres',
+	"ALTER SUBSCRIPTION tap_sub_schema REFRESH PUBLICATION");
+
+# Wait for initial table sync to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher,
+	'tap_sub_schema');
+
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.tab1");
+is($result, qq(20), 'check replicated inserts on subscriber');
+
+# Insert additional rows on the publisher after synchronization.
+$node_publisher->safe_psql('postgres',
+	"INSERT INTO sch1.tab1 VALUES(generate_series(21,30))");
+
+$node_publisher->wait_for_catchup('tap_sub_schema');
+
+# Verify that the new inserts are also replicated.
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.tab1");
+is($result, qq(30), 'check replicated inserts on subscriber');
+
 # cleanup
 $node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
 $node_publisher->safe_psql(
-- 
2.43.0