Re: MERGE vs REPLACE

On Wed, 2005-11-16 at 18:34 +0100, Martijn van Oosterhout wrote:
> On Wed, Nov 16, 2005 at 11:37:46AM -0500, Bruce Momjian wrote:
> >
> > Interesting approach. Actually, we could tell the user they have to use
> > BEGIN;LOCK tab before doing MERGE, and throw an error if we don't
> > already have a table lock.
>
> The bit I'm still missing is why there needs to be a lock at all. The
> SQL standard doesn't say anywhere that concurrent MERGE operations
> can't conflict. It seems to me that standard visibility rules apply. If
> neither MERGE statement can see the results of the other, then they
> will both INSERT. If you don't have a UNIQUE constraint to prevent this
> then what's the problem?
>
> It seems to me people would like, in the case of an existing UNIQUE
> constraint, to be able to use it to prevent "duplicate key" errors.
> This is nice, but the standard doesn't require that either.
>
> In other words, if we can use an index to avoid duplicate key errors,
> fine. But if there is no index available, it is not an error to do an
> INSERT because another INSERT was hidden from you.
>
> Conceptually, a MERGE statement is just a long string of INSERTs and
> UPDATEs in the same transaction and I think we should treat it as
> such.

Agreed.

Best Regards, Simon Riggs