postgres.git / summary / log / commit / refs
commit 7056ed4663f5ce4214cc1ebce42ec40f4400a7ea
Author: Amit Kapila <akapila@postgresql.org>
Date: Mon Sep 14 04:10:18 2026 +0000
Disallow SET UNLOGGED for tables in a publication's EXCEPT clause.
Unlogged tables cannot be replicated, so they can neither be published nor
be named in a publication's EXCEPT clause. ALTER TABLE ... SET UNLOGGED
checked only whether the table was published, so a table in an EXCEPT
clause could still be made unlogged, leaving a state that CREATE
PUBLICATION would reject and that pg_dump could not restore.
Author: Vignesh C <vignesh21@gmail.com>
Reviewed-by: Amit Kapila <amit.kapila16@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: shveta malik <shveta.malik@gmail.com>
Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Reviewed-by: Peter Smith <smithpb2250@gmail.com>
Reviewed-by: Zhijie Hou <houzj.fnst@fujitsu.com>
Discussion: https://postgr.es/m/CALDaNm1r2MkGu6h8zgU1Kj1sX-FcMQ7wGTeLSnhx-5joiyXEvg@mail.gmail.com
Backpatch-through: 19, where it was introduced
src/backend/catalog/pg_publication.c | 19 +++++++++++++++++++
src/backend/commands/tablecmds.c | 13 +++++++------
src/include/catalog/pg_publication.h | 1 +
src/test/regress/expected/publication.out | 10 ++++++++++
src/test/regress/sql/publication.sql | 8 ++++++++
5 files changed, 45 insertions(+), 6 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 26c86f667b9..6b752c4c738 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -937,6 +937,25 @@ GetRelationExcludedPublications(Oid relid)
return get_relation_publications(relid, true);
}
+/*
+ * Check whether the relation is referenced by any publication, either as a
+ * published relation or in a publication's EXCEPT clause.
+ */
+bool
+RelationHasPublication(Oid relid)
+{
+ CatCList *pubrellist;
+ bool found;
+
+ pubrellist = SearchSysCacheList1(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(relid));
+ found = (pubrellist->n_members > 0);
+
+ ReleaseSysCacheList(pubrellist);
+
+ return found;
+}
+
/*
* Internal function to get the list of relation oids for a publication.
*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8dc70bfa0f1..2f073ddb84a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -19541,16 +19541,17 @@ 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.
+ * UNLOGGED tables can neither be published nor be named in a
+ * publication's EXCEPT clause, so reject the change if the table is
+ * referenced by any publication.
*/
- if (!toLogged &&
- GetRelationIncludedPublications(RelationGetRelid(rel)) != NIL)
+ if (!toLogged && RelationHasPublication(RelationGetRelid(rel)))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
- errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
+ errmsg("cannot change table \"%s\" to unlogged because it is referenced by a publication",
RelationGetRelationName(rel)),
- errdetail("Unlogged relations cannot be replicated.")));
+ errdetail("Unlogged relations cannot be published or excluded via an EXCEPT clause."),
+ errhint("Drop the table from the publication, or remove it from the publication's EXCEPT clause, first.")));
/*
* Check existing foreign key constraints to preserve the invariant that
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 89b4bb14f62..5d1e6c54a85 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -157,6 +157,7 @@ extern Publication *GetPublication(Oid pubid);
extern Publication *GetPublicationByName(const char *pubname, bool missing_ok);
extern List *GetRelationIncludedPublications(Oid relid);
extern List *GetRelationExcludedPublications(Oid relid);
+extern bool RelationHasPublication(Oid relid);
/*---------
* Expected values for pub_partopt parameter of
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 4f21462cc17..fe602c4538e 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -279,6 +279,16 @@ CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (test
ERROR: syntax error at or near "testpub_tbl1"
LINE 1: ..._foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tb...
^
+-- A table in an EXCEPT clause cannot be changed to UNLOGGED.
+CREATE TABLE testpub_exc_unlogged_tbl (a int);
+CREATE PUBLICATION testpub_exc_unlogged FOR ALL TABLES EXCEPT (TABLE testpub_exc_unlogged_tbl);
+-- fail - the table is referenced in a publication EXCEPT clause
+ALTER TABLE testpub_exc_unlogged_tbl SET UNLOGGED;
+ERROR: cannot change table "testpub_exc_unlogged_tbl" to unlogged because it is referenced by a publication
+DETAIL: Unlogged relations cannot be published or excluded via an EXCEPT clause.
+HINT: Drop the table from the publication, or remove it from the publication's EXCEPT clause, first.
+DROP PUBLICATION testpub_exc_unlogged;
+DROP TABLE testpub_exc_unlogged_tbl;
---------------------------------------------
-- SET ALL TABLES/SEQUENCES
---------------------------------------------
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index fac54b02e27..80c244c9138 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -126,6 +126,14 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (TABL
-- fail - first table in the EXCEPT list should use TABLE keyword
CREATE PUBLICATION testpub_foralltables_excepttable2 FOR ALL TABLES EXCEPT (testpub_tbl1, testpub_tbl2);
+-- A table in an EXCEPT clause cannot be changed to UNLOGGED.
+CREATE TABLE testpub_exc_unlogged_tbl (a int);
+CREATE PUBLICATION testpub_exc_unlogged FOR ALL TABLES EXCEPT (TABLE testpub_exc_unlogged_tbl);
+-- fail - the table is referenced in a publication EXCEPT clause
+ALTER TABLE testpub_exc_unlogged_tbl SET UNLOGGED;
+DROP PUBLICATION testpub_exc_unlogged;
+DROP TABLE testpub_exc_unlogged_tbl;
+
---------------------------------------------
-- SET ALL TABLES/SEQUENCES
---------------------------------------------