Re: MERGE vs REPLACE

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-
> owner@postgresql.org] 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.