Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Mar 2, 2018 at 12:36 AM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

>
> > Our regular UPDATE .. FROM does not do that
> > either. Given that, it seems better to just throw an error (even when no
> NOT
> > MATCHED action exists) and explain to the users that MERGE will work as
> long
> > as concurrent updates don't modify the columns used in the join
> condition.
> > Concurrent deletes should be fine and we may actually even invoke WHEN
> NOT
> > MATCHED action in that case.
>
> Again, I have to ask: is such an UPDATE actually meaningfully
> different from a concurrent DELETE + INSERT? If so, why is a special
> error better than a dup violation, or maybe even having the INSERT
> (and whole MERGE statement) succeed?
>
>
Ok, I agree. I have updated the patch to remove the serialization error. If
MATCHED changes to NOT MATCHED because of concurrent update/delete, we now
simply retry from the top and execute the first NOT MATCHED action, if WHEN
AND qual passes on the updated version. Of course, if the MERGE does not
contain any NOT MATCHED action then we simply ignore the target row and
move to the next row. Since a NOT MATCHED case can never turn into a
MATCHED case, there is no risk of a live lock.

I've updated the documentation and the test cases to reflect this change.

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services