v49-0002-Support-DROP-and-SET-EXCEPT-TABLE-in-ALTER-PUBLI.patch

From fd4dc368aad17d613e9795d4fe9b89d15bf9baa0 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Wed, 25 Feb 2026 09:58:48 +0530
Subject: [PATCH v49 2/2] Support DROP and SET EXCEPT TABLE in ALTER
 PUBLICATION

Extend ALTER PUBLICATION to support DROP EXCEPT TABLE and
SET EXCEPT TABLE for publications defined with FOR ALL TABLES.
---
 doc/src/sgml/ref/alter_publication.sgml       | 26 ++++---
 src/backend/commands/publicationcmds.c        | 63 ++++++++++-------
 src/backend/parser/gram.y                     | 68 +++++++++++++++----
 src/bin/psql/tab-complete.in.c                |  8 ++-
 src/test/regress/expected/publication.out     | 45 ++++++++++++
 src/test/regress/sql/publication.sql          | 25 +++++++
 .../t/037_rep_changes_except_table.pl         | 29 ++++++++
 7 files changed, 218 insertions(+), 46 deletions(-)

diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 028770f2149..d1798e04d29 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -21,21 +21,27 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_object</replaceable> [, ...]
-ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_object</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_add_object</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_set_object</replaceable> [, ...]
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replaceable class="parameter">publication_drop_object</replaceable> [, ...]
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
 
-<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
+<phrase>where <replaceable class="parameter">publication_add_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 } [, ... ]
 
+<phrase>where <replaceable class="parameter">publication_set_object</replaceable> is one of:</phrase>
+
+    EXCEPT TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [, ... ]
+    TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
+    TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+
 <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>
@@ -55,11 +61,13 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
   <para>
    The first three variants change which tables/schemas are part of the
    publication.  The <literal>SET</literal> clause will replace the list of
-   tables/schemas in the publication with the specified list; the existing
-   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
+   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> clauses will add one or more
+   tables/schemas from 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/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 40772c67d75..dac8d13414e 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -181,7 +181,7 @@ parse_publication_options(ParseState *pstate,
  */
 static void
 ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
-						   List **rels, List **schemas)
+						   List **rels, List **exceptrels, List **schemas)
 {
 	ListCell   *cell;
 	PublicationObjSpec *pubobj;
@@ -200,7 +200,7 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
 		{
 			case PUBLICATIONOBJ_EXCEPT_TABLE:
 				pubobj->pubtable->except = true;
-				*rels = lappend(*rels, pubobj->pubtable);
+				*exceptrels = lappend(*exceptrels, pubobj->pubtable);
 				break;
 			case PUBLICATIONOBJ_TABLE:
 				pubobj->pubtable->except = false;
@@ -849,8 +849,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 	char		publish_generated_columns;
 	AclResult	aclresult;
 	List	   *relations = NIL;
+	List	   *exceptrelations = NIL;
 	List	   *schemaidlist = NIL;
-	List	   *rels = NIL;
 
 	/* must have CREATE privilege on database */
 	aclresult = object_aclcheck(DatabaseRelationId, MyDatabaseId, GetUserId(), ACL_CREATE);
@@ -936,20 +936,16 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 
 	/* Associate objects with the publication. */
 	ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
-							   &schemaidlist);
-	if (relations != NIL)
-	{
-		Assert(!stmt->for_all_sequences);
-		rels = OpenTableList(relations);
-	}
+							   &exceptrelations, &schemaidlist);
 
 	if (stmt->for_all_tables)
 	{
 		/* Process EXCEPT table list */
-		if (relations != NIL)
+		if (exceptrelations != NIL)
 		{
-			Assert(rels != NIL);
-			PublicationAddTables(puboid, rels, true, NULL);
+			exceptrelations = OpenTableList(exceptrelations);
+			PublicationAddTables(puboid, exceptrelations, true, NULL);
+			CloseTableList(exceptrelations);
 		}
 
 		/*
@@ -969,6 +965,9 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 
 		if (relations != NIL)
 		{
+			List	   *rels;
+
+			rels = OpenTableList(relations);
 			TransformPubWhereClauses(rels, pstate->p_sourcetext,
 									 publish_via_partition_root);
 
@@ -977,6 +976,7 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 									   publish_via_partition_root);
 
 			PublicationAddTables(puboid, rels, true, NULL);
+			CloseTableList(rels);
 		}
 
 		if (schemaidlist != NIL)
@@ -990,9 +990,6 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
 		}
 	}
 
-	if (rels != NIL)
-		CloseTableList(rels);
-
 	table_close(rel, RowExclusiveLock);
 
 	InvokeObjectPostCreateHook(PublicationRelationId, puboid, 0);
@@ -1273,15 +1270,24 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
 		PublicationDropTables(pubid, rels, false);
 	else						/* AP_SetObjects */
 	{
-		List	   *oldrelids = GetIncludedPublicationRelations(pubid,
-																PUBLICATION_PART_ROOT);
+		bool		isexcept = pubform->puballtables;
+		List	   *oldrelids;
 		List	   *delrels = NIL;
 		ListCell   *oldlc;
 
-		TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
+		if (isexcept)
+			oldrelids = GetExcludedPublicationTables(pubid,
+													 PUBLICATION_PART_ROOT);
+		else
+		{
+			oldrelids = GetIncludedPublicationRelations(pubid,
+														PUBLICATION_PART_ROOT);
 
-		CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
-								   pubform->pubviaroot);
+			TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
+
+			CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
+									   pubform->pubviaroot);
+		}
 
 		/*
 		 * To recreate the relation list for the publication, look for
@@ -1489,7 +1495,7 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
  */
 static void
 CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
-					  List *tables, List *schemaidlist)
+					  List *tables, List *excepttables, List *schemaidlist)
 {
 	Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
 
@@ -1547,6 +1553,14 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
 						   NameStr(pubform->pubname)),
 					errdetail("Tables or sequences cannot be added to or dropped from FOR ALL SEQUENCES publications."));
 	}
+
+	/* Check that user is allowed to manipulate the publication tables. */
+	if (excepttables && !pubform->puballtables)
+		ereport(ERROR,
+				errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+				errmsg("publication \"%s\" is defined as NON FOR ALL TABLES",
+					   NameStr(pubform->pubname)),
+				errdetail("EXCEPT Tables cannot be added to or dropped from non FOR ALL TABLES publications."));
 }
 
 /*
@@ -1585,13 +1599,15 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
 	else
 	{
 		List	   *relations = NIL;
+		List	   *exceptrelations = NIL;
 		List	   *schemaidlist = NIL;
 		Oid			pubid = pubform->oid;
 
 		ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
-								   &schemaidlist);
+								   &exceptrelations, &schemaidlist);
 
-		CheckAlterPublication(stmt, tup, relations, schemaidlist);
+		CheckAlterPublication(stmt, tup, relations, exceptrelations,
+							  schemaidlist);
 
 		heap_freetuple(tup);
 
@@ -1612,6 +1628,7 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
 					errmsg("publication \"%s\" does not exist",
 						   stmt->pubname));
 
+		relations = list_concat(relations, exceptrelations);
 		AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
 							   schemaidlist != NIL);
 		AlterPublicationSchemas(stmt, tup, schemaidlist);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index de4374e74d5..298cd747c35 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -207,7 +207,7 @@ static void preprocess_pub_all_objtype_list(List *all_objects_list,
 											bool *all_tables,
 											bool *all_sequences,
 											core_yyscan_t yyscanner);
-static void preprocess_pubobj_list(List *pubobjspec_list,
+static bool preprocess_pubobj_list(List *pubobjspec_list,
 								   core_yyscan_t yyscanner);
 static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
@@ -581,7 +581,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				Bit ConstBit BitWithLength BitWithoutLength
 %type <str>		character
 %type <str>		extract_arg
-%type <boolean> opt_varying opt_timezone opt_no_inherit
+%type <boolean> opt_varying opt_timezone opt_no_inherit opt_except
 
 %type <ival>	Iconst SignedIconst
 %type <str>		Sconst comment_text notify_payload
@@ -10830,16 +10830,28 @@ CreatePublicationStmt:
 				}
 			| CREATE PUBLICATION name FOR pub_obj_list opt_definition
 				{
+					bool has_except_table;
 					CreatePublicationStmt *n = makeNode(CreatePublicationStmt);
 
 					n->pubname = $3;
 					n->options = $6;
 					n->pubobjects = (List *) $5;
-					preprocess_pubobj_list(n->pubobjects, yyscanner);
+					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 ALL TABLES PUBLICATION"));
+
 					$$ = (Node *) n;
 				}
 		;
 
+opt_except:
+			EXCEPT									{ $$ = true; }
+			| /*EMPTY*/								{ $$ = false; }
+		;
+
 /*
  * FOR TABLE and FOR TABLES IN SCHEMA specifications
  *
@@ -10854,14 +10866,14 @@ CreatePublicationStmt:
  * relation_expr here.
  */
 PublicationObjSpec:
-			TABLE relation_expr opt_column_list OptWhereClause
+			opt_except TABLE relation_expr opt_column_list OptWhereClause
 				{
 					$$ = makeNode(PublicationObjSpec);
-					$$->pubobjtype = PUBLICATIONOBJ_TABLE;
+					$$->pubobjtype = ($1) ? PUBLICATIONOBJ_EXCEPT_TABLE : PUBLICATIONOBJ_TABLE;
 					$$->pubtable = makeNode(PublicationTable);
-					$$->pubtable->relation = $2;
-					$$->pubtable->columns = $3;
-					$$->pubtable->whereClause = $4;
+					$$->pubtable->relation = $3;
+					$$->pubtable->columns = $4;
+					$$->pubtable->whereClause = $5;
 				}
 			| TABLES IN_P SCHEMA ColId
 				{
@@ -11012,11 +11024,18 @@ AlterPublicationStmt:
 				}
 			| ALTER PUBLICATION name ADD_P pub_obj_list
 				{
+					bool has_except_table = false;
 					AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
 
 					n->pubname = $3;
 					n->pubobjects = $5;
-					preprocess_pubobj_list(n->pubobjects, yyscanner);
+					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/DROP clause"));
+
 					n->action = AP_AddObjects;
 					$$ = (Node *) n;
 				}
@@ -19882,16 +19901,19 @@ preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects,
 /*
  * Process pubobjspec_list to check for errors in any of the objects and
  * convert PUBLICATIONOBJ_CONTINUATION into appropriate PublicationObjSpecType.
+ *
+ * Return true if an EXCEPT table is found.
  */
-static void
+static bool
 preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
 {
 	ListCell   *cell;
 	PublicationObjSpec *pubobj;
 	PublicationObjSpecType prevobjtype = PUBLICATIONOBJ_CONTINUATION;
+	bool		foundexcepttable = false;
 
 	if (!pubobjspec_list)
-		return;
+		return false;
 
 	pubobj = (PublicationObjSpec *) linitial(pubobjspec_list);
 	if (pubobj->pubobjtype == PUBLICATIONOBJ_CONTINUATION)
@@ -19908,7 +19930,8 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
 		if (pubobj->pubobjtype == PUBLICATIONOBJ_CONTINUATION)
 			pubobj->pubobjtype = prevobjtype;
 
-		if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE)
+		if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE ||
+			pubobj->pubobjtype == PUBLICATIONOBJ_EXCEPT_TABLE)
 		{
 			/* relation name or pubtable must be set for this type of object */
 			if (!pubobj->name && !pubobj->pubtable)
@@ -19927,6 +19950,25 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
 				pubobj->pubtable = pubtable;
 				pubobj->name = NULL;
 			}
+
+			if (pubobj->pubobjtype == PUBLICATIONOBJ_EXCEPT_TABLE)
+			{
+				foundexcepttable = true;
+
+				/* WHERE clause is not allowed on an except table */
+				if (pubobj->pubtable->whereClause)
+					ereport(ERROR,
+							errcode(ERRCODE_SYNTAX_ERROR),
+							errmsg("WHERE clause not allowed for except table"),
+							parser_errposition(pubobj->location));
+
+				/* Column list is not allowed on a except table */
+				if (pubobj->pubtable && pubobj->pubtable->columns)
+					ereport(ERROR,
+							errcode(ERRCODE_SYNTAX_ERROR),
+							errmsg("column specification not allowed for except table"),
+							parser_errposition(pubobj->location));
+			}
 		}
 		else if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_SCHEMA ||
 				 pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA)
@@ -19962,6 +20004,8 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
 
 		prevobjtype = pubobj->pubobjtype;
 	}
+
+	return foundexcepttable;
 }
 
 /*----------
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 39404ea0f69..71efb3c4082 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2317,10 +2317,14 @@ 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");
+		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("(", "TABLES IN SCHEMA", "TABLE");
+		COMPLETE_WITH("(", "EXCEPT", "TABLES IN SCHEMA", "TABLE");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "EXCEPT"))
+		COMPLETE_WITH("TABLE");		
 	else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|DROP|SET", "TABLES", "IN", "SCHEMA"))
 		COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_schemas
 								 " AND nspname NOT LIKE E'pg\\\\_%%'",
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index a70fac988a1..1a85f5165e6 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -126,6 +126,17 @@ ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
 Tables from schemas:
     "pub_test"
 
+-- 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/DROP clause
+-- fail - can't drop an EXCEPT TABLE from 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable DROP EXCEPT TABLE testpub_tbl1;
+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
+DETAIL:  EXCEPT Tables cannot be added to or dropped from non FOR ALL TABLES publications.
 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
@@ -213,6 +224,17 @@ Not-null constraints:
  regress_publication_user | t          | f             | t       | t       | f       | f         | none              | f
 (1 row)
 
+-- 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/DROP clause
+-- fail - can't drop an EXCEPT TABLE from schema publication
+ALTER PUBLICATION testpub_forschema DROP EXCEPT TABLE pub_test.testpub_nopk;;
+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
+DETAIL:  EXCEPT Tables cannot be added to or dropped from non FOR ALL TABLES publications.
 ---------------------------------------------
 -- EXCEPT TABLE tests for normal tables
 ---------------------------------------------
@@ -255,6 +277,29 @@ Except Publications:
     "testpub_foralltables_excepttable"
     "testpub_foralltables_excepttable1"
 
+-- 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
+                                          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
+Except tables:
+    "public.testpub_tbl2"
+
+-- Replace the publication EXCEPT table list with a specific EXCEPT table.
+ALTER PUBLICATION testpub_foralltables_excepttable SET EXCEPT TABLE testpub_tbl1;
+\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
+Except tables:
+    "public.testpub_tbl1"
+
+-- 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/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 31b9b7d4c54..e7df769a96d 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -74,6 +74,13 @@ ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test;
 ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
 \dRp+ testpub_fortable
 
+-- fail - can't add an EXCEPT TABLE to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable ADD EXCEPT TABLE testpub_tbl1;
+-- fail - can't drop an EXCEPT TABLE from 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable DROP EXCEPT TABLE testpub_tbl1;
+-- fail - can't set an EXCEPT TABLE to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable SET EXCEPT TABLE testpub_tbl1;
+
 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
@@ -105,6 +112,13 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
 \d+ testpub_tbl2
 \dRp+ testpub_foralltables
 
+-- fail - can't add an EXCEPT TABLE to schema publication
+ALTER PUBLICATION testpub_forschema ADD EXCEPT TABLE pub_test.testpub_nopk;;
+-- fail - can't drop an EXCEPT TABLE from schema publication
+ALTER PUBLICATION testpub_forschema DROP EXCEPT TABLE pub_test.testpub_nopk;;
+-- fail - can't set an EXCEPT TABLE to schema publication
+ALTER PUBLICATION testpub_forschema SET EXCEPT TABLE pub_test.testpub_nopk;;
+
 ---------------------------------------------
 -- EXCEPT TABLE tests for normal tables
 ---------------------------------------------
@@ -120,6 +134,17 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (test
 -- in the EXCEPT clause
 \d testpub_tbl1
 
+-- 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
+
+-- Replace the publication EXCEPT table list with a specific EXCEPT table.
+ALTER PUBLICATION testpub_foralltables_excepttable SET 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;
+
 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/subscription/t/037_rep_changes_except_table.pl b/src/test/subscription/t/037_rep_changes_except_table.pl
index a9d1751d054..47be2419fe6 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