| From: | Joao Rito <joaovictor(dot)rito(at)gmail(dot)com> |
|---|---|
| To: | Tender Wang <tndrwang(at)gmail(dot)com> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: EXPLAIN ANALYZE overcounts MERGE inserts suppressed by triggers |
| Date: | 2026-11-18 19:47:41 |
| Message-ID: | 20261118194741.1157-1-joaovictor.rito@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tristan Partin | 2026-11-18 19:56:22 | Re: Add ids to catalog/view columns in documentation |
| Previous Message | Joao Rito | 2026-11-18 19:47:02 | Re: MERGE fails to reject source references in NOT MATCHED BY SOURCE actions |