On Fri, Mar 2, 2018 at 12:36 AM, Peter Geoghegan <pg@bowt.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.