Re: MERGE Specification

decibel(at)decibel(dot)org (Decibel!) writes:

> On Apr 22, 2008, at 1:17 PM, Gregory Stark wrote:
>> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
>>
>>> As I've said elsewhere, we could have it lock each row, its just more
>>> overhead if we do and not necessary at all for bulk data merging.
>>>
>>> I'll presume we want locking as an option, unless people say
>>> otherwise.
>>
>> It's not so simple. If you look for a row to merge into and don't
>> find one
>> there's no row to lock. What unique constraints do is hold the lock
>> on the
>> index page where the entry would have to be added.
>>
>> That's the trick that plpgsql cannot implement. That's why users
>> are forced to
>> loop and retry until they manage to do an update successfully or
>> insert
>> successfully.
>
> Yeah, hopefully there's a better way to do this other than row locks.
>
> But no matter how this is done, I think we need to handle the race
> conditions, and handle them by default. If people *really* know what
> they're doing, they can disable the row locking (perhaps one way to
> do this would be to grab an explicit lock on the table and have merge
> check for that...).

I agree that handling the race conditions by default is preferable.

Consider: An excellent reason to prefer MERGE is if it handles race
conditions that would otherwise require application code be more
carefully and cleverly written to avoid the race conditions.

If MERGE "solves it automatically," and eliminates hand-written code,
that's TWO benefits, that quite likely outweigh any performance costs.

I know we've had cases where race conditions [that a well-done MERGE
would probably solve easily] bit us badly, requiring considerable
development effort, and the addition of extra table columns and such.

There are some possibilities of "worst case" MERGE being "pretty
slow;" it's likely to be faster than the alternatives we used in its
absence ;-).
--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/wp.html
"I once went to a shrink. He told me to speak freely. I did. The
damn fool tried to charge me $90 an hour."
-- jimjr(at)qis(dot)net (Jim Moore Jr)