v62-0001-Add-support-for-EXCEPT-TABLE-in-ALTER-PUBLICATIO.patch

From 95f6eb56ec3b537498c22bdf9009c871d98e6a52 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Wed, 4 Mar 2026 16:28:32 +0530
Subject: [PATCH v62] Add support for EXCEPT TABLE in ALTER PUBLICATION.

Following commit fd366065e0, which added EXCEPT TABLE support to
CREATE PUBLICATION, this commit extends ALTER PUBLICATION to allow
modifying the exclusion list.

New Syntax:
ALTER PUBLICATION name SET ALL TABLES EXCEPT TABLE (table_list)
ALTER PUBLICATION name SET ALL TABLES

The first form replaces the current EXCEPT TABLE list with the specified
tables. The second form clears the existing except table list.  Like the
creation syntax, only root partitioned tables can be specified in the
exclusion list.

Author: vignesh C <vignesh21@gmail.com>
Reviewed-by: shveta malik <shveta.malik@gmail.com>
Reviewed-by: Amit Kapila <amit.kapila16@gmail.com>
Reviewed-by: Nisha Moond <nisha.moond412@gmail.com>
Discussion: https://postgr.es/m/CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com
---
 doc/src/sgml/ref/alter_publication.sgml   | 45 +++++++++++++++++-----
 src/backend/catalog/pg_publication.c      | 10 +++--
 src/backend/commands/publicationcmds.c    | 42 +++++++++++++++-----
 src/backend/commands/tablecmds.c          |  3 +-
 src/backend/parser/gram.y                 | 16 ++++++++
 src/bin/psql/tab-complete.in.c            | 10 ++++-
 src/include/catalog/pg_publication.h      |  3 +-
 src/include/nodes/parsenodes.h            |  1 +
 src/test/regress/expected/publication.out | 47 ++++++++++++++++++++++-
 src/test/regress/sql/publication.sql      | 28 +++++++++++++-
 src/test/subscription/t/037_except.pl     | 36 ++++++++++++++++-
 11 files changed, 212 insertions(+), 29 deletions(-)

diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 028770f2149..c5e3dd3be77 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -22,6 +22,7 @@ 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 ALL TABLES <replaceable class="parameter">publication_except_tables</replaceable>
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_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>] [, ... ] )
@@ -33,6 +34,10 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
     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_except_tables</replaceable> is:</phrase>
+
+    [ EXCEPT TABLE ( [ ONLY ] <replaceable class="parameter">table_name</replaceable> [, ... ] ) ]
+
 <phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
 
     TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
@@ -53,14 +58,22 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
   </para>
 
   <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
+   The first four variants modify which tables/schemas are included in the
+   publication, or which tables are excluded from it.  The
+   <literal>SET ALL TABLES</literal> clause is used to update the
+   <literal>EXCEPT TABLE</literal> list of a <literal>FOR ALL TABLES</literal>
+   publication.  If <literal>EXCEPT TABLE</literal> is specified with a list of
+   tables, the existing except table list is replaced with the specified tables.
+   If <literal>EXCEPT TABLE</literal> is omitted, the existing except table
+   list is cleared. The <literal>SET</literal> clause, when used with a
+   publication defined with <literal>FOR TABLE</literal> or
+   <literal>FOR TABLES IN SCHEMA</literal>, replaces the list of tables/schemas
+   in the publication with the specified list; the existing tables or 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
-   <link linkend="sql-altersubscription-params-refresh-publication">
+   tables/except tables/schemas 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
    <literal>DROP TABLES IN SCHEMA</literal> will not drop any schema tables
@@ -70,7 +83,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
   </para>
 
   <para>
-   The fourth variant of this command listed in the synopsis can change
+   The fifth variant of this command listed in the synopsis can change
    all of the publication properties specified in
    <xref linkend="sql-createpublication"/>.  Properties not mentioned in the
    command retain their previous settings.
@@ -83,8 +96,9 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
   <para>
    You must own the publication to use <command>ALTER PUBLICATION</command>.
    Adding a table to a publication additionally requires owning that table.
-   The <literal>ADD TABLES IN SCHEMA</literal> and
-   <literal>SET TABLES IN SCHEMA</literal> to a publication requires the
+   The <literal>ADD TABLES IN SCHEMA</literal>,
+   <literal>SET TABLES IN SCHEMA</literal>, and
+   <literal>SET ALL TABLES</literal> to a publication requires the
    invoking user to be a superuser.
    To alter the owner, you must be able to <literal>SET ROLE</literal> to the
    new owning role, and that role must have <literal>CREATE</literal>
@@ -222,6 +236,19 @@ ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname), department
    Change the set of columns published for a table:
 <programlisting>
 ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname), TABLE departments;
+</programlisting></para>
+
+  <para>
+   Replace the publication's EXCEPT table list:
+<programlisting>
+ALTER PUBLICATION mypublication SET ALL TABLES EXCEPT TABLE (users, departments);
+</programlisting></para>
+
+  <para>
+   Change the publication to include all tables by removing any existing
+   EXCEPT table list:
+<programlisting>
+ALTER PUBLICATION mypublication SET ALL TABLES;
 </programlisting></para>
 
   <para>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index aadc7c202c6..facccbe74ea 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -440,7 +440,7 @@ attnumstoint2vector(Bitmapset *attrs)
  */
 ObjectAddress
 publication_add_relation(Oid pubid, PublicationRelInfo *pri,
-						 bool if_not_exists)
+						 bool if_not_exists, bool is_alter)
 {
 	Relation	rel;
 	HeapTuple	tup;
@@ -544,10 +544,12 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
 
 	/*
 	 * Relations excluded via the EXCEPT clause do not need explicit
-	 * invalidation as CreatePublication() function invalidates all relations
-	 * as part of defining a FOR ALL TABLES publication.
+	 * invalidation during CREATE PUBLICATION, as CreatePublication() function
+	 * invalidates all relations as part of defining a FOR ALL TABLES
+	 * publication. For ALTER PUBLICATION, explicit invalidation is still
+	 * required.
 	 */
-	if (!pri->except)
+	if (!pri->except || is_alter)
 	{
 		/*
 		 * Invalidate relcache so that publication info is rebuilt.
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 6a3ca4751fa..7944c957033 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1272,15 +1272,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
@@ -1498,6 +1507,11 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
 				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
 				 errmsg("must be superuser to add or set schemas")));
 
+	if (stmt->for_all_tables && !superuser())
+		ereport(ERROR,
+				errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				errmsg("must be superuser to alter FOR ALL TABLES publication"));
+
 	/*
 	 * Check that user is allowed to manipulate the publication tables in
 	 * schema
@@ -1546,6 +1560,17 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
 						   NameStr(pubform->pubname)),
 					errdetail("Tables or sequences cannot be added to or dropped from FOR ALL SEQUENCES publications."));
 	}
+
+	/*
+	 * SET ALL TABLES is allowed only for publications defined as FOR ALL
+	 * TABLES.
+	 */
+	if (stmt->for_all_tables && !pubform->puballtables)
+		ereport(ERROR,
+				errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+				errmsg("publication \"%s\" does not support ALL TABLES operations",
+					   NameStr(pubform->pubname)),
+				errdetail("This operation requires the publication to have been defined as FOR ALL TABLES."));
 }
 
 /*
@@ -1591,9 +1616,6 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
 		ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
 								   &exceptrelations, &schemaidlist);
 
-		/* EXCEPT clause is not supported with ALTER PUBLICATION */
-		Assert(exceptrelations == NIL);
-
 		CheckAlterPublication(stmt, tup, relations, schemaidlist);
 
 		heap_freetuple(tup);
@@ -1615,6 +1637,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);
@@ -1953,7 +1976,8 @@ PublicationAddTables(Oid pubid, List *rels, bool if_not_exists,
 			aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(rel->rd_rel->relkind),
 						   RelationGetRelationName(rel));
 
-		obj = publication_add_relation(pubid, pub_rel, if_not_exists);
+		obj = publication_add_relation(pubid, pub_rel, if_not_exists,
+									   (stmt != NULL));
 		if (stmt)
 		{
 			EventTriggerCollectSimpleCommand(obj, InvalidObjectAddress,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 85242dcc245..ec758ad5db3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20400,7 +20400,8 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
 							  list_length(exceptpuboids),
 							  RelationGetRelationName(attachrel),
 							  pubnames.data),
-				errdetail("The publication EXCEPT clause cannot contain tables that are partitions."));
+				errdetail("The publication EXCEPT clause cannot contain tables that are partitions."),
+				errhint("Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET ALL TABLES."));
 	}
 
 	list_free(exceptpuboids);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f01f5734fe9..aa997f449b3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10999,6 +10999,8 @@ pub_except_obj_list: PublicationExceptObjSpec
  *
  * ALTER PUBLICATION name SET pub_obj [, ...]
  *
+ * ALTER PUBLICATION name SET ALL TABLES [ EXCEPT pub_obj [, ...] ]
+ *
  * pub_obj is one of:
  *
  *		TABLE table_name [, ...]
@@ -11013,6 +11015,7 @@ AlterPublicationStmt:
 
 					n->pubname = $3;
 					n->options = $5;
+					n->for_all_tables = false;
 					$$ = (Node *) n;
 				}
 			| ALTER PUBLICATION name ADD_P pub_obj_list
@@ -11023,6 +11026,7 @@ AlterPublicationStmt:
 					n->pubobjects = $5;
 					preprocess_pubobj_list(n->pubobjects, yyscanner);
 					n->action = AP_AddObjects;
+					n->for_all_tables = false;
 					$$ = (Node *) n;
 				}
 			| ALTER PUBLICATION name SET pub_obj_list
@@ -11033,6 +11037,17 @@ AlterPublicationStmt:
 					n->pubobjects = $5;
 					preprocess_pubobj_list(n->pubobjects, yyscanner);
 					n->action = AP_SetObjects;
+					n->for_all_tables = false;
+					$$ = (Node *) n;
+				}
+			| ALTER PUBLICATION name SET ALL TABLES opt_pub_except_clause
+				{
+					AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
+
+					n->pubname = $3;
+					n->pubobjects = $7;
+					n->action = AP_SetObjects;
+					n->for_all_tables = true;
 					$$ = (Node *) n;
 				}
 			| ALTER PUBLICATION name DROP pub_obj_list
@@ -11043,6 +11058,7 @@ AlterPublicationStmt:
 					n->pubobjects = $5;
 					preprocess_pubobj_list(n->pubobjects, yyscanner);
 					n->action = AP_DropObjects;
+					n->for_all_tables = false;
 					$$ = (Node *) n;
 				}
 		;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 199fc64ddf5..10a6b8f4284 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2320,7 +2320,15 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH("TABLES IN SCHEMA", "TABLE");
 	/* ALTER PUBLICATION <name> SET */
 	else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET"))
-		COMPLETE_WITH("(", "TABLES IN SCHEMA", "TABLE");
+		COMPLETE_WITH("(", "ALL TABLES", "TABLES IN SCHEMA", "TABLE");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL"))
+		COMPLETE_WITH("TABLES");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES"))
+		COMPLETE_WITH("EXCEPT TABLE (");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES", "EXCEPT"))
+		COMPLETE_WITH("TABLE (");
+	else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "ALL", "TABLES", "EXCEPT", "TABLE"))
+		COMPLETE_WITH("(");
 	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/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index e25228713e7..f40c0fe2f95 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -198,7 +198,8 @@ extern bool is_schema_publication(Oid pubid);
 extern bool check_and_fetch_column_list(Publication *pub, Oid relid,
 										MemoryContext mcxt, Bitmapset **cols);
 extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri,
-											  bool if_not_exists);
+											  bool if_not_exists,
+											  bool is_alter);
 extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns);
 extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid,
 											bool if_not_exists);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f3d32ef0188..b0f5c603bd0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4386,6 +4386,7 @@ typedef struct AlterPublicationStmt
 	List	   *pubobjects;		/* Optional list of publication objects */
 	AlterPublicationAction action;	/* What action to perform with the given
 									 * objects */
+	bool		for_all_tables; /* True if SET ALL TABLES is specified */
 } AlterPublicationStmt;
 
 typedef struct CreateSubscriptionStmt
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 681d2564ed5..97a5288a728 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -126,6 +126,13 @@ ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
 Tables from schemas:
     "pub_test"
 
+-- fail - SET ALL TABLES is not allowed for a 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+ERROR:  publication "testpub_fortable" does not support ALL TABLES operations
+DETAIL:  This operation requires the publication to have been defined as FOR ALL TABLES.
+ALTER PUBLICATION testpub_fortable SET ALL TABLES;
+ERROR:  publication "testpub_fortable" does not support ALL TABLES operations
+DETAIL:  This operation requires the publication to have been 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
@@ -213,6 +220,13 @@ Not-null constraints:
  regress_publication_user | t          | f             | t       | t       | f       | f         | none              | f        | 
 (1 row)
 
+-- fail - SET ALL TABLES is not allowed for a schema publication
+ALTER PUBLICATION testpub_forschema SET ALL TABLES EXCEPT TABLE (pub_test.testpub_nopk);
+ERROR:  publication "testpub_forschema" does not support ALL TABLES operations
+DETAIL:  This operation requires the publication to have been defined as FOR ALL TABLES.
+ALTER PUBLICATION testpub_forschema SET ALL TABLES;
+ERROR:  publication "testpub_forschema" does not support ALL TABLES operations
+DETAIL:  This operation requires the publication to have been defined as FOR ALL TABLES.
 ---------------------------------------------
 -- EXCEPT TABLE tests for normal tables
 ---------------------------------------------
@@ -254,6 +268,25 @@ Except Publications:
     "testpub_foralltables_excepttable"
     "testpub_foralltables_excepttable1"
 
+-- Remove all the EXCEPT tables.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES;
+\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)
+
+-- Replace the publication EXCEPT table list with a specific EXCEPT table.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES 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 | Description 
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
+ regress_publication_user | t          | f             | t       | t       | t       | t         | none              | f        | 
+Except tables:
+    "public.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;
@@ -361,6 +394,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 ALL TABLES.
 RESET client_min_messages;
 DROP TABLE testpub_root, testpub_part1, tab_main;
 DROP PUBLICATION testpub8;
@@ -1541,7 +1575,18 @@ ERROR:  permission denied to change owner of publication "testpub4"
 HINT:  The owner of a FOR ALL TABLES or ALL SEQUENCES or TABLES IN SCHEMA publication must be a superuser.
 ALTER PUBLICATION testpub4 owner to regress_publication_user; -- ok
 SET ROLE regress_publication_user;
-DROP PUBLICATION testpub4;
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub5 FOR ALL TABLES;
+RESET client_min_messages;
+ALTER PUBLICATION testpub5 OWNER TO regress_publication_user3;
+SET ROLE regress_publication_user3;
+-- fail - SET ALL TABLES on a publication requires superuser privileges
+ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT TABLE (testpub_tbl1); -- fail
+ERROR:  must be superuser to alter FOR ALL TABLES publication
+ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail
+ERROR:  must be superuser to alter FOR ALL TABLES publication
+SET ROLE regress_publication_user;
+DROP PUBLICATION testpub4, testpub5;
 DROP ROLE regress_publication_user3;
 REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
 DROP TABLE testpub_parted;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 405579dad52..56e4bfba974 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -74,6 +74,10 @@ ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test;
 ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
 \dRp+ testpub_fortable
 
+-- fail - SET ALL TABLES is not allowed for a 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+ALTER PUBLICATION testpub_fortable SET 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
@@ -105,6 +109,10 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
 \d+ testpub_tbl2
 \dRp+ testpub_foralltables
 
+-- fail - SET ALL TABLES is not allowed for a schema publication
+ALTER PUBLICATION testpub_forschema SET ALL TABLES EXCEPT TABLE (pub_test.testpub_nopk);
+ALTER PUBLICATION testpub_forschema SET ALL TABLES;
+
 ---------------------------------------------
 -- EXCEPT TABLE tests for normal tables
 ---------------------------------------------
@@ -119,6 +127,14 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE
 -- in the EXCEPT TABLE clause
 \d testpub_tbl1
 
+-- Remove all the EXCEPT tables.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES;
+\dRp+ testpub_foralltables_excepttable
+
+-- Replace the publication EXCEPT table list with a specific EXCEPT table.
+ALTER PUBLICATION testpub_foralltables_excepttable SET ALL TABLES EXCEPT TABLE (testpub_tbl1);
+\dRp+ testpub_foralltables_excepttable
+
 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;
@@ -991,7 +1007,17 @@ ALTER PUBLICATION testpub4 owner to regress_publication_user2; -- fail
 ALTER PUBLICATION testpub4 owner to regress_publication_user; -- ok
 
 SET ROLE regress_publication_user;
-DROP PUBLICATION testpub4;
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub5 FOR ALL TABLES;
+RESET client_min_messages;
+ALTER PUBLICATION testpub5 OWNER TO regress_publication_user3;
+SET ROLE regress_publication_user3;
+-- fail - SET ALL TABLES on a publication requires superuser privileges
+ALTER PUBLICATION testpub5 SET ALL TABLES EXCEPT TABLE (testpub_tbl1); -- fail
+ALTER PUBLICATION testpub5 SET ALL TABLES; -- fail
+
+SET ROLE regress_publication_user;
+DROP PUBLICATION testpub4, testpub5;
 DROP ROLE regress_publication_user3;
 
 REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
diff --git a/src/test/subscription/t/037_except.pl b/src/test/subscription/t/037_except.pl
index 2729df4d5c0..cbfe42011ef 100644
--- a/src/test/subscription/t/037_except.pl
+++ b/src/test/subscription/t/037_except.pl
@@ -152,18 +152,50 @@ $result =
   $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM child1");
 is($result, qq(10), 'check replicated inserts on subscriber');
 
+$node_publisher->safe_psql('postgres',
+	"CREATE TABLE tab2 AS SELECT generate_series(1,10) AS a");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE tab2 (a int)");
+
+# Replace the EXCEPT TABLE list so that only tab2 is excluded.
+$node_publisher->safe_psql('postgres',
+	"ALTER PUBLICATION tab_pub SET ALL TABLES 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 initial table synchronization does not occur for tables
+# listed in the EXCEPT TABLE clause.
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab2");
+is($result, qq(0),
+	'check there is no initial data copied for the tables specified in the EXCEPT TABLE clause'
+);
+
+# Verify that table synchronization occurs once tab1 is removed from the
+# EXCEPT TABLE clause via SET ALL TABLES EXCEPT TABLE.
+$result =
+  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab1");
+is($result, qq(20),
+	'check that the data is copied as the tab1 is removed from EXCEPT TABLE clause'
+);
+
 # cleanup
 $node_subscriber->safe_psql(
 	'postgres', qq(
 	DROP SUBSCRIPTION tab_sub;
 	TRUNCATE TABLE tab1;
-	DROP TABLE parent, parent1, child, child1;
+	DROP TABLE parent, parent1, child, child1, tab2;
 ));
 $node_publisher->safe_psql(
 	'postgres', qq(
 	DROP PUBLICATION tab_pub;
 	TRUNCATE TABLE tab1;
-    DROP TABLE parent, parent1, child, child1;
+    DROP TABLE parent, parent1, child, child1, tab2;
 ));
 
 # ============================================
-- 
2.43.0