Re: MERGE vs REPLACE

Well, from my point of view it is more than delete and insert. That I
can do right now with existing infrastructure. The problem I try to
solve is something along: a bunch of clients try to update a count, and
ONE of them must initialize the count if it does not exist... this can't
be done with current infrastructure without race conditions.
Our current solution is to initialize all the possible counts
beforehand, but that suboptimal as only a few of them will actually have
data coming in later...
And of course the import problem... we can have multiple concurrent
imports, which must insert just once per some unique criteria, and
update if the record is already there, and all this in a batch. This is
also not possible without race conditions or aggressive locking.
So for me the atomic, consistent and without performance penalties
"update_or_insert_based_on_unique_criteria" does have a value, and
that's coming exactly from the locking of the unique index which
eliminates the race condition from this operation. I don't care about
syntax sugar, just about things I could do more efficiently if this
mechanism were in place...

Cheers,
Csaba.

On Wed, 2005-11-16 at 20:33, Martijn van Oosterhout wrote:
> On Wed, Nov 16, 2005 at 11:06:15AM -0800, Dann Corbit wrote:
> > > -----Original Message-----
> > > From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> > > owner(at)postgresql(dot)org] On Behalf Of Simon Riggs
> > > > 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.
>
> And for us it makes no difference because in MVCC, UPDATE == DELETE +
> INSERT. IMHO it's just a nice construct to specify UPDATEs and INSERTs
> in the same statement.
>
> Have a nice day,