Hi,
EXPLAIN ANALYZE reports a row as inserted when a BEFORE INSERT trigger
suppresses a MERGE insert. Here's a reproducer:
CREATE TABLE merge_count_target (id int);
CREATE FUNCTION skip_insert() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RETURN NULL;
END;
$$;
CREATE TRIGGER skip_insert BEFORE INSERT ON merge_count_target
FOR EACH ROW EXECUTE FUNCTION skip_insert();
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON)
MERGE INTO merge_count_target t
USING (VALUES (1)) AS s(id)
ON t.id = s.id
WHEN NOT MATCHED THEN INSERT VALUES (s.id);
SELECT count(*) FROM merge_count_target;
The target table remains empty, but the plan reports `Tuples Inserted:
1` and `Tuples Skipped: 0`. The expected counts are 0 inserted and 1
skipped.
ExecMergeNotMatched() increments mt_merge_inserted unconditionally
after calling ExecInsert().
The caller cannot infer from the returned slot whether the insert happened.
ExecInsert() returns NULL both when a trigger suppresses the insert
and when an insert succeeds without a RETURNING clause.
The attached patch moves the increment into ExecInsert(), where the
control flow already distinguishes a skipped row from one that was
inserted or queued for FDW batch insertion.
It checks the current MERGE action so that the physical insert
performed by a cross-partition UPDATE is counted only as an UPDATE.
The patch also extends the existing trigger test in merge.sql to check
the inserted and skipped counts.
--
Thanks,
Tender Wang
From 9b820630c522d3d8beebe647f0410e88c4b8730f Mon Sep 17 00:00:00 2001
From: Tender Wang <[email protected]>
Date: Fri, 6 Nov 2026 17:38:33 +0800
Subject: [PATCH] Fix MERGE insert counts when a trigger suppresses insertion.
When a BEFORE INSERT trigger returns NULL, ExecInsert() skips the
row, but its MERGE caller still increments mt_merge_inserted.
EXPLAIN ANALYZE consequently reports the row as inserted instead
of skipped.
Count MERGE INSERT actions in ExecInsert() after the row is inserted
or queued for FDW batch insertion. Check the current MERGE action
so that the insert half of a cross-partition UPDATE is excluded
from the INSERT count.
---
src/backend/executor/nodeModifyTable.c | 9 ++++++++-
src/test/regress/expected/merge.out | 20 ++++++++++++++++++++
src/test/regress/sql/merge.sql | 17 +++++++++++++++++
3 files changed, 45 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeModifyTable.c
b/src/backend/executor/nodeModifyTable.c
index 558ceece9bb..62cf06ae4a0 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -879,6 +879,8 @@ ExecInsert(ModifyTableContext *context,
OnConflictAction onconflict = node->onConflictAction;
PartitionTupleRouting *proute = mtstate->mt_partition_tuple_routing;
MemoryContext oldContext;
+ bool merge_insert = mtstate->operation == CMD_MERGE &&
+ mtstate->mt_merge_action->mas_action->commandType == CMD_INSERT;
/*
* If the input result relation is a partitioned table, find the leaf
@@ -1033,6 +1035,10 @@ ExecInsert(ModifyTableContext *context,
resultRelInfo->ri_NumSlots++;
+ /* Count MERGE inserts queued for FDW batching. */
+ if (merge_insert)
+ mtstate->mt_merge_inserted++;
+
MemoryContextSwitchTo(oldContext);
return NULL;
@@ -1277,6 +1283,8 @@ ExecInsert(ModifyTableContext *context,
if (canSetTag)
(estate->es_processed)++;
+ if (merge_insert)
+ mtstate->mt_merge_inserted++;
/*
* If this insert is the result of a partition key update that moved the
@@ -3895,7 +3903,6 @@ ExecMergeNotMatched(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
rslot = ExecInsert(context,
mtstate->rootResultRelInfo,
newslot,
canSetTag, NULL, NULL);
- mtstate->mt_merge_inserted += 1;
break;
case CMD_NOTHING:
/* Do nothing */
diff --git a/src/test/regress/expected/merge.out
b/src/test/regress/expected/merge.out
index 9cb1d87066a..0a1a181f4ee 100644
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -1084,6 +1084,26 @@ SELECT * FROM target FULL OUTER JOIN source ON (sid =
tid);
1 | 10 | |
(4 rows)
+-- A suppressed INSERT must be counted as skipped by EXPLAIN ANALYZE.
+DO $$
+DECLARE
+ plan json;
+BEGIN
+ EXECUTE $explain$
+ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON)
+ MERGE INTO target t USING (VALUES (4, 40)) AS s(sid, delta)
+ ON t.tid = s.sid
+ WHEN NOT MATCHED THEN INSERT VALUES (s.sid, s.delta)
+ $explain$ INTO plan;
+ RAISE NOTICE 'inserted = %, skipped = %',
+ plan->0->'Plan'->>'Tuples Inserted',
+ plan->0->'Plan'->>'Tuples Skipped';
+END;
+$$;
+NOTICE: BEFORE INSERT STATEMENT trigger
+NOTICE: BEFORE INSERT ROW trigger row: (4,40)
+NOTICE: AFTER INSERT STATEMENT trigger
+NOTICE: inserted = 0, skipped = 1
DROP TRIGGER merge_skip ON target;
DROP FUNCTION skip_merge_op();
-- test from PL/pgSQL
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
index 2660b19f238..20710805e36 100644
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -713,6 +713,23 @@ RAISE NOTICE 'ROW_COUNT = %', result;
END;
$$;
SELECT * FROM target FULL OUTER JOIN source ON (sid = tid);
+
+-- A suppressed INSERT must be counted as skipped by EXPLAIN ANALYZE.
+DO $$
+DECLARE
+ plan json;
+BEGIN
+ EXECUTE $explain$
+ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON)
+ MERGE INTO target t USING (VALUES (4, 40)) AS s(sid, delta)
+ ON t.tid = s.sid
+ WHEN NOT MATCHED THEN INSERT VALUES (s.sid, s.delta)
+ $explain$ INTO plan;
+ RAISE NOTICE 'inserted = %, skipped = %',
+ plan->0->'Plan'->>'Tuples Inserted',
+ plan->0->'Plan'->>'Tuples Skipped';
+END;
+$$;
DROP TRIGGER merge_skip ON target;
DROP FUNCTION skip_merge_op();
--
2.43.0
