Re: BUG #19111: Using EXPLAIN ANALYZE with MERGE causes failed assert

On Thu, Nov 13, 2025 at 9:02 PM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
>
> On Thu, 13 Nov 2025 at 12:18, PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> >
> > On PostgreSQL 17+ if you do the following:
> > ...
> > Then the backend for the second psql crashes. With asserts turned on.
>
> Thanks for the report.
>
> What's happening here is that the MERGE in the second query has both
> NOT MATCHED BY SOURCE and NOT MATCHED BY TARGET actions, so it does a
> full join between the two tables. Initially there is a single matched
> row, but the concurrent update turns that into a not matched pair of
> rows and both actions are executed. So the ModifyTable node processes
> it as 2 rows, whereas its parent node only outputs 1 row, which is
> something the explain code doesn't like (because it computes the
> difference, interpreting that as the number of rows skipped).
>
> A possible solution would be something like the attached. It feels a
> little ugly, but I don't see any other easy fix.
>
> It's only a rough patch (it should have an isolation test case), but
> it fixes the problem by causing the parent (full join) node to report
> that it returned 2 rows, which it didn't really, but it would have
> done, if the other update had happened before the MERGE, rather than
> concurrently.

IMHO it makes sense to make a full join node to report 2 rows because
if you see internally merge is making the behavior as if it would have
returned 2 rows by parent [1], so I think it's right to fix the
instrument to report that, otherwise the plan might look confusing.
OTOH, someone might argue that we should just show in instrument what
really happened that the parent returned just 1 row and then it got
converted to 2 actions, and for doing that we may just remove the
assert.

I have attached an isolation test for the same.

[1]
if (!matched)
{
/*
* If a concurrent update turned a MATCHED case into a NOT MATCHED
* case, and we have both WHEN NOT MATCHED BY SOURCE and WHEN NOT
* MATCHED [BY TARGET] actions, and there is a RETURNING clause,
* ExecMergeMatched() may have already executed a WHEN NOT MATCHED BY
* SOURCE action, and computed the row to return. If so, we cannot
* execute a WHEN NOT MATCHED [BY TARGET] action now, so mark it as
* pending (to be processed on the next call to ExecModifyTable()).
* Otherwise, just process the action now.
*/
if (rslot == NULL)
rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag);
else
context->mtstate->mt_merge_pending_not_matched = context->planSlot;
}

--
Regards,
Dilip Kumar
Google