> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Simon Riggs > Sent: Wednesday, November 16, 2005 10:35 AM > To: Martijn van Oosterhout > Cc: Bruce Momjian; Rick Gigger; Tom Lane; Christopher Kings-Lynne; Jim C. > Nasby; josh@agliodbs.com; pgsql-hackers@postgresql.org; Jaime Casanova; > Peter Eisentraut > Subject: Re: [HACKERS] 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.
Merge could also be considered as a long string of deletes and inserts. I guess that deleting those records that already exist and then inserting all of the records is faster because it could be done like a single join to perform the delete and then a single batch insert. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly