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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Wed, Nov 16, 2005 at 11:06:15AM -0800, Dann Corbit wrote:
> > -----Original Message-----
> > From: pgsql-hackers-owner@postgresql.org [mailtogsql-hackers-
> > owner@postgresql.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,
--
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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDe4mAIB7bNG8LQkwRAj3LAJ4naUMvVqqay+gUnZKXWO HSPtfVgACePhOi
ZpUDsKTcBjiFyDEVrPbisYM=
=qIWX
-----END PGP SIGNATURE-----

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@postgresql.org [mailtogsql-hackers-
> > > owner@postgresql.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,


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings