Re: [HACKERS] MERGE SQL Statement for PG11

On 29 January 2018 at 14:19, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

>> The concurrency rules are very simple:
>> If a MATCHED row is concurrently updated/deleted
>> 1. We run EvalPlanQual
>> 2. If the updated row is gone EPQ returns NULL slot or EPQ returns a
>> row with NULL values, then
>> {
>> if NOT MATCHED action exists, then raise ERROR
>> else continue to next row
>> }
>> else
>> re-check all MATCHED AND conditions and execute the first action
>> whose WHEN Condition evaluates to TRUE
>>
>>
>> This means MERGE will work just fine for "normal" UPDATEs, but it will
>> often fail (deterministically) in concurrent tests with mixed
>> insert/deletes or UPDATEs that touch the PK, as requested.
>
>
> can be nice to have part about differences between MERGE and INSERT ON
> CONFLICT DO

We've agreed not to attempt to make it do anything like INSERT ON
CONFLICT, so we don't need to discuss that here anymore.

MERGE can be semantically equivalent to an UPDATE join or a DELETE
join, and in those cases, MERGE behaves the same. It handles much more
complex cases also.

MERGE as submitted here follows all MVCC rules similar to an UPDATE
join. If it hits a problem with concurent activity it throws
ERROR: could not serialize access due to concurrent update
to make sure there is no ambiguity (as described directly above).

As we discussed earlier, removing some of those ERRORs and making it
do something useful instead may be possible later.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services