Thanks for the review!

> 1. For 003, a SET LOGGED that fails the foreign key check rewrites
> the table first. I think it will create too much overhead.

Good catch, I only thought about the cases that should fail and I
didn't notice the WAL overhead.

v4 checks the foreign keys at the start of phase 3 too, before any
rewrite. Phase 2 already added the keys of the same command at that
point, so this also covers SET LOGGED, ADD FOREIGN KEY. The check at
the end remains for event
triggers and USING expressions, we need both.

There's also a new regression test which verifies that no
table_rewrite event fires in this case. I am not sure if we want to
keep that, but for the review now I think it's better to have more
tests than less.

> 2. For 004, this seems already captured in a different bug[1], I think the
> current patch is to reject access to the table from the trigger.
>
> If that goes in, would 0004 still be needed?

No, if that gets committed, we don't need 0004. For now I kept it
here, but if that gets committed, I'll drop it, and also the previous
patches can be committed without it.
From 6b98d2a4de3dc92ca8669b75631579d3f5a39445 Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Wed, 11 Nov 2026 22:18:20 +0000
Subject: [PATCH REL_17 v4 1/3] Check partition persistence on both sides of
 foreign keys

The foreign key persistence checks are applied only to the relations
named in the constraint.  Unlogged partitioned tables may have
permanent partitions and vice versa, and neither the recursion when
the constraint is created, nor the cloning performed when a partition
is created or attached, nor SET LOGGED/UNLOGGED looks at the
partitions.  A permanent partition could therefore reference rows in
an unlogged table or an unlogged partition, for example:

    CREATE UNLOGGED TABLE pk (a int PRIMARY KEY);
    CREATE UNLOGGED TABLE fk (x int) PARTITION BY RANGE (x);
    CREATE TABLE fk1 PARTITION OF fk FOR VALUES FROM (0) TO (100);
    ALTER TABLE fk ADD FOREIGN KEY (x) REFERENCES pk;

After a crash, recovery resets the unlogged relation to its init fork:
the referenced rows vanish while the referencing rows remain, leaving
the constraint violated, and a dump of the database can no longer be
restored.

Fix this by rejecting any foreign key that connects a permanent
relation with storage in the referencing partition tree with an
unlogged relation with storage in the referenced partition tree, when
the constraint is created, when it is cloned for a new partition on
either side, and when a partition changes persistence.  The
persistence of partitioned tables themselves is not considered here,
as they hold no data; the existing checks on the named relations are
kept as they are.
---
 src/backend/commands/tablecmds.c          | 101 +++++++++++++++++++++-
 src/test/regress/expected/foreign_key.out | 101 ++++++++++++++++++++++
 src/test/regress/sql/foreign_key.sql      |  74 ++++++++++++++++
 3 files changed, 274 insertions(+), 2 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 982d98d43fa..88c0402c847 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -556,6 +556,9 @@ static void addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint,
 static void CloneForeignKeyConstraints(List **wqueue, Relation parentRel,
 									   Relation partitionRel);
 static void CloneFkReferenced(Relation parentRel, Relation partitionRel);
+static Oid	FindStoredRelWithPersistence(Oid relid, char persistence,
+										 Oid skipRelid);
+static void CheckFkPersistenceTrees(Oid fkrelid, Oid pkrelid);
 static void CloneFkReferencing(List **wqueue, Relation parentRel,
 							   Relation partRel);
 static void createForeignKeyCheckTriggers(Oid myRelOid, Oid refRelOid,
@@ -10338,6 +10341,13 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 			break;
 	}
 
+	/*
+	 * The checks above only look at the named relations.  If either side is
+	 * partitioned, its partitions can have a different persistence, so check
+	 * those too.
+	 */
+	CheckFkPersistenceTrees(RelationGetRelid(rel), RelationGetRelid(pkrel));
+
 	/*
 	 * Look up the referencing attributes to make sure they exist, and record
 	 * their attnums and type OIDs.
@@ -11251,6 +11261,69 @@ CloneForeignKeyConstraints(List **wqueue, Relation parentRel,
 	CloneFkReferenced(parentRel, partitionRel);
 }
 
+/*
+ * Return a relation with storage and the given persistence in the partition
+ * tree rooted at relid, other than skipRelid, or InvalidOid if there is none.
+ * If relid is not a partitioned table, only relid itself is considered.
+ *
+ * Partitioned tables are skipped: they hold no data, so their persistence
+ * does not matter for what is left after crash recovery.
+ */
+static Oid
+FindStoredRelWithPersistence(Oid relid, char persistence, Oid skipRelid)
+{
+	List	   *rels;
+	ListCell   *lc;
+
+	if (get_rel_relkind(relid) == RELKIND_PARTITIONED_TABLE)
+		rels = find_all_inheritors(relid, AccessShareLock, NULL);
+	else
+		rels = list_make1_oid(relid);
+
+	foreach(lc, rels)
+	{
+		Oid			childid = lfirst_oid(lc);
+
+		if (childid == skipRelid ||
+			!RELKIND_HAS_STORAGE(get_rel_relkind(childid)))
+			continue;
+		if (get_rel_persistence(childid) == persistence)
+			return childid;
+	}
+
+	return InvalidOid;
+}
+
+/*
+ * Check that a foreign key from fkrelid to pkrelid does not make a permanent
+ * table reference an unlogged one, taking all partitions of both sides into
+ * account.  An unlogged partitioned table may have permanent partitions and
+ * vice versa, so the persistence of the named relations is not enough.
+ */
+static void
+CheckFkPersistenceTrees(Oid fkrelid, Oid pkrelid)
+{
+	Oid			unloggedRelid;
+	Oid			permRelid;
+
+	unloggedRelid = FindStoredRelWithPersistence(pkrelid,
+												 RELPERSISTENCE_UNLOGGED,
+												 InvalidOid);
+	if (!OidIsValid(unloggedRelid))
+		return;
+
+	permRelid = FindStoredRelWithPersistence(fkrelid,
+											 RELPERSISTENCE_PERMANENT,
+											 InvalidOid);
+	if (OidIsValid(permRelid))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				 errmsg("constraints on permanent tables may reference only permanent tables"),
+				 errdetail("Permanent table \"%s\" would reference unlogged table \"%s\".",
+						   get_rel_name(permRelid),
+						   get_rel_name(unloggedRelid))));
+}
+
 /*
  * CloneFkReferenced
  *		Subroutine for CloneForeignKeyConstraints
@@ -11352,6 +11425,9 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
 		/* We need the same lock level that CreateTrigger will acquire */
 		fkRel = table_open(constrForm->conrelid, ShareRowExclusiveLock);
 
+		CheckFkPersistenceTrees(constrForm->conrelid,
+								RelationGetRelid(partitionRel));
+
 		indexOid = constrForm->conindid;
 		DeconstructFkConstraintRow(tuple,
 								   &numfks,
@@ -11568,6 +11644,9 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
 			(void) find_all_inheritors(RelationGetRelid(pkrel),
 									   ShareRowExclusiveLock, NULL);
 
+		CheckFkPersistenceTrees(RelationGetRelid(partRel),
+								constrForm->confrelid);
+
 		DeconstructFkConstraintRow(tuple, &numfks, conkey, confkey,
 								   conpfeqop, conppeqop, conffeqop,
 								   &numfkdelsetcols, confdelsetcols);
@@ -17867,6 +17946,7 @@ ATPrepChangePersistence(Relation rel, bool toLogged)
 		{
 			Oid			foreignrelid;
 			Relation	foreignrel;
+			Oid			otherrelid;
 
 			/* the opposite end of what we used as scankey */
 			foreignrelid = toLogged ? con->confrelid : con->conrelid;
@@ -17877,24 +17957,41 @@ ATPrepChangePersistence(Relation rel, bool toLogged)
 
 			foreignrel = relation_open(foreignrelid, AccessShareLock);
 
+			/*
+			 * If the relation has storage, also check the partitions on the
+			 * other side, as they can differ from their parent.  Skip the
+			 * relation itself, in case it is part of that partition tree.
+			 */
+			if (RELKIND_HAS_STORAGE(rel->rd_rel->relkind))
+				otherrelid =
+					FindStoredRelWithPersistence(foreignrelid,
+												 toLogged ? RELPERSISTENCE_UNLOGGED : RELPERSISTENCE_PERMANENT,
+												 RelationGetRelid(rel));
+			else
+				otherrelid = InvalidOid;
+
 			if (toLogged)
 			{
 				if (!RelationIsPermanent(foreignrel))
+					otherrelid = foreignrelid;
+				if (OidIsValid(otherrelid))
 					ereport(ERROR,
 							(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
 							 errmsg("could not change table \"%s\" to logged because it references unlogged table \"%s\"",
 									RelationGetRelationName(rel),
-									RelationGetRelationName(foreignrel)),
+									get_rel_name(otherrelid)),
 							 errtableconstraint(rel, NameStr(con->conname))));
 			}
 			else
 			{
 				if (RelationIsPermanent(foreignrel))
+					otherrelid = foreignrelid;
+				if (OidIsValid(otherrelid))
 					ereport(ERROR,
 							(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
 							 errmsg("could not change table \"%s\" to unlogged because it references logged table \"%s\"",
 									RelationGetRelationName(rel),
-									RelationGetRelationName(foreignrel)),
+									get_rel_name(otherrelid)),
 							 errtableconstraint(rel, NameStr(con->conname))));
 			}
 
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 835dbb13301..314155fd3c3 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -3310,3 +3310,104 @@ DROP CAST (fk_err_vch AS int);
 DROP FUNCTION fk_err_vcast(fk_err_vch);
 DROP TYPE fk_err_vch;
 DROP ROLE regress_fk_err_role;
+-- FK persistence rules apply to the partitions on both sides, not just to
+-- the named tables: unlogged partitioned tables can have permanent
+-- partitions and vice versa, and crash recovery resets every unlogged
+-- partition, leaving rows in permanent referencing partitions dangling.
+CREATE SCHEMA fkpart14;
+SET search_path TO fkpart14;
+-- referenced side
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+CREATE TABLE fk (x int REFERENCES pk);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk" would reference unlogged table "pk1".
+CREATE UNLOGGED TABLE ufk (x int REFERENCES pk);
+ALTER TABLE ufk SET LOGGED;	-- fails
+ERROR:  could not change table "ufk" to logged because it references unlogged table "pk1"
+DROP TABLE ufk, pk;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE fk (x int REFERENCES pk);
+CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk" would reference unlogged table "pk1".
+CREATE UNLOGGED TABLE pk2 (a int PRIMARY KEY);
+ALTER TABLE pk ATTACH PARTITION pk2 FOR VALUES FROM (0) TO (100);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk" would reference unlogged table "pk2".
+CREATE TABLE pk3 (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE pk31 PARTITION OF pk3 FOR VALUES FROM (0) TO (50);
+ALTER TABLE pk ATTACH PARTITION pk3 FOR VALUES FROM (0) TO (100);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk" would reference unlogged table "pk31".
+-- ok, only the persistence of partitions with storage matters
+CREATE UNLOGGED TABLE pk4 (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE pk41 PARTITION OF pk4 FOR VALUES FROM (0) TO (50);
+ALTER TABLE pk ATTACH PARTITION pk4 FOR VALUES FROM (0) TO (100);
+ALTER TABLE pk41 SET UNLOGGED;	-- fails
+ERROR:  could not change table "pk41" to unlogged because it references logged table "fk"
+DROP TABLE fk, pk, pk2, pk3;
+-- referencing side
+CREATE UNLOGGED TABLE pk (a int PRIMARY KEY);
+CREATE UNLOGGED TABLE fk (x int) PARTITION BY RANGE (x);
+CREATE TABLE fk1 PARTITION OF fk FOR VALUES FROM (0) TO (100);
+ALTER TABLE fk ADD FOREIGN KEY (x) REFERENCES pk;	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk1" would reference unlogged table "pk".
+DROP TABLE fk1;
+CREATE UNLOGGED TABLE fk1 PARTITION OF fk FOR VALUES FROM (0) TO (100);
+ALTER TABLE fk ADD FOREIGN KEY (x) REFERENCES pk;
+CREATE TABLE fk2 PARTITION OF fk FOR VALUES FROM (100) TO (200);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk2" would reference unlogged table "pk".
+CREATE TABLE fk3 (x int);
+ALTER TABLE fk ATTACH PARTITION fk3 FOR VALUES FROM (100) TO (200);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk3" would reference unlogged table "pk".
+CREATE UNLOGGED TABLE fk4 (x int) PARTITION BY RANGE (x);
+CREATE TABLE fk41 PARTITION OF fk4 FOR VALUES FROM (100) TO (150);
+ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES FROM (100) TO (200);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk41" would reference unlogged table "pk".
+ALTER TABLE fk1 SET LOGGED;	-- fails
+ERROR:  could not change table "fk1" to logged because it references unlogged table "pk"
+DROP TABLE fk, fk3, fk4, pk;
+-- both sides partitioned
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+CREATE UNLOGGED TABLE fk (x int REFERENCES pk) PARTITION BY RANGE (x);
+CREATE UNLOGGED TABLE fk1 PARTITION OF fk FOR VALUES FROM (0) TO (100);
+CREATE UNLOGGED TABLE pk2 PARTITION OF pk FOR VALUES FROM (100) TO (200);
+CREATE TABLE fk2 PARTITION OF fk FOR VALUES FROM (100) TO (200);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk2" would reference unlogged table "pk2".
+ALTER TABLE fk1 SET LOGGED;	-- fails
+ERROR:  could not change table "fk1" to logged because it references unlogged table "pk2"
+ALTER TABLE pk DETACH PARTITION pk2;
+DROP TABLE pk2;
+CREATE TABLE fk2 PARTITION OF fk FOR VALUES FROM (100) TO (200);
+CREATE UNLOGGED TABLE pk2 PARTITION OF pk FOR VALUES FROM (100) TO (200);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "fk2" would reference unlogged table "pk2".
+ALTER TABLE pk1 SET UNLOGGED;	-- fails
+ERROR:  could not change table "pk1" to unlogged because it references logged table "fk2"
+-- ok, unlogged partitions of the referencing side may still be added
+CREATE UNLOGGED TABLE fk3 PARTITION OF fk FOR VALUES FROM (200) TO (300);
+DROP TABLE fk, pk;
+-- self-referencing
+CREATE TABLE self (a int PRIMARY KEY, b int REFERENCES self) PARTITION BY RANGE (a);
+CREATE TABLE self1 PARTITION OF self FOR VALUES FROM (0) TO (100);
+CREATE UNLOGGED TABLE self2 PARTITION OF self FOR VALUES FROM (100) TO (200);	-- fails
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Permanent table "self1" would reference unlogged table "self2".
+CREATE TABLE self2 PARTITION OF self FOR VALUES FROM (100) TO (200);
+ALTER TABLE self2 SET UNLOGGED;	-- fails
+ERROR:  could not change table "self2" to unlogged because it references logged table "self"
+DROP TABLE self;
+-- ok, the table being changed is not counted twice
+CREATE TABLE self (a int PRIMARY KEY, b int REFERENCES self) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE self1 PARTITION OF self FOR VALUES FROM (0) TO (100);
+ALTER TABLE self1 SET LOGGED;
+DROP TABLE self;
+DROP SCHEMA fkpart14;
+RESET search_path;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 1da88b2aa0b..eb1875b0908 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2381,3 +2381,77 @@ DROP CAST (fk_err_vch AS int);
 DROP FUNCTION fk_err_vcast(fk_err_vch);
 DROP TYPE fk_err_vch;
 DROP ROLE regress_fk_err_role;
+
+-- FK persistence rules apply to the partitions on both sides, not just to
+-- the named tables: unlogged partitioned tables can have permanent
+-- partitions and vice versa, and crash recovery resets every unlogged
+-- partition, leaving rows in permanent referencing partitions dangling.
+CREATE SCHEMA fkpart14;
+SET search_path TO fkpart14;
+-- referenced side
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+CREATE TABLE fk (x int REFERENCES pk);	-- fails
+CREATE UNLOGGED TABLE ufk (x int REFERENCES pk);
+ALTER TABLE ufk SET LOGGED;	-- fails
+DROP TABLE ufk, pk;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE fk (x int REFERENCES pk);
+CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);	-- fails
+CREATE UNLOGGED TABLE pk2 (a int PRIMARY KEY);
+ALTER TABLE pk ATTACH PARTITION pk2 FOR VALUES FROM (0) TO (100);	-- fails
+CREATE TABLE pk3 (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE pk31 PARTITION OF pk3 FOR VALUES FROM (0) TO (50);
+ALTER TABLE pk ATTACH PARTITION pk3 FOR VALUES FROM (0) TO (100);	-- fails
+-- ok, only the persistence of partitions with storage matters
+CREATE UNLOGGED TABLE pk4 (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE pk41 PARTITION OF pk4 FOR VALUES FROM (0) TO (50);
+ALTER TABLE pk ATTACH PARTITION pk4 FOR VALUES FROM (0) TO (100);
+ALTER TABLE pk41 SET UNLOGGED;	-- fails
+DROP TABLE fk, pk, pk2, pk3;
+-- referencing side
+CREATE UNLOGGED TABLE pk (a int PRIMARY KEY);
+CREATE UNLOGGED TABLE fk (x int) PARTITION BY RANGE (x);
+CREATE TABLE fk1 PARTITION OF fk FOR VALUES FROM (0) TO (100);
+ALTER TABLE fk ADD FOREIGN KEY (x) REFERENCES pk;	-- fails
+DROP TABLE fk1;
+CREATE UNLOGGED TABLE fk1 PARTITION OF fk FOR VALUES FROM (0) TO (100);
+ALTER TABLE fk ADD FOREIGN KEY (x) REFERENCES pk;
+CREATE TABLE fk2 PARTITION OF fk FOR VALUES FROM (100) TO (200);	-- fails
+CREATE TABLE fk3 (x int);
+ALTER TABLE fk ATTACH PARTITION fk3 FOR VALUES FROM (100) TO (200);	-- fails
+CREATE UNLOGGED TABLE fk4 (x int) PARTITION BY RANGE (x);
+CREATE TABLE fk41 PARTITION OF fk4 FOR VALUES FROM (100) TO (150);
+ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES FROM (100) TO (200);	-- fails
+ALTER TABLE fk1 SET LOGGED;	-- fails
+DROP TABLE fk, fk3, fk4, pk;
+-- both sides partitioned
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+CREATE UNLOGGED TABLE fk (x int REFERENCES pk) PARTITION BY RANGE (x);
+CREATE UNLOGGED TABLE fk1 PARTITION OF fk FOR VALUES FROM (0) TO (100);
+CREATE UNLOGGED TABLE pk2 PARTITION OF pk FOR VALUES FROM (100) TO (200);
+CREATE TABLE fk2 PARTITION OF fk FOR VALUES FROM (100) TO (200);	-- fails
+ALTER TABLE fk1 SET LOGGED;	-- fails
+ALTER TABLE pk DETACH PARTITION pk2;
+DROP TABLE pk2;
+CREATE TABLE fk2 PARTITION OF fk FOR VALUES FROM (100) TO (200);
+CREATE UNLOGGED TABLE pk2 PARTITION OF pk FOR VALUES FROM (100) TO (200);	-- fails
+ALTER TABLE pk1 SET UNLOGGED;	-- fails
+-- ok, unlogged partitions of the referencing side may still be added
+CREATE UNLOGGED TABLE fk3 PARTITION OF fk FOR VALUES FROM (200) TO (300);
+DROP TABLE fk, pk;
+-- self-referencing
+CREATE TABLE self (a int PRIMARY KEY, b int REFERENCES self) PARTITION BY RANGE (a);
+CREATE TABLE self1 PARTITION OF self FOR VALUES FROM (0) TO (100);
+CREATE UNLOGGED TABLE self2 PARTITION OF self FOR VALUES FROM (100) TO (200);	-- fails
+CREATE TABLE self2 PARTITION OF self FOR VALUES FROM (100) TO (200);
+ALTER TABLE self2 SET UNLOGGED;	-- fails
+DROP TABLE self;
+-- ok, the table being changed is not counted twice
+CREATE TABLE self (a int PRIMARY KEY, b int REFERENCES self) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE self1 PARTITION OF self FOR VALUES FROM (0) TO (100);
+ALTER TABLE self1 SET LOGGED;
+DROP TABLE self;
+DROP SCHEMA fkpart14;
+RESET search_path;
-- 
2.56.0

From ad35285ffb35589dbd827fb8bfb6aabe2b87814b Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Wed, 11 Nov 2026 22:19:48 +0000
Subject: [PATCH REL_17 v4 2/3] Check foreign keys after adding them in ALTER
 TABLE SET LOGGED

Previously, ALTER TABLE ... SET LOGGED checked the table's foreign keys
in the preparation phase, before any subcommand had run. A foreign key
added by the same command did not exist yet, and when it was added the
table was still unlogged, so it could reference an unlogged table. For
example, the following left fk permanent while still referencing the
unlogged table upk:

    ALTER TABLE fk SET LOGGED, ADD FOREIGN KEY (x) REFERENCES upk;

User code running later in the same command had the same effect: a
table_rewrite event trigger or a USING expression could add a foreign
key, or make the referenced table unlogged, while the table still had
its old persistence. The same applies to SET UNLOGGED. After a crash,
the referenced rows are gone but the referencing ones remain.

Fix this by checking the foreign keys at the start of phase 3, which
sees the ones added by the command, and again at the end of phase 3,
after the rewrites and everything else the command runs.  The first
check keeps the common failure, an existing foreign key, from rewriting
the table before raising the error.

This has been possible since commit f41872d0c12 added SET LOGGED.
Backpatch to all supported versions.
---
 src/backend/commands/tablecmds.c            | 71 +++++++++++++++++----
 src/test/regress/expected/alter_table.out   | 21 ++++++
 src/test/regress/expected/event_trigger.out | 42 ++++++++++++
 src/test/regress/expected/foreign_key.out   |  8 ++-
 src/test/regress/sql/alter_table.sql        | 18 ++++++
 src/test/regress/sql/event_trigger.sql      | 41 ++++++++++++
 src/test/regress/sql/foreign_key.sql        |  6 +-
 7 files changed, 191 insertions(+), 16 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 88c0402c847..40e7b462110 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -626,6 +626,7 @@ static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
 static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const char *amname);
 static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId);
 static bool ATPrepChangePersistence(Relation rel, bool toLogged);
+static void ATCheckPersistenceForeignKeys(AlteredTableInfo *tab);
 static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
 								const char *tablespacename, LOCKMODE lockmode);
 static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
@@ -5730,6 +5731,22 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 {
 	ListCell   *ltab;
 
+	/*
+	 * Check the foreign keys of tables changing persistence before rewriting
+	 * anything, so that the common failure is cheap.  This sees the foreign
+	 * keys added in phase 2.  User code run below can still change things, so
+	 * the check is repeated at the end.  Partitioned tables are included, as
+	 * SET LOGGED/UNLOGGED checks their foreign keys even though nothing is
+	 * changed for them.
+	 */
+	foreach(ltab, *wqueue)
+	{
+		AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab);
+
+		if (tab->chgPersistence)
+			ATCheckPersistenceForeignKeys(tab);
+	}
+
 	/* Go through each table that needs to be checked or rewritten */
 	foreach(ltab, *wqueue)
 	{
@@ -6005,6 +6022,21 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 			CommandCounterIncrement();
 		}
 	}
+
+	/*
+	 * Check the foreign keys of tables changing persistence again.  User code
+	 * that ran above, such as table_rewrite event triggers or expressions
+	 * evaluated during a rewrite, can add foreign keys, or change the
+	 * persistence of the other side, while this table still has its old
+	 * persistence.
+	 */
+	foreach(ltab, *wqueue)
+	{
+		AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab);
+
+		if (tab->chgPersistence)
+			ATCheckPersistenceForeignKeys(tab);
+	}
 }
 
 /*
@@ -17865,9 +17897,8 @@ ATExecSetCompression(Relation rel,
 /*
  * Preparation phase for SET LOGGED/UNLOGGED
  *
- * This verifies that we're not trying to change a temp table.  Also,
- * existing foreign key constraints are checked to avoid ending up with
- * permanent tables referencing unlogged tables.
+ * This verifies that we're not trying to change a temp table.  Foreign keys
+ * are checked later, by ATCheckPersistenceForeignKeys.
  *
  * Return value is false if the operation is a no-op (in which case the
  * checks are skipped), otherwise true.
@@ -17875,11 +17906,6 @@ ATExecSetCompression(Relation rel,
 static bool
 ATPrepChangePersistence(Relation rel, bool toLogged)
 {
-	Relation	pg_constraint;
-	HeapTuple	tuple;
-	SysScanDesc scan;
-	ScanKeyData skey[1];
-
 	/*
 	 * Disallow changing status for a temp table.  Also verify whether we can
 	 * get away with doing nothing; in such cases we don't need to run the
@@ -17918,11 +17944,28 @@ ATPrepChangePersistence(Relation rel, bool toLogged)
 						RelationGetRelationName(rel)),
 				 errdetail("Unlogged relations cannot be replicated.")));
 
-	/*
-	 * Check existing foreign key constraints to preserve the invariant that
-	 * permanent tables cannot reference unlogged ones.  Self-referencing
-	 * foreign keys can safely be ignored.
-	 */
+	return true;
+}
+
+/*
+ * Check foreign keys for SET LOGGED/UNLOGGED
+ *
+ * This preserves the invariant that permanent tables cannot reference
+ * unlogged ones.  It runs in phase 3 rather than at preparation; see
+ * ATRewriteTables.
+ */
+static void
+ATCheckPersistenceForeignKeys(AlteredTableInfo *tab)
+{
+	bool		toLogged = (tab->newrelpersistence == RELPERSISTENCE_PERMANENT);
+	Relation	rel;
+	Relation	pg_constraint;
+	HeapTuple	tuple;
+	SysScanDesc scan;
+	ScanKeyData skey[1];
+
+	rel = table_open(tab->relid, NoLock);
+
 	pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
 
 	/*
@@ -18003,7 +18046,7 @@ ATPrepChangePersistence(Relation rel, bool toLogged)
 
 	table_close(pg_constraint, AccessShareLock);
 
-	return true;
+	table_close(rel, NoLock);
 }
 
 /*
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index c15a0fad1e9..81e8623b190 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3861,6 +3861,22 @@ CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unl
 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
 ERROR:  could not change table "unlogged2" to logged because it references unlogged table "unlogged1"
+CREATE UNLOGGED TABLE unlogged4(f1 INTEGER);
+ALTER TABLE unlogged4 SET LOGGED, ADD FOREIGN KEY (f1) REFERENCES unlogged1; -- fails, same with a foreign key added by the same command
+ERROR:  could not change table "unlogged4" to logged because it references unlogged table "unlogged1"
+ALTER TABLE unlogged4 ADD FOREIGN KEY (f1) REFERENCES unlogged1, SET LOGGED; -- fails
+ERROR:  could not change table "unlogged4" to logged because it references unlogged table "unlogged1"
+ALTER TABLE unlogged4 SET LOGGED, ADD FOREIGN KEY (f1) REFERENCES unlogged3; -- ok, unlogged3 is permanent now
+CREATE UNLOGGED TABLE unlogged5(f1 INTEGER PRIMARY KEY, f2 INTEGER);
+ALTER TABLE unlogged5 SET LOGGED, ADD FOREIGN KEY (f2) REFERENCES unlogged5; -- skip self-referencing foreign key
+CREATE TABLE logged6(f1 INTEGER PRIMARY KEY);
+CREATE UNLOGGED TABLE unlogged6(f1 INTEGER REFERENCES logged6, f2 INTEGER);
+INSERT INTO logged6 VALUES (1);
+INSERT INTO unlogged6 VALUES (1, 1);
+CREATE FUNCTION unlog_logged6(int) RETURNS bigint LANGUAGE plpgsql AS
+$$ BEGIN ALTER TABLE logged6 SET UNLOGGED; RETURN $1; END $$;
+ALTER TABLE unlogged6 SET LOGGED, ALTER f2 TYPE bigint USING unlog_logged6(f2); -- fails, logged6 became unlogged during the rewrite
+ERROR:  could not change table "unlogged6" to logged because it references unlogged table "logged6"
 ALTER TABLE unlogged1 SET LOGGED;
 -- check relpersistence of an unlogged table after changing to permanent
 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
@@ -3879,6 +3895,11 @@ ORDER BY relname;
 (5 rows)
 
 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
+DROP TABLE unlogged6;
+DROP TABLE logged6;
+DROP FUNCTION unlog_logged6(int);
+DROP TABLE unlogged5;
+DROP TABLE unlogged4;
 DROP TABLE unlogged3;
 DROP TABLE unlogged2;
 DROP TABLE unlogged1;
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 777f1660c0d..16f0ccf8541 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -629,6 +629,48 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+-- a table_rewrite event trigger cannot be used to add a foreign key between
+-- a permanent and an unlogged table, while the table being rewritten still
+-- has its old persistence
+CREATE UNLOGGED TABLE rewrite_upk (id int PRIMARY KEY);
+CREATE UNLOGGED TABLE rewrite_ufk (id int);
+CREATE TABLE rewrite_ppk (id int PRIMARY KEY);
+CREATE TABLE rewrite_pfk (id int);
+CREATE FUNCTION test_evtrig_add_fk() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  IF pg_event_trigger_table_rewrite_oid() = 'rewrite_ufk'::regclass THEN
+    ALTER TABLE rewrite_ufk ADD FOREIGN KEY (id) REFERENCES rewrite_upk;
+  ELSIF pg_event_trigger_table_rewrite_oid() = 'rewrite_ppk'::regclass THEN
+    ALTER TABLE rewrite_pfk ADD FOREIGN KEY (id) REFERENCES rewrite_ppk;
+  END IF;
+END;
+$$;
+CREATE EVENT TRIGGER add_fk ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_add_fk();
+ALTER TABLE rewrite_ufk SET LOGGED;  -- fails
+ERROR:  could not change table "rewrite_ufk" to logged because it references unlogged table "rewrite_upk"
+ALTER TABLE rewrite_ppk SET UNLOGGED;  -- fails
+ERROR:  could not change table "rewrite_ppk" to unlogged because it references logged table "rewrite_pfk"
+DROP EVENT TRIGGER add_fk;
+DROP FUNCTION test_evtrig_add_fk();
+DROP TABLE rewrite_ufk, rewrite_upk, rewrite_pfk, rewrite_ppk;
+-- an existing foreign key makes SET LOGGED fail before any rewrite
+CREATE UNLOGGED TABLE rewrite_upk (id int PRIMARY KEY);
+CREATE UNLOGGED TABLE rewrite_ufk (id int REFERENCES rewrite_upk);
+CREATE FUNCTION test_evtrig_rewrite_notice() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  RAISE NOTICE 'rewriting %', pg_event_trigger_table_rewrite_oid()::regclass;
+END;
+$$;
+CREATE EVENT TRIGGER rewrite_notice ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_rewrite_notice();
+ALTER TABLE rewrite_ufk SET LOGGED;  -- fails, no rewrite
+ERROR:  could not change table "rewrite_ufk" to logged because it references unlogged table "rewrite_upk"
+DROP EVENT TRIGGER rewrite_notice;
+DROP FUNCTION test_evtrig_rewrite_notice();
+DROP TABLE rewrite_ufk, rewrite_upk;
 -- Tests for REINDEX
 CREATE OR REPLACE FUNCTION reindex_start_command()
 RETURNS event_trigger AS $$
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 314155fd3c3..061dc9db1aa 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -3325,7 +3325,10 @@ DETAIL:  Permanent table "fk" would reference unlogged table "pk1".
 CREATE UNLOGGED TABLE ufk (x int REFERENCES pk);
 ALTER TABLE ufk SET LOGGED;	-- fails
 ERROR:  could not change table "ufk" to logged because it references unlogged table "pk1"
-DROP TABLE ufk, pk;
+CREATE UNLOGGED TABLE ufk2 (x int);
+ALTER TABLE ufk2 SET LOGGED, ADD FOREIGN KEY (x) REFERENCES pk;	-- fails
+ERROR:  could not change table "ufk2" to logged because it references unlogged table "pk1"
+DROP TABLE ufk, ufk2, pk;
 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
 CREATE TABLE fk (x int REFERENCES pk);
 CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);	-- fails
@@ -3371,6 +3374,9 @@ ERROR:  constraints on permanent tables may reference only permanent tables
 DETAIL:  Permanent table "fk41" would reference unlogged table "pk".
 ALTER TABLE fk1 SET LOGGED;	-- fails
 ERROR:  could not change table "fk1" to logged because it references unlogged table "pk"
+-- SET LOGGED on a partitioned table changes nothing, but is still checked
+ALTER TABLE fk SET LOGGED;	-- fails
+ERROR:  could not change table "fk" to logged because it references unlogged table "pk"
 DROP TABLE fk, fk3, fk4, pk;
 -- both sides partitioned
 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index d084aa8efd0..9c980e4d426 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2460,6 +2460,19 @@ CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unl
 CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
+CREATE UNLOGGED TABLE unlogged4(f1 INTEGER);
+ALTER TABLE unlogged4 SET LOGGED, ADD FOREIGN KEY (f1) REFERENCES unlogged1; -- fails, same with a foreign key added by the same command
+ALTER TABLE unlogged4 ADD FOREIGN KEY (f1) REFERENCES unlogged1, SET LOGGED; -- fails
+ALTER TABLE unlogged4 SET LOGGED, ADD FOREIGN KEY (f1) REFERENCES unlogged3; -- ok, unlogged3 is permanent now
+CREATE UNLOGGED TABLE unlogged5(f1 INTEGER PRIMARY KEY, f2 INTEGER);
+ALTER TABLE unlogged5 SET LOGGED, ADD FOREIGN KEY (f2) REFERENCES unlogged5; -- skip self-referencing foreign key
+CREATE TABLE logged6(f1 INTEGER PRIMARY KEY);
+CREATE UNLOGGED TABLE unlogged6(f1 INTEGER REFERENCES logged6, f2 INTEGER);
+INSERT INTO logged6 VALUES (1);
+INSERT INTO unlogged6 VALUES (1, 1);
+CREATE FUNCTION unlog_logged6(int) RETURNS bigint LANGUAGE plpgsql AS
+$$ BEGIN ALTER TABLE logged6 SET UNLOGGED; RETURN $1; END $$;
+ALTER TABLE unlogged6 SET LOGGED, ALTER f2 TYPE bigint USING unlog_logged6(f2); -- fails, logged6 became unlogged during the rewrite
 ALTER TABLE unlogged1 SET LOGGED;
 -- check relpersistence of an unlogged table after changing to permanent
 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
@@ -2469,6 +2482,11 @@ UNION ALL
 SELECT r.relname || ' toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
 ORDER BY relname;
 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
+DROP TABLE unlogged6;
+DROP TABLE logged6;
+DROP FUNCTION unlog_logged6(int);
+DROP TABLE unlogged5;
+DROP TABLE unlogged4;
 DROP TABLE unlogged3;
 DROP TABLE unlogged2;
 DROP TABLE unlogged1;
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index c613c0cfd43..117b2987729 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -471,6 +471,47 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+-- a table_rewrite event trigger cannot be used to add a foreign key between
+-- a permanent and an unlogged table, while the table being rewritten still
+-- has its old persistence
+CREATE UNLOGGED TABLE rewrite_upk (id int PRIMARY KEY);
+CREATE UNLOGGED TABLE rewrite_ufk (id int);
+CREATE TABLE rewrite_ppk (id int PRIMARY KEY);
+CREATE TABLE rewrite_pfk (id int);
+CREATE FUNCTION test_evtrig_add_fk() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  IF pg_event_trigger_table_rewrite_oid() = 'rewrite_ufk'::regclass THEN
+    ALTER TABLE rewrite_ufk ADD FOREIGN KEY (id) REFERENCES rewrite_upk;
+  ELSIF pg_event_trigger_table_rewrite_oid() = 'rewrite_ppk'::regclass THEN
+    ALTER TABLE rewrite_pfk ADD FOREIGN KEY (id) REFERENCES rewrite_ppk;
+  END IF;
+END;
+$$;
+CREATE EVENT TRIGGER add_fk ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_add_fk();
+ALTER TABLE rewrite_ufk SET LOGGED;  -- fails
+ALTER TABLE rewrite_ppk SET UNLOGGED;  -- fails
+DROP EVENT TRIGGER add_fk;
+DROP FUNCTION test_evtrig_add_fk();
+DROP TABLE rewrite_ufk, rewrite_upk, rewrite_pfk, rewrite_ppk;
+
+-- an existing foreign key makes SET LOGGED fail before any rewrite
+CREATE UNLOGGED TABLE rewrite_upk (id int PRIMARY KEY);
+CREATE UNLOGGED TABLE rewrite_ufk (id int REFERENCES rewrite_upk);
+CREATE FUNCTION test_evtrig_rewrite_notice() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  RAISE NOTICE 'rewriting %', pg_event_trigger_table_rewrite_oid()::regclass;
+END;
+$$;
+CREATE EVENT TRIGGER rewrite_notice ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_rewrite_notice();
+ALTER TABLE rewrite_ufk SET LOGGED;  -- fails, no rewrite
+DROP EVENT TRIGGER rewrite_notice;
+DROP FUNCTION test_evtrig_rewrite_notice();
+DROP TABLE rewrite_ufk, rewrite_upk;
+
 -- Tests for REINDEX
 CREATE OR REPLACE FUNCTION reindex_start_command()
 RETURNS event_trigger AS $$
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index eb1875b0908..10a5c376622 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2394,7 +2394,9 @@ CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
 CREATE TABLE fk (x int REFERENCES pk);	-- fails
 CREATE UNLOGGED TABLE ufk (x int REFERENCES pk);
 ALTER TABLE ufk SET LOGGED;	-- fails
-DROP TABLE ufk, pk;
+CREATE UNLOGGED TABLE ufk2 (x int);
+ALTER TABLE ufk2 SET LOGGED, ADD FOREIGN KEY (x) REFERENCES pk;	-- fails
+DROP TABLE ufk, ufk2, pk;
 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
 CREATE TABLE fk (x int REFERENCES pk);
 CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);	-- fails
@@ -2424,6 +2426,8 @@ CREATE UNLOGGED TABLE fk4 (x int) PARTITION BY RANGE (x);
 CREATE TABLE fk41 PARTITION OF fk4 FOR VALUES FROM (100) TO (150);
 ALTER TABLE fk ATTACH PARTITION fk4 FOR VALUES FROM (100) TO (200);	-- fails
 ALTER TABLE fk1 SET LOGGED;	-- fails
+-- SET LOGGED on a partitioned table changes nothing, but is still checked
+ALTER TABLE fk SET LOGGED;	-- fails
 DROP TABLE fk, fk3, fk4, pk;
 -- both sides partitioned
 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
-- 
2.56.0

From e71735ee759d49386810c098303255accad1c8b4 Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Wed, 11 Nov 2026 22:57:03 +0000
Subject: [PATCH REL_17 v4 3/3] Don't undo table_rewrite event trigger changes
 in ALTER TABLE

Previously, ALTER TABLE phase 3 looked up the tablespace, access method
and persistence of a table before firing the table_rewrite event
trigger, and rewrote the table with those values. If the trigger
changed any of them on the same table, the rewrite silently reverted
the change. For example, a trigger that ran ALTER TABLE ... SET LOGGED
on a table being rewritten by ALTER COLUMN TYPE left the table unlogged
again. If the trigger also added a foreign key from a permanent table
to it, this bypassed the check that permanent tables do not reference
unlogged ones.

Fix this by firing the event trigger before looking at the relation.

Backpatch to all supported versions.
---
 src/backend/commands/tablecmds.c            | 32 +++++++++++----------
 src/test/regress/expected/event_trigger.out | 23 +++++++++++++++
 src/test/regress/sql/event_trigger.sql      | 19 ++++++++++++
 3 files changed, 59 insertions(+), 15 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 40e7b462110..0339fe35beb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5800,6 +5800,23 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 			Oid			NewTableSpace;
 			char		persistence;
 
+			/*
+			 * Fire off an Event Trigger now, before actually rewriting the
+			 * table.  This must happen before we look at the relation below,
+			 * as the trigger can run commands that change it, such as its
+			 * persistence, and we must not undo those changes.
+			 *
+			 * We don't support Event Trigger for nested commands anywhere,
+			 * here included, and parsetree is given NULL when coming from
+			 * AlterTableInternal.
+			 *
+			 * And fire it only once.
+			 */
+			if (parsetree)
+				EventTriggerTableRewrite((Node *) parsetree,
+										 tab->relid,
+										 tab->rewrite);
+
 			OldHeap = table_open(tab->relid, NoLock);
 
 			/*
@@ -5857,21 +5874,6 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 
 			table_close(OldHeap, NoLock);
 
-			/*
-			 * Fire off an Event Trigger now, before actually rewriting the
-			 * table.
-			 *
-			 * We don't support Event Trigger for nested commands anywhere,
-			 * here included, and parsetree is given NULL when coming from
-			 * AlterTableInternal.
-			 *
-			 * And fire it only once.
-			 */
-			if (parsetree)
-				EventTriggerTableRewrite((Node *) parsetree,
-										 tab->relid,
-										 tab->rewrite);
-
 			/*
 			 * Create transient table that will receive the modified data.
 			 *
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index 16f0ccf8541..f24bbb086e7 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -671,6 +671,29 @@ ERROR:  could not change table "rewrite_ufk" to logged because it references unl
 DROP EVENT TRIGGER rewrite_notice;
 DROP FUNCTION test_evtrig_rewrite_notice();
 DROP TABLE rewrite_ufk, rewrite_upk;
+-- changes made by a table_rewrite event trigger to the table being rewritten
+-- are not undone by the rewrite
+CREATE UNLOGGED TABLE rewrite_persist (a int);
+CREATE FUNCTION test_evtrig_set_logged() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  IF (pg_event_trigger_table_rewrite_reason() & 1) = 0 THEN
+    ALTER TABLE rewrite_persist SET LOGGED;
+  END IF;
+END;
+$$;
+CREATE EVENT TRIGGER set_logged ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_set_logged();
+ALTER TABLE rewrite_persist ALTER a TYPE bigint;
+SELECT relpersistence FROM pg_class WHERE oid = 'rewrite_persist'::regclass;
+ relpersistence 
+----------------
+ p
+(1 row)
+
+DROP EVENT TRIGGER set_logged;
+DROP FUNCTION test_evtrig_set_logged();
+DROP TABLE rewrite_persist;
 -- Tests for REINDEX
 CREATE OR REPLACE FUNCTION reindex_start_command()
 RETURNS event_trigger AS $$
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 117b2987729..5b5627e27d6 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -512,6 +512,25 @@ DROP EVENT TRIGGER rewrite_notice;
 DROP FUNCTION test_evtrig_rewrite_notice();
 DROP TABLE rewrite_ufk, rewrite_upk;
 
+-- changes made by a table_rewrite event trigger to the table being rewritten
+-- are not undone by the rewrite
+CREATE UNLOGGED TABLE rewrite_persist (a int);
+CREATE FUNCTION test_evtrig_set_logged() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  IF (pg_event_trigger_table_rewrite_reason() & 1) = 0 THEN
+    ALTER TABLE rewrite_persist SET LOGGED;
+  END IF;
+END;
+$$;
+CREATE EVENT TRIGGER set_logged ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_set_logged();
+ALTER TABLE rewrite_persist ALTER a TYPE bigint;
+SELECT relpersistence FROM pg_class WHERE oid = 'rewrite_persist'::regclass;
+DROP EVENT TRIGGER set_logged;
+DROP FUNCTION test_evtrig_set_logged();
+DROP TABLE rewrite_persist;
+
 -- Tests for REINDEX
 CREATE OR REPLACE FUNCTION reindex_start_command()
 RETURNS event_trigger AS $$
-- 
2.56.0

From 0fe1eff8efc21979eb8e2d65b2fe1bc476c28c90 Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Mon, 31 Aug 2026 12:00:35 +0000
Subject: [PATCH v4 1/4] pgbench: don't disable --unlogged-tables when
 partitioning

With --partitions, --unlogged-tables was silently ignored for all of
pgbench's tables; only the pgbench_accounts partitions were created
unlogged.  The documentation promises unlogged tables regardless.

Create the other tables unlogged as requested.  pgbench_accounts
itself has to stay permanent, because a partitioned table cannot be
unlogged, and pgbench_branches is kept permanent too, so that
--foreign-keys can still reference it from the permanent
pgbench_accounts: constraints on permanent tables may reference only
permanent tables.

This also stops pgbench_history from being a permanent table that
references the unlogged pgbench_accounts partitions, a combination
that leaves dangling rows in pgbench_history after crash recovery and
that the server is about to reject.
---
 doc/src/sgml/ref/pgbench.sgml |  7 +++++++
 src/bin/pgbench/pgbench.c     | 15 ++++++++++++++-
 2 files changed, 21 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 9688527c955..356caf1eedf 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -400,6 +400,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
       <listitem>
        <para>
         Create all tables as unlogged tables, rather than permanent tables.
+        When <option>--partitions</option> is used,
+        <structname>pgbench_accounts</structname> is nevertheless created as a
+        permanent table, because a partitioned table cannot be unlogged; its
+        partitions are created unlogged.  <structname>pgbench_branches</structname>
+        is then also kept permanent, so that
+        <option>--foreign-keys</option> can reference it from the permanent
+        <structname>pgbench_accounts</structname>.
        </para>
       </listitem>
      </varlistentry>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 181f0d892ef..23b75ae9ebd 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -4961,9 +4961,22 @@ initCreateTables(PGconn *con)
 	{
 		const struct ddlinfo *ddl = &DDLs[i];
 
+		bool		unlogged = unlogged_tables;
+
+		/*
+		 * When the accounts table is partitioned, it cannot be unlogged
+		 * itself; its partitions are created unlogged instead.  In that case
+		 * pgbench_branches must stay permanent too, so that the permanent
+		 * pgbench_accounts can reference it when --foreign-keys is used.
+		 */
+		if (partition_method != PART_NONE &&
+			(strcmp(ddl->table, "pgbench_accounts") == 0 ||
+			 strcmp(ddl->table, "pgbench_branches") == 0))
+			unlogged = false;
+
 		/* Construct new create table statement. */
 		printfPQExpBuffer(&query, "create%s table %s(%s)",
-						  (unlogged_tables && partition_method == PART_NONE) ? " unlogged" : "",
+						  unlogged ? " unlogged" : "",
 						  ddl->table,
 						  (scale >= SCALE_32BIT_THRESHOLD) ? ddl->bigcols : ddl->smcols);
 
-- 
2.56.0

From 43974a7e3878265e45380262928b9b8bce9f246e Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Mon, 31 Aug 2026 12:00:35 +0000
Subject: [PATCH v4 2/4] Disallow foreign keys referencing unlogged partitions

The foreign key persistence checks are applied only to the referenced
relation named in the constraint.  When that relation is partitioned,
it is always permanent, but unlogged partitions may exist below it,
and neither the referenced-side recursion nor the cloning performed
when a partition is created or attached re-checks persistence.  A
permanent table could therefore reference rows in an unlogged
partition.  After a crash, recovery resets the partition to its init
fork: the referenced rows vanish while the referencing rows remain,
leaving the constraint durably violated, and a dump of the database
can no longer be restored.  Of these routes, only ALTER TABLE ... SET
UNLOGGED on the partition was blocked.

To fix, repeat the persistence check in addFkRecurseReferenced, which
every relation on the referenced side passes through when a constraint
is created or cloned for a newly created or attached partition.  Only
the permanent-to-unlogged combination needs to be rejected there,
since temporary relations cannot be partitions of permanent ones.

The bug is as old as foreign keys referencing partitioned tables,
in v12.
---
 src/backend/commands/tablecmds.c          | 21 ++++++++++++
 src/test/regress/expected/foreign_key.out | 42 +++++++++++++++++++++++
 src/test/regress/sql/foreign_key.sql      | 34 ++++++++++++++++++
 3 files changed, 97 insertions(+)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 93ad49e59fd..0d246054cd1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11014,6 +11014,27 @@ addFkRecurseReferenced(Constraint *fkconstraint, Relation rel,
 	Assert(CheckRelationLockedByMe(pkrel, ShareRowExclusiveLock, true));
 	Assert(CheckRelationLockedByMe(rel, ShareRowExclusiveLock, true));
 
+	/*
+	 * ATAddForeignKeyConstraint checks the persistence of the referenced
+	 * relation named in the constraint, but that says nothing about the
+	 * partitions below it: a permanent partitioned table may contain unlogged
+	 * partitions, whose data vanishes on crash recovery just as for a
+	 * standalone unlogged table.  Every relation on the referenced side
+	 * passes through here, both when the constraint is created and when it is
+	 * cloned for a newly created or attached partition, so repeat the check
+	 * for each of them.  Only the permanent-to-unlogged combination needs to
+	 * be considered, because temporary relations cannot be partitions of
+	 * permanent ones.
+	 */
+	if (rel->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT &&
+		!RelationIsPermanent(pkrel))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				 errmsg("constraints on permanent tables may reference only permanent tables"),
+				 errdetail("Table \"%s\" is an unlogged partition of a table referenced by permanent table \"%s\".",
+						   RelationGetRelationName(pkrel),
+						   RelationGetRelationName(rel))));
+
 	/*
 	 * Create action triggers to enforce the constraint, or skip them if the
 	 * constraint is NOT ENFORCED.
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index b1f36f6a1bb..4028fab4bd8 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -4267,3 +4267,45 @@ ERROR:  insert or update on table "ri_fk_io" violates foreign key constraint "ri
 DETAIL:  Key (a)=(2) is not present in table "ri_pk".
 ROLLBACK TO SAVEPOINT s;
 ROLLBACK;
+-- FK persistence rules apply to unlogged partitions on the referenced side
+-- just like to standalone unlogged tables: crash recovery resets them, so
+-- rows in a permanent referencing table would be left dangling.
+CREATE SCHEMA fkpart14;
+SET search_path TO fkpart14;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+-- fails: an existing partition of the referenced table is unlogged
+CREATE TABLE fk (x int REFERENCES pk);
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Table "pk1" is an unlogged partition of a table referenced by permanent table "fk".
+-- ok: an unlogged table may reference unlogged partitions
+CREATE UNLOGGED TABLE ufk (x int REFERENCES pk);
+DROP TABLE ufk, pk;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE fk (x int REFERENCES pk);
+-- fails: creating an unlogged partition of the referenced table
+CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Table "pk1" is an unlogged partition of a table referenced by permanent table "fk".
+-- fails: attaching an unlogged partition to the referenced table
+CREATE UNLOGGED TABLE pk2 (a int PRIMARY KEY);
+ALTER TABLE pk ATTACH PARTITION pk2 FOR VALUES FROM (0) TO (100);
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Table "pk2" is an unlogged partition of a table referenced by permanent table "fk".
+-- fails: same, with the unlogged table below a sub-partitioned table
+CREATE TABLE pk3 (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE pk31 PARTITION OF pk3 FOR VALUES FROM (0) TO (50);
+ALTER TABLE pk ATTACH PARTITION pk3 FOR VALUES FROM (0) TO (100);
+ERROR:  constraints on permanent tables may reference only permanent tables
+DETAIL:  Table "pk31" is an unlogged partition of a table referenced by permanent table "fk".
+-- ok: permanent partitions can be added, but not made unlogged afterwards
+CREATE TABLE pk4 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+ALTER TABLE pk4 SET UNLOGGED;
+ERROR:  could not change table "pk4" to unlogged because it references logged table "fk"
+-- ok: unlogged partitions on the referencing side are still allowed
+CREATE TABLE pk5 (a int PRIMARY KEY);
+CREATE TABLE fk2 (x int REFERENCES pk5) PARTITION BY RANGE (x);
+CREATE UNLOGGED TABLE fk21 PARTITION OF fk2 FOR VALUES FROM (0) TO (100);
+DROP TABLE fk2, pk5, fk, pk, pk2, pk3;
+DROP SCHEMA fkpart14;
+RESET search_path;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 99ea110e848..339fc374df5 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -3195,3 +3195,37 @@ SAVEPOINT s;
 INSERT INTO ri_fk_io VALUES ('2');	-- fails
 ROLLBACK TO SAVEPOINT s;
 ROLLBACK;
+
+-- FK persistence rules apply to unlogged partitions on the referenced side
+-- just like to standalone unlogged tables: crash recovery resets them, so
+-- rows in a permanent referencing table would be left dangling.
+CREATE SCHEMA fkpart14;
+SET search_path TO fkpart14;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+-- fails: an existing partition of the referenced table is unlogged
+CREATE TABLE fk (x int REFERENCES pk);
+-- ok: an unlogged table may reference unlogged partitions
+CREATE UNLOGGED TABLE ufk (x int REFERENCES pk);
+DROP TABLE ufk, pk;
+CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE TABLE fk (x int REFERENCES pk);
+-- fails: creating an unlogged partition of the referenced table
+CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+-- fails: attaching an unlogged partition to the referenced table
+CREATE UNLOGGED TABLE pk2 (a int PRIMARY KEY);
+ALTER TABLE pk ATTACH PARTITION pk2 FOR VALUES FROM (0) TO (100);
+-- fails: same, with the unlogged table below a sub-partitioned table
+CREATE TABLE pk3 (a int PRIMARY KEY) PARTITION BY RANGE (a);
+CREATE UNLOGGED TABLE pk31 PARTITION OF pk3 FOR VALUES FROM (0) TO (50);
+ALTER TABLE pk ATTACH PARTITION pk3 FOR VALUES FROM (0) TO (100);
+-- ok: permanent partitions can be added, but not made unlogged afterwards
+CREATE TABLE pk4 PARTITION OF pk FOR VALUES FROM (0) TO (100);
+ALTER TABLE pk4 SET UNLOGGED;
+-- ok: unlogged partitions on the referencing side are still allowed
+CREATE TABLE pk5 (a int PRIMARY KEY);
+CREATE TABLE fk2 (x int REFERENCES pk5) PARTITION BY RANGE (x);
+CREATE UNLOGGED TABLE fk21 PARTITION OF fk2 FOR VALUES FROM (0) TO (100);
+DROP TABLE fk2, pk5, fk, pk, pk2, pk3;
+DROP SCHEMA fkpart14;
+RESET search_path;
-- 
2.56.0

From d4a0fbb84fc5c82aecd2831fa4a01d0ffab5e5ac Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Wed, 11 Nov 2026 13:24:22 +0000
Subject: [PATCH v4 3/4] Check foreign keys after adding them in ALTER TABLE
 SET LOGGED

Previously, ALTER TABLE ... SET LOGGED checked the table's foreign keys
in the preparation phase, before any subcommand had run. A foreign key
added by the same command did not exist yet, and when it was added the
table was still unlogged, so it could reference an unlogged table. For
example, the following left fk permanent while still referencing the
unlogged table upk:

    ALTER TABLE fk SET LOGGED, ADD FOREIGN KEY (x) REFERENCES upk;

User code running later in the same command had the same effect: a
table_rewrite event trigger or a USING expression could add a foreign
key, or make the referenced table unlogged, while the table still had
its old persistence. The same applies to SET UNLOGGED. After a crash,
the referenced rows are gone but the referencing ones remain.

Fix this by checking the foreign keys at the start of phase 3, which
sees the ones added by the command, and again at the end of phase 3,
after the rewrites and everything else the command runs.  The first
check keeps the common failure, an existing foreign key, from rewriting
the table before raising the error.

This has been possible since commit f41872d0c12 added SET LOGGED.
Backpatch to all supported versions.
---
 src/backend/commands/tablecmds.c            | 81 ++++++++++++++++-----
 src/test/regress/expected/alter_table.out   | 21 ++++++
 src/test/regress/expected/event_trigger.out | 42 +++++++++++
 src/test/regress/expected/foreign_key.out   |  9 ++-
 src/test/regress/sql/alter_table.sql        | 18 +++++
 src/test/regress/sql/event_trigger.sql      | 41 +++++++++++
 src/test/regress/sql/foreign_key.sql        |  7 +-
 7 files changed, 197 insertions(+), 22 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0d246054cd1..53a2b3dc659 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -691,6 +691,7 @@ static void ATPrepSetAccessMethod(AlteredTableInfo *tab, Relation rel, const cha
 static void ATExecSetAccessMethodNoStorage(Relation rel, Oid newAccessMethodId);
 static void ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel,
 									bool toLogged);
+static void ATCheckPersistenceForeignKeys(AlteredTableInfo *tab);
 static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
 								const char *tablespacename, LOCKMODE lockmode);
 static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
@@ -5910,6 +5911,20 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 {
 	ListCell   *ltab;
 
+	/*
+	 * Check the foreign keys of tables changing persistence before rewriting
+	 * anything, so that the common failure is cheap.  This sees the foreign
+	 * keys added in phase 2.  User code run below can still change things, so
+	 * the check is repeated at the end.
+	 */
+	foreach(ltab, *wqueue)
+	{
+		AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab);
+
+		if (tab->chgPersistence)
+			ATCheckPersistenceForeignKeys(tab);
+	}
+
 	/* Go through each table that needs to be checked or rewritten */
 	foreach(ltab, *wqueue)
 	{
@@ -6185,6 +6200,21 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 			CommandCounterIncrement();
 		}
 	}
+
+	/*
+	 * Check the foreign keys of tables changing persistence again.  User code
+	 * that ran above, such as table_rewrite event triggers or expressions
+	 * evaluated during a rewrite, can add foreign keys, or change the
+	 * persistence of the other side, while this table still has its old
+	 * persistence.
+	 */
+	foreach(ltab, *wqueue)
+	{
+		AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab);
+
+		if (tab->chgPersistence)
+			ATCheckPersistenceForeignKeys(tab);
+	}
 }
 
 /*
@@ -19537,18 +19567,12 @@ ATExecSetCompression(Relation rel,
 /*
  * Preparation phase for SET LOGGED/UNLOGGED
  *
- * This verifies that we're not trying to change a temp table.  Also,
- * existing foreign key constraints are checked to avoid ending up with
- * permanent tables referencing unlogged tables.
+ * This verifies that we're not trying to change a temp table.  Foreign keys
+ * are checked later, by ATCheckPersistenceForeignKeys.
  */
 static void
 ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
 {
-	Relation	pg_constraint;
-	HeapTuple	tuple;
-	SysScanDesc scan;
-	ScanKeyData skey[1];
-
 	/*
 	 * Disallow changing status for a temp table.  Also verify whether we can
 	 * get away with doing nothing; in such cases we don't need to run the
@@ -19588,11 +19612,34 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
 				 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
-	 * permanent tables cannot reference unlogged ones.  Self-referencing
-	 * foreign keys can safely be ignored.
-	 */
+	/* force rewrite if necessary; see comment in ATRewriteTables */
+	tab->rewrite |= AT_REWRITE_ALTER_PERSISTENCE;
+	if (toLogged)
+		tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
+	else
+		tab->newrelpersistence = RELPERSISTENCE_UNLOGGED;
+	tab->chgPersistence = true;
+}
+
+/*
+ * Check foreign keys for SET LOGGED/UNLOGGED
+ *
+ * This preserves the invariant that permanent tables cannot reference
+ * unlogged ones.  It runs in phase 3 rather than at preparation; see
+ * ATRewriteTables.
+ */
+static void
+ATCheckPersistenceForeignKeys(AlteredTableInfo *tab)
+{
+	bool		toLogged = (tab->newrelpersistence == RELPERSISTENCE_PERMANENT);
+	Relation	rel;
+	Relation	pg_constraint;
+	HeapTuple	tuple;
+	SysScanDesc scan;
+	ScanKeyData skey[1];
+
+	rel = table_open(tab->relid, NoLock);
+
 	pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
 
 	/*
@@ -19655,13 +19702,7 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
 
 	table_close(pg_constraint, AccessShareLock);
 
-	/* force rewrite if necessary; see comment in ATRewriteTables */
-	tab->rewrite |= AT_REWRITE_ALTER_PERSISTENCE;
-	if (toLogged)
-		tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
-	else
-		tab->newrelpersistence = RELPERSISTENCE_UNLOGGED;
-	tab->chgPersistence = true;
+	table_close(rel, NoLock);
 }
 
 /*
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 20707c186b3..9f7251effc3 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3676,6 +3676,22 @@ CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unl
 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
 ERROR:  could not change table "unlogged2" to logged because it references unlogged table "unlogged1"
+CREATE UNLOGGED TABLE unlogged4(f1 INTEGER);
+ALTER TABLE unlogged4 SET LOGGED, ADD FOREIGN KEY (f1) REFERENCES unlogged1; -- fails, same with a foreign key added by the same command
+ERROR:  could not change table "unlogged4" to logged because it references unlogged table "unlogged1"
+ALTER TABLE unlogged4 ADD FOREIGN KEY (f1) REFERENCES unlogged1, SET LOGGED; -- fails
+ERROR:  could not change table "unlogged4" to logged because it references unlogged table "unlogged1"
+ALTER TABLE unlogged4 SET LOGGED, ADD FOREIGN KEY (f1) REFERENCES unlogged3; -- ok, unlogged3 is permanent now
+CREATE UNLOGGED TABLE unlogged5(f1 INTEGER PRIMARY KEY, f2 INTEGER);
+ALTER TABLE unlogged5 SET LOGGED, ADD FOREIGN KEY (f2) REFERENCES unlogged5; -- skip self-referencing foreign key
+CREATE TABLE logged6(f1 INTEGER PRIMARY KEY);
+CREATE UNLOGGED TABLE unlogged6(f1 INTEGER REFERENCES logged6, f2 INTEGER);
+INSERT INTO logged6 VALUES (1);
+INSERT INTO unlogged6 VALUES (1, 1);
+CREATE FUNCTION unlog_logged6(int) RETURNS bigint LANGUAGE plpgsql AS
+$$ BEGIN ALTER TABLE logged6 SET UNLOGGED; RETURN $1; END $$;
+ALTER TABLE unlogged6 SET LOGGED, ALTER f2 TYPE bigint USING unlog_logged6(f2); -- fails, logged6 became unlogged during the rewrite
+ERROR:  could not change table "unlogged6" to logged because it references unlogged table "logged6"
 ALTER TABLE unlogged1 SET LOGGED;
 -- check relpersistence of an unlogged table after changing to permanent
 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
@@ -3694,6 +3710,11 @@ ORDER BY relname;
 (5 rows)
 
 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
+DROP TABLE unlogged6;
+DROP TABLE logged6;
+DROP FUNCTION unlog_logged6(int);
+DROP TABLE unlogged5;
+DROP TABLE unlogged4;
 DROP TABLE unlogged3;
 DROP TABLE unlogged2;
 DROP TABLE unlogged1;
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index f57e8ffa7a5..c947f56602a 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -639,6 +639,48 @@ ERROR:  cannot alter type "rewritetype" because column "rewritemetoo3.a" uses it
 drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
+-- a table_rewrite event trigger cannot be used to add a foreign key between
+-- a permanent and an unlogged table, while the table being rewritten still
+-- has its old persistence
+CREATE UNLOGGED TABLE rewrite_upk (id int PRIMARY KEY);
+CREATE UNLOGGED TABLE rewrite_ufk (id int);
+CREATE TABLE rewrite_ppk (id int PRIMARY KEY);
+CREATE TABLE rewrite_pfk (id int);
+CREATE FUNCTION test_evtrig_add_fk() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  IF pg_event_trigger_table_rewrite_oid() = 'rewrite_ufk'::regclass THEN
+    ALTER TABLE rewrite_ufk ADD FOREIGN KEY (id) REFERENCES rewrite_upk;
+  ELSIF pg_event_trigger_table_rewrite_oid() = 'rewrite_ppk'::regclass THEN
+    ALTER TABLE rewrite_pfk ADD FOREIGN KEY (id) REFERENCES rewrite_ppk;
+  END IF;
+END;
+$$;
+CREATE EVENT TRIGGER add_fk ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_add_fk();
+ALTER TABLE rewrite_ufk SET LOGGED;  -- fails
+ERROR:  could not change table "rewrite_ufk" to logged because it references unlogged table "rewrite_upk"
+ALTER TABLE rewrite_ppk SET UNLOGGED;  -- fails
+ERROR:  could not change table "rewrite_ppk" to unlogged because it references logged table "rewrite_pfk"
+DROP EVENT TRIGGER add_fk;
+DROP FUNCTION test_evtrig_add_fk();
+DROP TABLE rewrite_ufk, rewrite_upk, rewrite_pfk, rewrite_ppk;
+-- an existing foreign key makes SET LOGGED fail before any rewrite
+CREATE UNLOGGED TABLE rewrite_upk (id int PRIMARY KEY);
+CREATE UNLOGGED TABLE rewrite_ufk (id int REFERENCES rewrite_upk);
+CREATE FUNCTION test_evtrig_rewrite_notice() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  RAISE NOTICE 'rewriting %', pg_event_trigger_table_rewrite_oid()::regclass;
+END;
+$$;
+CREATE EVENT TRIGGER rewrite_notice ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_rewrite_notice();
+ALTER TABLE rewrite_ufk SET LOGGED;  -- fails, no rewrite
+ERROR:  could not change table "rewrite_ufk" to logged because it references unlogged table "rewrite_upk"
+DROP EVENT TRIGGER rewrite_notice;
+DROP FUNCTION test_evtrig_rewrite_notice();
+DROP TABLE rewrite_ufk, rewrite_upk;
 -- Tests for REINDEX
 CREATE OR REPLACE FUNCTION reindex_start_command()
 RETURNS event_trigger AS $$
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 4028fab4bd8..9b29b9dc6ae 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -4280,7 +4280,14 @@ ERROR:  constraints on permanent tables may reference only permanent tables
 DETAIL:  Table "pk1" is an unlogged partition of a table referenced by permanent table "fk".
 -- ok: an unlogged table may reference unlogged partitions
 CREATE UNLOGGED TABLE ufk (x int REFERENCES pk);
-DROP TABLE ufk, pk;
+-- fails: the referencing table cannot be made permanent afterwards
+ALTER TABLE ufk SET LOGGED;
+ERROR:  could not change table "ufk" to logged because it references unlogged table "pk1"
+-- fails: nor in the same command that adds the constraint
+CREATE UNLOGGED TABLE ufk2 (x int);
+ALTER TABLE ufk2 SET LOGGED, ADD FOREIGN KEY (x) REFERENCES pk;
+ERROR:  could not change table "ufk2" to logged because it references unlogged table "pk1"
+DROP TABLE ufk, ufk2, pk;
 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
 CREATE TABLE fk (x int REFERENCES pk);
 -- fails: creating an unlogged partition of the referenced table
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 18f3fdac57a..3267ee728e7 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2278,6 +2278,19 @@ CREATE UNLOGGED TABLE unlogged2(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unl
 CREATE UNLOGGED TABLE unlogged3(f1 SERIAL PRIMARY KEY, f2 INTEGER REFERENCES unlogged3); -- self-referencing foreign key
 ALTER TABLE unlogged3 SET LOGGED; -- skip self-referencing foreign key
 ALTER TABLE unlogged2 SET LOGGED; -- fails because a foreign key to an unlogged table exists
+CREATE UNLOGGED TABLE unlogged4(f1 INTEGER);
+ALTER TABLE unlogged4 SET LOGGED, ADD FOREIGN KEY (f1) REFERENCES unlogged1; -- fails, same with a foreign key added by the same command
+ALTER TABLE unlogged4 ADD FOREIGN KEY (f1) REFERENCES unlogged1, SET LOGGED; -- fails
+ALTER TABLE unlogged4 SET LOGGED, ADD FOREIGN KEY (f1) REFERENCES unlogged3; -- ok, unlogged3 is permanent now
+CREATE UNLOGGED TABLE unlogged5(f1 INTEGER PRIMARY KEY, f2 INTEGER);
+ALTER TABLE unlogged5 SET LOGGED, ADD FOREIGN KEY (f2) REFERENCES unlogged5; -- skip self-referencing foreign key
+CREATE TABLE logged6(f1 INTEGER PRIMARY KEY);
+CREATE UNLOGGED TABLE unlogged6(f1 INTEGER REFERENCES logged6, f2 INTEGER);
+INSERT INTO logged6 VALUES (1);
+INSERT INTO unlogged6 VALUES (1, 1);
+CREATE FUNCTION unlog_logged6(int) RETURNS bigint LANGUAGE plpgsql AS
+$$ BEGIN ALTER TABLE logged6 SET UNLOGGED; RETURN $1; END $$;
+ALTER TABLE unlogged6 SET LOGGED, ALTER f2 TYPE bigint USING unlog_logged6(f2); -- fails, logged6 became unlogged during the rewrite
 ALTER TABLE unlogged1 SET LOGGED;
 -- check relpersistence of an unlogged table after changing to permanent
 SELECT relname, relkind, relpersistence FROM pg_class WHERE relname ~ '^unlogged1'
@@ -2287,6 +2300,11 @@ UNION ALL
 SELECT r.relname || ' toast index', ri.relkind, ri.relpersistence FROM pg_class r join pg_class t ON t.oid = r.reltoastrelid JOIN pg_index i ON i.indrelid = t.oid JOIN pg_class ri ON ri.oid = i.indexrelid WHERE r.relname ~ '^unlogged1'
 ORDER BY relname;
 ALTER TABLE unlogged1 SET LOGGED; -- silently do nothing
+DROP TABLE unlogged6;
+DROP TABLE logged6;
+DROP FUNCTION unlog_logged6(int);
+DROP TABLE unlogged5;
+DROP TABLE unlogged4;
 DROP TABLE unlogged3;
 DROP TABLE unlogged2;
 DROP TABLE unlogged1;
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 32e9bb58c5e..3167d6d30f0 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -476,6 +476,47 @@ drop table rewriteme;
 drop event trigger no_rewrite_allowed;
 drop function test_evtrig_no_rewrite();
 
+-- a table_rewrite event trigger cannot be used to add a foreign key between
+-- a permanent and an unlogged table, while the table being rewritten still
+-- has its old persistence
+CREATE UNLOGGED TABLE rewrite_upk (id int PRIMARY KEY);
+CREATE UNLOGGED TABLE rewrite_ufk (id int);
+CREATE TABLE rewrite_ppk (id int PRIMARY KEY);
+CREATE TABLE rewrite_pfk (id int);
+CREATE FUNCTION test_evtrig_add_fk() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  IF pg_event_trigger_table_rewrite_oid() = 'rewrite_ufk'::regclass THEN
+    ALTER TABLE rewrite_ufk ADD FOREIGN KEY (id) REFERENCES rewrite_upk;
+  ELSIF pg_event_trigger_table_rewrite_oid() = 'rewrite_ppk'::regclass THEN
+    ALTER TABLE rewrite_pfk ADD FOREIGN KEY (id) REFERENCES rewrite_ppk;
+  END IF;
+END;
+$$;
+CREATE EVENT TRIGGER add_fk ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_add_fk();
+ALTER TABLE rewrite_ufk SET LOGGED;  -- fails
+ALTER TABLE rewrite_ppk SET UNLOGGED;  -- fails
+DROP EVENT TRIGGER add_fk;
+DROP FUNCTION test_evtrig_add_fk();
+DROP TABLE rewrite_ufk, rewrite_upk, rewrite_pfk, rewrite_ppk;
+
+-- an existing foreign key makes SET LOGGED fail before any rewrite
+CREATE UNLOGGED TABLE rewrite_upk (id int PRIMARY KEY);
+CREATE UNLOGGED TABLE rewrite_ufk (id int REFERENCES rewrite_upk);
+CREATE FUNCTION test_evtrig_rewrite_notice() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  RAISE NOTICE 'rewriting %', pg_event_trigger_table_rewrite_oid()::regclass;
+END;
+$$;
+CREATE EVENT TRIGGER rewrite_notice ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_rewrite_notice();
+ALTER TABLE rewrite_ufk SET LOGGED;  -- fails, no rewrite
+DROP EVENT TRIGGER rewrite_notice;
+DROP FUNCTION test_evtrig_rewrite_notice();
+DROP TABLE rewrite_ufk, rewrite_upk;
+
 -- Tests for REINDEX
 CREATE OR REPLACE FUNCTION reindex_start_command()
 RETURNS event_trigger AS $$
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 339fc374df5..66fc5cd4d89 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -3207,7 +3207,12 @@ CREATE UNLOGGED TABLE pk1 PARTITION OF pk FOR VALUES FROM (0) TO (100);
 CREATE TABLE fk (x int REFERENCES pk);
 -- ok: an unlogged table may reference unlogged partitions
 CREATE UNLOGGED TABLE ufk (x int REFERENCES pk);
-DROP TABLE ufk, pk;
+-- fails: the referencing table cannot be made permanent afterwards
+ALTER TABLE ufk SET LOGGED;
+-- fails: nor in the same command that adds the constraint
+CREATE UNLOGGED TABLE ufk2 (x int);
+ALTER TABLE ufk2 SET LOGGED, ADD FOREIGN KEY (x) REFERENCES pk;
+DROP TABLE ufk, ufk2, pk;
 CREATE TABLE pk (a int PRIMARY KEY) PARTITION BY RANGE (a);
 CREATE TABLE fk (x int REFERENCES pk);
 -- fails: creating an unlogged partition of the referenced table
-- 
2.56.0

From 243f08af11ed549b078b82184bc1f2b20aa1b67a Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Wed, 11 Nov 2026 22:57:03 +0000
Subject: [PATCH v4 4/4] Don't undo table_rewrite event trigger changes in
 ALTER TABLE

Previously, ALTER TABLE phase 3 looked up the tablespace, access method
and persistence of a table before firing the table_rewrite event
trigger, and rewrote the table with those values. If the trigger
changed any of them on the same table, the rewrite silently reverted
the change. For example, a trigger that ran ALTER TABLE ... SET LOGGED
on a table being rewritten by ALTER COLUMN TYPE left the table unlogged
again. If the trigger also added a foreign key from a permanent table
to it, this bypassed the check that permanent tables do not reference
unlogged ones.

Fix this by firing the event trigger before looking at the relation.

Backpatch to all supported versions.
---
 src/backend/commands/tablecmds.c            | 32 +++++++++++----------
 src/test/regress/expected/event_trigger.out | 23 +++++++++++++++
 src/test/regress/sql/event_trigger.sql      | 19 ++++++++++++
 3 files changed, 59 insertions(+), 15 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 53a2b3dc659..72b73fad664 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5976,6 +5976,23 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 			Oid			NewTableSpace;
 			char		persistence;
 
+			/*
+			 * Fire off an Event Trigger now, before actually rewriting the
+			 * table.  This must happen before we look at the relation below,
+			 * as the trigger can run commands that change it, such as its
+			 * persistence, and we must not undo those changes.
+			 *
+			 * We don't support Event Trigger for nested commands anywhere,
+			 * here included, and parsetree is given NULL when coming from
+			 * AlterTableInternal.
+			 *
+			 * And fire it only once.
+			 */
+			if (parsetree)
+				EventTriggerTableRewrite((Node *) parsetree,
+										 tab->relid,
+										 tab->rewrite);
+
 			OldHeap = table_open(tab->relid, NoLock);
 
 			/*
@@ -6033,21 +6050,6 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 
 			table_close(OldHeap, NoLock);
 
-			/*
-			 * Fire off an Event Trigger now, before actually rewriting the
-			 * table.
-			 *
-			 * We don't support Event Trigger for nested commands anywhere,
-			 * here included, and parsetree is given NULL when coming from
-			 * AlterTableInternal.
-			 *
-			 * And fire it only once.
-			 */
-			if (parsetree)
-				EventTriggerTableRewrite((Node *) parsetree,
-										 tab->relid,
-										 tab->rewrite);
-
 			/*
 			 * Create transient table that will receive the modified data.
 			 *
diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out
index c947f56602a..f1f8840b820 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -681,6 +681,29 @@ ERROR:  could not change table "rewrite_ufk" to logged because it references unl
 DROP EVENT TRIGGER rewrite_notice;
 DROP FUNCTION test_evtrig_rewrite_notice();
 DROP TABLE rewrite_ufk, rewrite_upk;
+-- changes made by a table_rewrite event trigger to the table being rewritten
+-- are not undone by the rewrite
+CREATE UNLOGGED TABLE rewrite_persist (a int);
+CREATE FUNCTION test_evtrig_set_logged() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  IF (pg_event_trigger_table_rewrite_reason() & 1) = 0 THEN
+    ALTER TABLE rewrite_persist SET LOGGED;
+  END IF;
+END;
+$$;
+CREATE EVENT TRIGGER set_logged ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_set_logged();
+ALTER TABLE rewrite_persist ALTER a TYPE bigint;
+SELECT relpersistence FROM pg_class WHERE oid = 'rewrite_persist'::regclass;
+ relpersistence 
+----------------
+ p
+(1 row)
+
+DROP EVENT TRIGGER set_logged;
+DROP FUNCTION test_evtrig_set_logged();
+DROP TABLE rewrite_persist;
 -- Tests for REINDEX
 CREATE OR REPLACE FUNCTION reindex_start_command()
 RETURNS event_trigger AS $$
diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql
index 3167d6d30f0..0f24c6ae97e 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -517,6 +517,25 @@ DROP EVENT TRIGGER rewrite_notice;
 DROP FUNCTION test_evtrig_rewrite_notice();
 DROP TABLE rewrite_ufk, rewrite_upk;
 
+-- changes made by a table_rewrite event trigger to the table being rewritten
+-- are not undone by the rewrite
+CREATE UNLOGGED TABLE rewrite_persist (a int);
+CREATE FUNCTION test_evtrig_set_logged() RETURNS event_trigger
+LANGUAGE plpgsql AS $$
+BEGIN
+  IF (pg_event_trigger_table_rewrite_reason() & 1) = 0 THEN
+    ALTER TABLE rewrite_persist SET LOGGED;
+  END IF;
+END;
+$$;
+CREATE EVENT TRIGGER set_logged ON table_rewrite
+  EXECUTE FUNCTION test_evtrig_set_logged();
+ALTER TABLE rewrite_persist ALTER a TYPE bigint;
+SELECT relpersistence FROM pg_class WHERE oid = 'rewrite_persist'::regclass;
+DROP EVENT TRIGGER set_logged;
+DROP FUNCTION test_evtrig_set_logged();
+DROP TABLE rewrite_persist;
+
 -- Tests for REINDEX
 CREATE OR REPLACE FUNCTION reindex_start_command()
 RETURNS event_trigger AS $$
-- 
2.56.0

Reply via email to