Re: MERGE Specification

On Tue, Apr 22, 2008 at 08:24:58AM +0100, Simon Riggs wrote:
> The way MERGE works we first test to see if it matches or not, then if
> not matched we would activate the NOT MATCHED action, which standard
> says must be an insert. The gap between the two actions allows a race
> condition to exist.
>
> We could close the gap by taking a lock on the row when we perform the
> is-matched test, but that would be expensive for bulk operations. ISTM
> the lock should be optional. Not sure what the default should be. Input
> welcome.

ISTM that if the original select does a SELECT FOR UPDATE then it
should work fine for UPDATEs since any update with overwrite the xmax
field anyway.

What you can't do is prevent multiple inserts. Though if its a unique
index you should be able to do the same trick as normal inserts: create
the row, try to insert into the index and if that fails fall back to
doing an update.

What you really need for this though is a non-fatal _bt_check_unique so
you can recover without having a savepoint for every row.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.