EXPLAIN ANALYZE overcounts MERGE inserts suppressed by triggers

From: Tender Wang <tndrwang(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: EXPLAIN ANALYZE overcounts MERGE inserts suppressed by triggers
Date: 2026-11-06 09:52:56
Message-ID: CAHewXNmZAG1i0PkRy+b3koucghYn-ApBrYmtCgXDzYbRESA6Kg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Fix-MERGE-insert-counts-when-a-trigger-suppresses-in.patch text/plain 4.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Shlok Kyal 2026-11-06 10:04:25 Re: BUG? Unpublished generated columns are reported "missing" by the subscription
Previous Message ZizhuanLiu X-MAN 2026-11-06 09:48:15 Re: support create index on virtual generated column.