v5-0001-Fix-incorrect-reuse-of-ResultRelInfo-in-trigger-e.patch

From 40cf77f8b51775e97c7f9eea6247865ade99cd01 Mon Sep 17 00:00:00 2001
From: Amit Langote <amitlan@postgresql.org>
Date: Tue, 21 Oct 2025 18:02:21 +0900
Subject: [PATCH v5] Fix incorrect reuse of ResultRelInfo in trigger execution
 path

When a partitioned table UPDATE cascades via a trigger,
ExecGetTriggerResultRel() could return a cached ResultRelInfo
whose ri_RootResultRelInfo did not match the caller's
rootRelInfo.  This happened because previously cached entries
were reused solely by relid, even when created under a different
parent ResultRelInfo.  In such cases, ExecGetChildToRootMap()
could skip tuple translation, leading to wrong behaviour during
partitioned UPDATE cascades or multi-ModifyTable queries.

Fix by requiring that ri_RootResultRelInfo matches the caller's
rootRelInfo when reusing a cached ResultRelInfo from any of the
opened, tuple-routing, or trigger target lists.  If no exact
match exists, a new ResultRelInfo is created.  This avoids
returning an entry built for a different parent context while
still permitting safe reuse within the same one.

Also update the header comment of ExecGetTriggerResultRel() to
document the new matching rules and add regression tests covering
this scenario.

Reported-by: Dmitry Fomin <fomin.list@gmail.com>
Diagnozed-by: David Rowley <dgrowleyml@gmail.com>
Author: David Rowley <dgrowleyml@gmail.com>
Co-authored-by: Amit Langote <amitlangote09@gmail.com>
Discussion: https://postgr.es/m/7DCE78D7-0520-4207-822B-92F60AEA14B4@gmail.com
Batckpatch-through: 15
---
 src/backend/executor/execMain.c           | 21 ++++---
 src/test/regress/expected/foreign_key.out | 69 +++++++++++++++++++++++
 src/test/regress/sql/foreign_key.sql      | 64 +++++++++++++++++++++
 3 files changed, 147 insertions(+), 7 deletions(-)

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 831c55ce787..847b749ffa5 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1326,10 +1326,8 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
  *		Get a ResultRelInfo for a trigger target relation.
  *
  * Most of the time, triggers are fired on one of the result relations of the
- * query, and so we can just return a member of the es_result_relations array,
- * or the es_tuple_routing_result_relations list (if any). (Note: in self-join
- * situations there might be multiple members with the same OID; if so it
- * doesn't matter which one we pick.)
+ * query, and so we can just return one we already made and stored in the
+ * es_opened_result_relations or es_tuple_routing_result_relations Lists.
  *
  * However, it is sometimes necessary to fire triggers on other relations;
  * this happens mainly when an RI update trigger queues additional triggers
@@ -1339,6 +1337,12 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
  * also provides a way for EXPLAIN ANALYZE to report the runtimes of such
  * triggers.)  So we make additional ResultRelInfo's as needed, and save them
  * in es_trig_target_relations.
+ *
+ * When reusing cached entries from any of these lists, require an exact
+ * match of ri_RootResultRelInfo and the rootRelInfo passed by the caller.
+ * This avoids returning an entry created for a different parent context
+ * and ensures child->parent translation is neither skipped nor applied
+ * incorrectly.  If no exact match exists, build a new ResultRelInfo.
  */
 ResultRelInfo *
 ExecGetTriggerResultRel(EState *estate, Oid relid,
@@ -1353,7 +1357,8 @@ ExecGetTriggerResultRel(EState *estate, Oid relid,
 	foreach(l, estate->es_opened_result_relations)
 	{
 		rInfo = lfirst(l);
-		if (RelationGetRelid(rInfo->ri_RelationDesc) == relid)
+		if (RelationGetRelid(rInfo->ri_RelationDesc) == relid &&
+			rInfo->ri_RootResultRelInfo == rootRelInfo)
 			return rInfo;
 	}
 
@@ -1364,7 +1369,8 @@ ExecGetTriggerResultRel(EState *estate, Oid relid,
 	foreach(l, estate->es_tuple_routing_result_relations)
 	{
 		rInfo = (ResultRelInfo *) lfirst(l);
-		if (RelationGetRelid(rInfo->ri_RelationDesc) == relid)
+		if (RelationGetRelid(rInfo->ri_RelationDesc) == relid &&
+			rInfo->ri_RootResultRelInfo == rootRelInfo)
 			return rInfo;
 	}
 
@@ -1372,7 +1378,8 @@ ExecGetTriggerResultRel(EState *estate, Oid relid,
 	foreach(l, estate->es_trig_target_relations)
 	{
 		rInfo = (ResultRelInfo *) lfirst(l);
-		if (RelationGetRelid(rInfo->ri_RelationDesc) == relid)
+		if (RelationGetRelid(rInfo->ri_RelationDesc) == relid &&
+			rInfo->ri_RootResultRelInfo == rootRelInfo)
 			return rInfo;
 	}
 	/* Nope, so we need a new one */
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index dc541d61adf..c543d029084 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -3406,3 +3406,72 @@ SET client_min_messages TO warning;
 DROP SCHEMA fkpart12 CASCADE;
 RESET client_min_messages;
 RESET search_path;
+-- Test for bug where cascading UPDATE on partitioned tables could fail
+-- due to cached ResultRelInfo lacking ri_RootResultRelInfo.
+--
+-- The problem occurred when ExecGetTriggerResultRel() reused a
+-- ResultRelInfo created earlier by afterTriggerInvokeEvents() with
+-- a NULL rootRelInfo.  On a subsequent call, the cached entry was
+-- returned instead of building one with the correct rootRelInfo.
+-- This caused ExecGetChildToRootMap() to skip tuple translation,
+-- leading to incorrect tuple formats or crashes during ON UPDATE
+-- CASCADE between partitioned tables.
+--
+-- https://postgr.es/m/7DCE78D7-0520-4207-822B-92F60AEA14B4@gmail.com
+-- Parent partitioned table
+CREATE SCHEMA fkpart13
+  CREATE TABLE parted_cp_update_bug_1 (a int PRIMARY KEY, b int) PARTITION BY LIST (a)
+  CREATE TABLE parted_cp_update_bug_1_p1 PARTITION OF parted_cp_update_bug_1 FOR VALUES IN (1)
+  CREATE TABLE parted_cp_update_bug_1_p2 PARTITION OF parted_cp_update_bug_1 FOR VALUES IN (2)
+-- Child 1. Partitioned. One child is attached after column surgery to
+-- mirror Dmitry's case where the ResultRelInfo cache path is exercised.
+  CREATE TABLE parted_cp_update_bug_2 (a int PRIMARY KEY, b int,
+	FOREIGN KEY (a) REFERENCES parted_cp_update_bug_1 ON UPDATE CASCADE) PARTITION BY LIST (a)
+  CREATE TABLE parted_cp_update_bug_2_p1 PARTITION OF parted_cp_update_bug_2 FOR VALUES IN (1) PARTITION BY LIST (a)
+  CREATE TABLE parted_cp_update_bug_2_p1_p1 PARTITION OF parted_cp_update_bug_2_p1 FOR VALUES IN (1)
+  CREATE TABLE parted_cp_update_bug_2_p2 (a int NOT NULL, c int, b int)
+-- Child 2. Partitioned. Will reference both parent and child1.
+  CREATE TABLE parted_cp_update_bug_3 (a int, b int,
+	FOREIGN KEY (a) REFERENCES parted_cp_update_bug_1 ON UPDATE CASCADE,
+	FOREIGN KEY (a) REFERENCES parted_cp_update_bug_2 ON UPDATE CASCADE) PARTITION BY LIST (a)
+  CREATE TABLE parted_cp_update_bug_3_p1 PARTITION OF parted_cp_update_bug_3 FOR VALUES IN (1)
+  CREATE TABLE parted_cp_update_bug_3_p2 PARTITION OF parted_cp_update_bug_3 FOR VALUES IN (2);
+SET search_path TO fkpart13;
+ALTER TABLE parted_cp_update_bug_2_p2 DROP COLUMN c;
+ALTER TABLE parted_cp_update_bug_2 ATTACH PARTITION parted_cp_update_bug_2_p2 FOR VALUES IN (2);
+-- Seed rows go to the a=1 partitions
+INSERT INTO parted_cp_update_bug_1 VALUES (1, 1);
+INSERT INTO parted_cp_update_bug_2 VALUES (1, 1);
+INSERT INTO parted_cp_update_bug_3 VALUES (1, 1);
+-- Cascading update on parent. Before the fix this can take the cached
+-- ResultRelInfo without ri_RootResultRelInfo and fail to translate the tuple
+-- for the partitioned root format.  CTE is used to make the child rel
+-- parted_cp_update_bug_2_p1_p1 appear twice in the result rel lists in the
+-- top-level EState once via the parent updated in the CTE and then again via
+-- the parent updated in the main query
+WITH cte AS (UPDATE parted_cp_update_bug_2_p1 SET a = a) UPDATE parted_cp_update_bug_1 SET a = 2 WHERE a = 1;
+-- Verify that each row moved to its a=2 partition and values are intact
+SELECT tableoid::regclass AS rel, a, b FROM parted_cp_update_bug_1 ORDER BY a;
+            rel            | a | b 
+---------------------------+---+---
+ parted_cp_update_bug_1_p2 | 2 | 1
+(1 row)
+
+SELECT tableoid::regclass AS rel, a, b FROM parted_cp_update_bug_2 ORDER BY a;
+            rel            | a | b 
+---------------------------+---+---
+ parted_cp_update_bug_2_p2 | 2 | 1
+(1 row)
+
+SELECT tableoid::regclass AS rel, a, b FROM parted_cp_update_bug_3 ORDER BY a;
+            rel            | a | b 
+---------------------------+---+---
+ parted_cp_update_bug_3_p2 | 2 | 1
+(1 row)
+
+-- Clean up
+DROP TABLE parted_cp_update_bug_3 CASCADE;
+DROP TABLE parted_cp_update_bug_2 CASCADE;
+DROP TABLE parted_cp_update_bug_1 CASCADE;
+RESET client_min_messages;
+RESET search_path;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 39174ad1eb9..d6b7949e29b 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -2386,3 +2386,67 @@ SET client_min_messages TO warning;
 DROP SCHEMA fkpart12 CASCADE;
 RESET client_min_messages;
 RESET search_path;
+
+-- Test for bug where cascading UPDATE on partitioned tables could fail
+-- due to cached ResultRelInfo lacking ri_RootResultRelInfo.
+--
+-- The problem occurred when ExecGetTriggerResultRel() reused a
+-- ResultRelInfo created earlier by afterTriggerInvokeEvents() with
+-- a NULL rootRelInfo.  On a subsequent call, the cached entry was
+-- returned instead of building one with the correct rootRelInfo.
+-- This caused ExecGetChildToRootMap() to skip tuple translation,
+-- leading to incorrect tuple formats or crashes during ON UPDATE
+-- CASCADE between partitioned tables.
+--
+-- https://postgr.es/m/7DCE78D7-0520-4207-822B-92F60AEA14B4@gmail.com
+
+-- Parent partitioned table
+CREATE SCHEMA fkpart13
+  CREATE TABLE parted_cp_update_bug_1 (a int PRIMARY KEY, b int) PARTITION BY LIST (a)
+  CREATE TABLE parted_cp_update_bug_1_p1 PARTITION OF parted_cp_update_bug_1 FOR VALUES IN (1)
+  CREATE TABLE parted_cp_update_bug_1_p2 PARTITION OF parted_cp_update_bug_1 FOR VALUES IN (2)
+
+-- Child 1. Partitioned. One child is attached after column surgery to
+-- mirror Dmitry's case where the ResultRelInfo cache path is exercised.
+  CREATE TABLE parted_cp_update_bug_2 (a int PRIMARY KEY, b int,
+	FOREIGN KEY (a) REFERENCES parted_cp_update_bug_1 ON UPDATE CASCADE) PARTITION BY LIST (a)
+  CREATE TABLE parted_cp_update_bug_2_p1 PARTITION OF parted_cp_update_bug_2 FOR VALUES IN (1) PARTITION BY LIST (a)
+  CREATE TABLE parted_cp_update_bug_2_p1_p1 PARTITION OF parted_cp_update_bug_2_p1 FOR VALUES IN (1)
+  CREATE TABLE parted_cp_update_bug_2_p2 (a int NOT NULL, c int, b int)
+
+-- Child 2. Partitioned. Will reference both parent and child1.
+  CREATE TABLE parted_cp_update_bug_3 (a int, b int,
+	FOREIGN KEY (a) REFERENCES parted_cp_update_bug_1 ON UPDATE CASCADE,
+	FOREIGN KEY (a) REFERENCES parted_cp_update_bug_2 ON UPDATE CASCADE) PARTITION BY LIST (a)
+  CREATE TABLE parted_cp_update_bug_3_p1 PARTITION OF parted_cp_update_bug_3 FOR VALUES IN (1)
+  CREATE TABLE parted_cp_update_bug_3_p2 PARTITION OF parted_cp_update_bug_3 FOR VALUES IN (2);
+SET search_path TO fkpart13;
+
+ALTER TABLE parted_cp_update_bug_2_p2 DROP COLUMN c;
+ALTER TABLE parted_cp_update_bug_2 ATTACH PARTITION parted_cp_update_bug_2_p2 FOR VALUES IN (2);
+
+-- Seed rows go to the a=1 partitions
+INSERT INTO parted_cp_update_bug_1 VALUES (1, 1);
+INSERT INTO parted_cp_update_bug_2 VALUES (1, 1);
+INSERT INTO parted_cp_update_bug_3 VALUES (1, 1);
+
+-- Cascading update on parent. Before the fix this can take the cached
+-- ResultRelInfo without ri_RootResultRelInfo and fail to translate the tuple
+-- for the partitioned root format.  CTE is used to make the child rel
+-- parted_cp_update_bug_2_p1_p1 appear twice in the result rel lists in the
+-- top-level EState once via the parent updated in the CTE and then again via
+-- the parent updated in the main query
+WITH cte AS (UPDATE parted_cp_update_bug_2_p1 SET a = a) UPDATE parted_cp_update_bug_1 SET a = 2 WHERE a = 1;
+
+-- Verify that each row moved to its a=2 partition and values are intact
+SELECT tableoid::regclass AS rel, a, b FROM parted_cp_update_bug_1 ORDER BY a;
+SELECT tableoid::regclass AS rel, a, b FROM parted_cp_update_bug_2 ORDER BY a;
+SELECT tableoid::regclass AS rel, a, b FROM parted_cp_update_bug_3 ORDER BY a;
+
+-- Clean up
+DROP TABLE parted_cp_update_bug_3 CASCADE;
+DROP TABLE parted_cp_update_bug_2 CASCADE;
+DROP TABLE parted_cp_update_bug_1 CASCADE;
+
+RESET client_min_messages;
+RESET search_path;
-- 
2.47.3