Re: MERGE vs REPLACE

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.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.