Re: MERGE vs REPLACE

On Wed, Nov 16, 2005 at 12:59:36PM -0500, Bruce Momjian wrote:
> Martijn van Oosterhout wrote:
> > 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?
>
> I assume they want MERGE because they don't want duplicates.  If they
> don't care, they would have used INSERT.

The whole point of MERGE was because you can do different things on
INSERT or UPDATE. If you didn't care about the UPDATE case you could
indeed do just inserts...

I just don't think we should tie ourselves in knots over details that
the spec doesn't require. If the spec doesn't require "predicate
locking" then why on earth should we provide it if it's so difficult?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.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.