Re: MERGE Specification

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'll presume we want locking as an option, unless people say otherwise.

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