Re: MERGE Specification

On Mon, 2008-04-21 at 21:57 -0400, Alvaro Herrera wrote:
> Simon Riggs wrote:
>
> > Unrelated to rule processing, you did read the bit about MERGE and race
> > conditions? ISTM that MERGE as it stands isn't very useful for anything
> > other than large data loads since its going to cause problems if used
> > concurrently.
>
> But that's how the committee designed it, yes?

Yes. Not sure if I see your point there, but yes, that's how its been
designed.

Both DB2 and Oracle have additional items to get around the shortcomings
of the command.

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.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com