> Even with the v2 patch, the following steps seem to allow a permanent table
> to reference an unlogged partition. Is this an issue?

Eh, I think it is, but it is not specific to partitioned tables. This
also works, and it shouldn't:

    CREATE UNLOGGED TABLE upk (i int PRIMARY KEY);
    CREATE UNLOGGED TABLE fk (x int);
    ALTER TABLE fk SET LOGGED, ADD FOREIGN KEY (x) REFERENCES upk;

ATPrepChangePersistence checks the existing foreign keys in the prep
phase, the new constraint is added later while the table is still
unlogged, and the rewrite makes it permanent afterwards.

0003 moves the foreign key check right before the end of phase 3.

Similarly to the above example, we also had a (mostly theoretical)
issue with USING expressions and table_rewrite event triggers.

USING expressions are fixed by 0003, but table_rewrite triggers
aren't: if a table_rewrite trigger changes the persistence the rewrite
later reverts that change, and we can again end up with a mismatch in
a foreign key.

0004 fixes this by moving when we fire those triggers. (this also
affected access methods and tablespace changes)

> AFAIR, earlier versions allowed unlogged partitioned tables to have permanent
> partitions. In those versions, would the check added by this patch still work
> as expected in that case?

No, it wouldn't, 17 and earlier need additional checks on both the
referencing and referenced side, and 0002 is significantly more
complex there. I also attached a version for that, with more detailed
test coverage, that should prevent any combination where users try
create a foreign key between logged and unlogged tables.

I kept 0001 for now part of the patchset: if the patch in the other
thread gets committed, I'll drop it, the later patches do not depend
on it.

And one more question: these changes could cause pg_upgrade failures
for affected databases - but it's also a corner case, so I am not sure
how much handling it needs. Should we add detection this for
pg_upgrade --check, or at least mention it in the release notes?

e2bab2d7920 already broke upgrading without an added check, as this is
a valid 17 schema:

CREATE UNLOGGED TABLE upk (a int PRIMARY KEY);
CREATE UNLOGGED TABLE ufk (x int REFERENCES upk) PARTITION BY RANGE (x);
CREATE UNLOGGED TABLE ufk1 PARTITION OF ufk FOR VALUES FROM (0) TO (100);

and it fails the upgrade to 18, but pg_upgrade --check doesn't report 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 v3 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 7f549d0f72e1e296b1818da98b6a9c3fa1c87795 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 v3 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 end of phase 3, after the
rewrites and everything else the command runs.

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

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 88c0402c847..4138b86f771 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);
@@ -6005,6 +6006,24 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 			CommandCounterIncrement();
 		}
 	}
+
+	/*
+	 * Check the foreign keys of tables changing persistence last.  Foreign
+	 * keys added by this command must be seen, and so must anything done by
+	 * user code that ran above, such as table_rewrite event triggers or
+	 * expressions evaluated during a rewrite: those can add foreign keys, or
+	 * change the persistence of the other side, while this table still has
+	 * its old persistence.  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);
+	}
 }
 
 /*
@@ -17865,9 +17884,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 +17893,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 +17931,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 at the end of 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 +18033,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..8fe8b89280d 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -629,6 +629,32 @@ 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;
 -- 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..53e8b2c342c 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -471,6 +471,31 @@ 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;
+
 -- 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 7127bdeed15c0de8af32e452c546b48e632c25ac 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 v3 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 4138b86f771..11096b54fe8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5784,6 +5784,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);
 
 			/*
@@ -5841,21 +5858,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 8fe8b89280d..c8d38423fd8 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -655,6 +655,29 @@ ERROR:  could not change table "rewrite_ppk" to unlogged because it references l
 DROP EVENT TRIGGER add_fk;
 DROP FUNCTION test_evtrig_add_fk();
 DROP TABLE rewrite_ufk, rewrite_upk, rewrite_pfk, rewrite_ppk;
+-- 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 53e8b2c342c..279d0045c48 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -496,6 +496,25 @@ DROP EVENT TRIGGER add_fk;
 DROP FUNCTION test_evtrig_add_fk();
 DROP TABLE rewrite_ufk, rewrite_upk, rewrite_pfk, rewrite_ppk;
 
+-- 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 v3 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 v3 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 fa9fb1c9f3a54ed11c3f6f18a2b27f17d80333c5 Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Wed, 11 Nov 2026 13:24:22 +0000
Subject: [PATCH v3 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 end of phase 3, after the
rewrites and everything else the command runs.

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

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0d246054cd1..248fbafa2c3 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);
@@ -6185,6 +6186,22 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
 			CommandCounterIncrement();
 		}
 	}
+
+	/*
+	 * Check the foreign keys of tables changing persistence last.  Foreign
+	 * keys added by this command must be seen, and so must anything done by
+	 * user code that ran above, such as table_rewrite event triggers or
+	 * expressions evaluated during a rewrite: those 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 +19554,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 +19599,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 at the end of 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 +19689,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..d24d78450d0 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -639,6 +639,32 @@ 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;
 -- 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..ad82a548ea5 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -476,6 +476,31 @@ 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;
+
 -- 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 807fe068f4fcb4f9d3048b47fb491840e8325551 Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Wed, 11 Nov 2026 22:57:03 +0000
Subject: [PATCH v3 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 248fbafa2c3..1b0c9916188 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5962,6 +5962,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);
 
 			/*
@@ -6019,21 +6036,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 d24d78450d0..06ed5eef464 100644
--- a/src/test/regress/expected/event_trigger.out
+++ b/src/test/regress/expected/event_trigger.out
@@ -665,6 +665,29 @@ ERROR:  could not change table "rewrite_ppk" to unlogged because it references l
 DROP EVENT TRIGGER add_fk;
 DROP FUNCTION test_evtrig_add_fk();
 DROP TABLE rewrite_ufk, rewrite_upk, rewrite_pfk, rewrite_ppk;
+-- 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 ad82a548ea5..20dc7148d92 100644
--- a/src/test/regress/sql/event_trigger.sql
+++ b/src/test/regress/sql/event_trigger.sql
@@ -501,6 +501,25 @@ DROP EVENT TRIGGER add_fk;
 DROP FUNCTION test_evtrig_add_fk();
 DROP TABLE rewrite_ufk, rewrite_upk, rewrite_pfk, rewrite_ppk;
 
+-- 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