Re: MERGE Specification

On Apr 22, 2008, at 1:47 PM, Simon Riggs wrote:

> On Mon, 2008-04-21 at 22:27 -0400, Gregory Stark wrote:
>> "Simon Riggs" <simon@2ndquadrant.com> writes:
>>
>>> 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.
>>
>> If there are race conditions what advantage does it offer over  
>> writing plpgsql
>> or client code to do it?
>
> That's an excellent question. I'm not trying to sell you anything  
> here.
> MERGE is a SQL Standard command, supported by Oracle, DB2, SQLServer
> etc, so there is reason enough to implement it.
>
> There may be also reasons to implement other syntaxes, other  
> behaviours,
> which would be non-standard. If people want the latter first/second/ 
> not
> at all then please speak, its not an either-or situation.
>
> I would expect MERGE to be slightly faster than a well coded PL/pgSQL
> function, but there won't be too much in it. It will allow the  
> statement
> to be more easily parallelised in the form it currently takes, I would
> note.
>
>> I thought the whole advantage of having a built-in command is that  
>> it could do
>> the kind of locking our unique constraints do to avoid race  
>> conditions.
>
> 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 was hoping to use MERGE alongside the other standard DML. Its  
purpose is to set the "truth" regardless of past states.

Why should it be relegated to the bulk-loading basement alongside COPY?

Cheers,
M