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(at)2ndquadrant(dot)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