| 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 |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | ZizhuanLiu X-MAN | 2026-11-06 09:48:15 | Re: support create index on virtual generated column. |