Re: MERGE Specification

On Apr 22, 2008, at 1:17 PM, Gregory Stark wrote:
> "Simon Riggs" <simon@2ndquadrant.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...).

On a different note, if you intend for the SGML to become the doc  
page for MERGE, I'd really like to see some more complex examples  
showing both delete and more than 2 WHEN cases. Something like the  
"multiple actions on single target row" test case would work.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828