v1-0001-Handle-EXCEPT-publications-when-changing-table-to.patch

From 8c9b04f003d4989e09149efd3c875edc31a3a5bd Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Mon, 20 Apr 2026 15:02:31 +0530
Subject: [PATCH v1] Handle EXCEPT publications when changing table to UNLOGGED

When changing a table to UNLOGGED, tables that appear in publications
only via EXCEPT clauses (prexcept = true) were allowed, but left stale
entries in pg_publication_rel.

Since UNLOGGED tables are not supported in publications, remove such
entries when the table is changed to UNLOGGED, and emit a NOTICE to
inform the user.
---
 src/backend/commands/tablecmds.c          | 46 +++++++++++++++++++----
 src/test/regress/expected/publication.out | 26 +++++++++++++
 src/test/regress/sql/publication.sql      | 16 ++++++++
 3 files changed, 81 insertions(+), 7 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eec09ba1ded..3cb4b51489a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -98,6 +98,7 @@
 #include "tcop/utility.h"
 #include "utils/acl.h"
 #include "utils/builtins.h"
+#include "utils/catcache.h"
 #include "utils/fmgroids.h"
 #include "utils/inval.h"
 #include "utils/lsyscache.h"
@@ -19093,13 +19094,44 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
 	 * Check that the table is not part of any publication when changing to
 	 * UNLOGGED, as UNLOGGED tables can't be published.
 	 */
-	if (!toLogged &&
-		GetRelationIncludedPublications(RelationGetRelid(rel)) != NIL)
-		ereport(ERROR,
-				(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
-				 errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
-						RelationGetRelationName(rel)),
-				 errdetail("Unlogged relations cannot be replicated.")));
+	if (!toLogged)
+	{
+		CatCList   *pubrellist;
+
+		/* Find all publications associated with the relation. */
+		pubrellist = SearchSysCacheList1(PUBLICATIONRELMAP,
+										 ObjectIdGetDatum(RelationGetRelid(rel)));
+		for (int i = 0; i < pubrellist->n_members; i++)
+		{
+			HeapTuple	tup = &pubrellist->members[i]->tuple;
+			Form_pg_publication_rel pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
+
+			if (!pubrel->prexcept)
+			{
+				ereport(ERROR,
+						(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+						 errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
+								RelationGetRelationName(rel)),
+						 errdetail("Unlogged relations cannot be replicated.")));
+			}
+			else
+			{
+				ObjectAddress obj;
+				char	   *pubname;
+
+				pubname = get_publication_name(pubrel->prpubid, false);
+
+				ObjectAddressSet(obj, PublicationRelRelationId, pubrel->oid);
+				performDeletion(&obj, DROP_CASCADE, 0);
+
+				ereport(NOTICE,
+						errmsg("relation \"%s\" removed from publication \"%s\" due to being changed to UNLOGGED",
+							   RelationGetRelationName(rel), pubname));
+			}
+		}
+
+		ReleaseSysCacheList(pubrellist);
+	}
 
 	/*
 	 * Check existing foreign key constraints to preserve the invariant that
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index d028e9be866..dec7f5b24fb 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -256,6 +256,32 @@ Except publications:
     "testpub_foralltables_excepttable"
     "testpub_foralltables_excepttable1"
 
+SET client_min_messages = 'NOTICE';
+-- Change testpub_tbl1 to UNLOGGED. This should remove the relation from the
+-- publication (and effectively from the EXCEPT clause), since UNLOGGED tables
+-- are not supported in publications.
+ALTER TABLE testpub_tbl1 SET UNLOGGED;
+NOTICE:  relation "testpub_tbl1" removed from publication "testpub_foralltables_excepttable" due to being changed to UNLOGGED
+NOTICE:  relation "testpub_tbl1" removed from publication "testpub_foralltables_excepttable1" due to being changed to UNLOGGED
+\dRp+ testpub_foralltables_excepttable1
+                                                Publication testpub_foralltables_excepttable1
+          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)
+
+\d testpub_tbl1
+                        Unlogged table "public.testpub_tbl1"
+ Column |  Type   | Collation | Nullable |                 Default                  
+--------+---------+-----------+----------+------------------------------------------
+ id     | integer |           | not null | nextval('testpub_tbl1_id_seq'::regclass)
+ data   | text    |           |          | 
+Indexes:
+    "testpub_tbl1_pkey" PRIMARY KEY, btree (id)
+
+-- Restore the table to LOGGED.
+ALTER TABLE testpub_tbl1 SET LOGGED;
+SET client_min_messages = 'ERROR';
 -- fail - first table in the EXCEPT list should use TABLE keyword
 CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2);
 ERROR:  syntax error at or near "testpub_tbl1"
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 642e32fa098..3c8b5a3a980 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -119,6 +119,22 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (TABL
 -- Check that the table description shows the publications where it is listed
 -- in the EXCEPT clause
 \d testpub_tbl1
+
+SET client_min_messages = 'NOTICE';
+
+-- Change testpub_tbl1 to UNLOGGED. This should remove the relation from the
+-- publication (and effectively from the EXCEPT clause), since UNLOGGED tables
+-- are not supported in publications.
+ALTER TABLE testpub_tbl1 SET UNLOGGED;
+\dRp+ testpub_foralltables_excepttable1
+\d testpub_tbl1
+
+-- Restore the table to LOGGED.
+ALTER TABLE testpub_tbl1 SET LOGGED;
+
+SET client_min_messages = 'ERROR';
+
+
 -- fail - first table in the EXCEPT list should use TABLE keyword
 CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2);
 
-- 
2.43.0