Fix MERGE with DO NOTHING actions into a partitioned table.

deanrasheed · deanrasheed · commit 25303678a1d3 · 2025-03-29T09:50:14.000Z
ExecInitPartitionInfo() duplicates much of the logic in ExecInitMerge(), except that it failed to handle DO NOTHING actions. This would cause an "unknown action in MERGE WHEN clause" error if a MERGE with any DO NOTHING actions attempted to insert into a partition not already initialised by ExecInitModifyTable(). Bug: #18871 Reported-by: Alexander Lakhin <exclusion@gmail.com> Author: Tender Wang <tndrwang@gmail.com> Reviewed-by: Gurjeet Singh <gurjeet@singh.im> Discussion: https://postgr.es/m/18871-b44e3c96de3bd2e8%40postgresql.org Backpatch-through: 15
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
@@ -872,7 +872,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
 	 * reference and make copy for this relation, converting stuff that
 	 * references attribute numbers to match this relation's.
 	 *
-	 * This duplicates much of the logic in ExecInitMerge(), so something
+	 * This duplicates much of the logic in ExecInitMerge(), so if something
 	 * changes there, look here too.
 	 */
 	if (node && node->operation == CMD_MERGE)
@@ -952,6 +952,8 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
 												  NULL);
 					break;
 				case CMD_DELETE:
+				case CMD_NOTHING:
+					/* Nothing to do */
 					break;
 
 				default:
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
@@ -3600,7 +3600,7 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate)
 				case CMD_NOTHING:
 					break;
 				default:
-					elog(ERROR, "unknown operation");
+					elog(ERROR, "unknown action in MERGE WHEN clause");
 					break;
 			}
 		}
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
@@ -2058,6 +2058,23 @@ SELECT * FROM pa_target ORDER BY tid;
   15 |    1500 | initial
 (8 rows)
 
+ROLLBACK;
+-- bug #18871: ExecInitPartitionInfo()'s handling of DO NOTHING actions
+BEGIN;
+TRUNCATE pa_target;
+MERGE INTO pa_target t
+  USING (VALUES (10, 100)) AS s(sid, delta)
+  ON t.tid = s.sid
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (1, 10, 'inserted by merge')
+  WHEN MATCHED THEN
+    DO NOTHING;
+SELECT * FROM pa_target ORDER BY tid, val;
+ tid | balance |        val        
+-----+---------+-------------------
+   1 |      10 | inserted by merge
+(1 row)
+
 ROLLBACK;
 DROP TABLE pa_target CASCADE;
 -- The target table is partitioned in the same way, but this time by attaching
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
@@ -1269,6 +1269,19 @@ MERGE INTO pa_target t
 SELECT * FROM pa_target ORDER BY tid;
 ROLLBACK;
 
+-- bug #18871: ExecInitPartitionInfo()'s handling of DO NOTHING actions
+BEGIN;
+TRUNCATE pa_target;
+MERGE INTO pa_target t
+  USING (VALUES (10, 100)) AS s(sid, delta)
+  ON t.tid = s.sid
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (1, 10, 'inserted by merge')
+  WHEN MATCHED THEN
+    DO NOTHING;
+SELECT * FROM pa_target ORDER BY tid, val;
+ROLLBACK;
+
 DROP TABLE pa_target CASCADE;
 
 -- The target table is partitioned in the same way, but this time by attaching

Original file line number	Diff line number	Diff line change
`@@ -3600,7 +3600,7 @@ ExecInitMerge(ModifyTableState mtstate, EState estate)`
`3600`	`3600`	`case CMD_NOTHING:`
`3601`	`3601`	`break;`
`3602`	`3602`	`default:`
`3603`		`- elog(ERROR, "unknown operation");`
	`3603`	`+ elog(ERROR, "unknown action in MERGE WHEN clause");`
`3604`	`3604`	`break;`
`3605`	`3605`	`}`
`3606`	`3606`	`}`