EXPLAIN ANALYZE overcounts MERGE inserts suppressed by triggers
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
Hi Tender,
Thanks for the patch. I reviewed v1
(0001-Fix-MERGE-insert-counts-when-a-trigger-suppresses-in.patch).
Setup: current master (c074737f68), built with --enable-cassert
--enable-debug, once without and once with the patch. The patch applies
cleanly with git apply.
What I tested, with the same SQL on both builds:
1. Your reproducer (BEFORE INSERT ROW trigger returning NULL). Master
reports "Tuples Inserted": 1 and "Tuples Skipped": 0 while the table
stays empty. With the patch it reports 0 inserted and 1 skipped. The
text format changes from "Tuples: inserted=2" to "Tuples: skipped=2" for
two source rows. The MERGE command tag was "MERGE 0" in both cases.
2. A trigger that suppresses only odd ids, four source rows. Master:
inserted=4. Patched: inserted=2 skipped=2, which matches the rows really
present in the table.
3. No trigger on the target (plain inserts, and a mix of DELETE, UPDATE
and INSERT actions, and a WHEN NOT MATCHED AND <cond> action). The
EXPLAIN output is identical on master and patched.
4. UPDATE and DELETE actions suppressed by BEFORE ROW triggers: both
builds already report skipped=1, so the patch does not need to change
anything there.
5. Partitioned target, BEFORE INSERT trigger returning NULL on one leaf:
with ids 1 (suppressed leaf) and 11 (normal leaf), master says
inserted=2, patched says inserted=1 skipped=1.
6. Cross-partition UPDATE through MERGE: counted as updated only, same
as master (so the check on the current MERGE action works as intended).
7. INSTEAD OF INSERT trigger on a view used as MERGE target: when the
trigger returns NEW, both builds report inserted=1. When it returns
NULL, master says inserted=1 and the patch says skipped=1. This looks
like the same bug and the patch fixes it too.
8. MERGE ... RETURNING with a suppressed insert: master inserted=1
skipped=1, patched skipped=2 (one row was suppressed, the other matched
an existing row with no action).
9. make check on the patched tree: all 243 tests passed.
A few comments on the patch itself:
a) The new count in the FDW batching branch of ExecInsert() looks
unreachable for MERGE. MERGE on a foreign table (also through a foreign
partition) fails with 'cannot execute MERGE on relation "pt_f"' (I tried
it with postgres_fdw and batch_size 10, on both builds). ri_BatchSize is
only set under "operation == CMD_INSERT" in ExecInitModifyTable(). So
merge_insert is always false in that branch. It is harmless, but maybe
the comment and the increment could be dropped there, or a short comment
could say why it is there.
b) The cross-partition UPDATE case: when the destination leaf has a
BEFORE INSERT trigger returning NULL, MERGE reports updated=1 on both
master and patched (the row is deleted from the old partition and not
inserted). That is not caused by this patch and I think it is out of
scope here. I only mention it because the counts are not correct for
that case either.
c) Test: the new DO block extracts the counts from the JSON plan and
prints a NOTICE. That works, but merge.sql already has the
explain_merge() helper (defined later in the file), which would give the
short text form "Tuples: skipped=1" without the PL/pgSQL wrapper and the
extra trigger NOTICE lines. It may be tidier to add the case near the
other explain_merge() tests with its own small trigger.
d) Minor: the test only covers the plain table case. A case with an
INSTEAD OF trigger on a view, or a partitioned target, would cover the
other paths that go through ExecInsert().
Overall the patch looks correct to me and fixes the cases above. From
reading the patch, moving the increment into ExecInsert() seems to keep
the two counters consistent, but I did not verify that by testing.
What I did not test: I did not run the exact new merge.sql test against
an unpatched build (I used equivalent queries instead, items 1 and 2),
and I did not test concurrent-update (EvalPlanQual) paths or MERGE with
transition tables.
Regards,
Joao Rito