From kleptog@svana.org Thu Nov 17 07:33:25 2005
Return-path: <kleptog@svana.org>
Received: from svana.org (mail@svana.org [203.20.62.76])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAHCXNu09324
	for <pgman@candle.pha.pa.us>; Thu, 17 Nov 2005 07:33:25 -0500 (EST)
Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian))
	id 1Eciwk-0007Gd-00; Thu, 17 Nov 2005 23:32:58 +1100
Date: Thu, 17 Nov 2005 13:32:57 +0100
From: Martijn van Oosterhout <kleptog@svana.org>
To: Csaba Nagy <nagy@ecircle-ag.com>
cc: Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Dann Corbit <DCorbit@connx.com>, Simon Riggs <simon@2ndquadrant.com>,
   Bruce Momjian <pgman@candle.pha.pa.us>,
   Rick Gigger <rick@alpinenetworking.com>, Tom Lane <tgl@sss.pgh.pa.us>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
Subject: Re: [HACKERS] MERGE vs REPLACE
Message-ID: <20051117123250.GC22933@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="raC6veAxrt5nqIoY"
Content-Disposition: inline
In-Reply-To: <1132228373.10890.313.camel@coppola.muc.ecircle.de>
User-Agent: Mutt/1.3.28i
X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6
X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522  48A7 6394 F08A 0DC6 7BE6
X-PGP-Key-URL: <http://svana.org/kleptog/0DC67BE6.pgp.asc>
Status: OR


--raC6veAxrt5nqIoY
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Thu, Nov 17, 2005 at 12:52:53PM +0100, Csaba Nagy wrote:
> Yes, these algorithms are clear to me, but they don't work for batch
> updates in postgres without savepoints before each row insert/update,
> which is not good for performance (not to mention on older postgres
> versions without savepoint support it won't work at all). If there is a
> way of no race condition, no performance penalty, that would be
> something new and useful. I just guess the MERGE would provide that.

Well, then you guess wrong. This isn't what MERGE is for. MERGE is just
a neat way of specifying the UPDATE and INSERT cases in the same
statement. It doesn't remove the possibility duplicate inserts and thus
primary key violations.

If someone wants to make extensions to MERGE so that it can avoid the
race condition and avoid the duplicate key violations, that's fine. But
be aware that this is outside of the spec. It may be a useful addition,
but perhaps we should consider MERGE and REPLACE as completely seperate
targets.

MERGE has a whole join construction with subqueries that would be a
pain to make work in a way that is truly serialisable. REPLACE deals
with only one row and tries to solve the race for that case only. Much
easier to consider them seperately, no?

I guess what's really irritating is that this clearly exposes the case
listed in the docs as "Why SERIALIZABLE isn't in all cases". If we
could solve that for MERGE, we could probably solve it in the general
case too.

Have a nice day,
--=20
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.

--raC6veAxrt5nqIoY
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFDfHhxIB7bNG8LQkwRAhIwAJwPmzE2GHrqzPujkkj2I5r6OlVo5QCeN4st
Ka50Vh0AnXuj4pBt27V6j7I=
=7rb7
-----END PGP SIGNATURE-----

--raC6veAxrt5nqIoY--

From pgsql-hackers-owner+M76186@postgresql.org Thu Nov 17 09:44:46 2005
Return-path: <pgsql-hackers-owner+M76186@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAHEihu07081
	for <pgman@candle.pha.pa.us>; Thu, 17 Nov 2005 09:44:44 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 84700C4B33D;
	Thu, 17 Nov 2005 14:44:42 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 84C6BD967F
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Thu, 17 Nov 2005 10:41:57 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 47040-01
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Nov 2005 14:42:00 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id ECB3CD963C
	for <pgsql-hackers@postgresql.org>; Thu, 17 Nov 2005 10:41:53 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jAHEfAuK025850;
	Thu, 17 Nov 2005 09:41:11 -0500 (EST)
To: Csaba Nagy <nagy@ecircle-ag.com>
cc: Martijn van Oosterhout <kleptog@svana.org>,
   Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Dann Corbit <DCorbit@connx.com>, Simon Riggs <simon@2ndquadrant.com>,
   Bruce Momjian <pgman@candle.pha.pa.us>,
   Rick Gigger <rick@alpinenetworking.com>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
Subject: Re: [HACKERS] MERGE vs REPLACE 
In-Reply-To: <1132231474.10890.317.camel@coppola.muc.ecircle.de> 
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de>
Comments: In-reply-to Csaba Nagy <nagy@ecircle-ag.com>
	message dated "Thu, 17 Nov 2005 13:44:35 +0100"
Date: Thu, 17 Nov 2005 09:41:10 -0500
Message-ID: <25849.1132238470@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.008 required=5 tests=[AWL=0.008]
X-Spam-Score: 0.008
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: ORr

Csaba Nagy <nagy@ecircle-ag.com> writes:
> OK, in this case I don't care about either MERGE or REPLACE, but for an
> UPSERT which does the locking :-)

This is exactly the point --- pretty much nobody has come to us and
asked for a feature that does what Peter and Martijn say MERGE does.
(I haven't bothered to look at the 2003 spec, I'm assuming they read it
correctly.)  What we *have* been asked for, over and over, is an
insert-or-update feature that's not so tedious and inefficient as the
savepoint-insert-rollback-update kluge.  That's what we ought to be
concentrating on providing.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

From pgsql-hackers-owner+M76189@postgresql.org Thu Nov 17 10:17:57 2005
Return-path: <pgsql-hackers-owner+M76189@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAHFHvu12126
	for <pgman@candle.pha.pa.us>; Thu, 17 Nov 2005 10:17:57 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 9B0AFC4B33D;
	Thu, 17 Nov 2005 15:17:55 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 02FEFDB988
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Thu, 17 Nov 2005 11:14:52 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 48136-10
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Nov 2005 15:14:56 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25])
	by svr1.postgresql.org (Postfix) with ESMTP id 34BD1DB97C
	for <pgsql-hackers@postgresql.org>; Thu, 17 Nov 2005 11:14:50 -0400 (AST)
Received: from ns.snowman.net (ns.snowman.net [66.92.160.21])
	by svr2.postgresql.org (Postfix) with ESMTP id A8A8FF0BEF
	for <pgsql-hackers@postgresql.org>; Thu, 17 Nov 2005 15:14:54 +0000 (GMT)
Received: by ns.snowman.net (Postfix, from userid 1000)
	id EDB5717AD6; Thu, 17 Nov 2005 10:15:30 -0500 (EST)
Date: Thu, 17 Nov 2005 10:15:30 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Csaba Nagy <nagy@ecircle-ag.com>,
   Martijn van Oosterhout <kleptog@svana.org>,
   Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Dann Corbit <DCorbit@connx.com>, Simon Riggs <simon@2ndquadrant.com>,
   Bruce Momjian <pgman@candle.pha.pa.us>,
   Rick Gigger <rick@alpinenetworking.com>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
Subject: Re: [HACKERS] MERGE vs REPLACE
Message-ID: <20051117151530.GU6026@ns.snowman.net>
Mail-Followup-To: Tom Lane <tgl@sss.pgh.pa.us>,
	Csaba Nagy <nagy@ecircle-ag.com>,
	Martijn van Oosterhout <kleptog@svana.org>,
	Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
	Dann Corbit <DCorbit@connx.com>, Simon Riggs <simon@2ndquadrant.com>,
	Bruce Momjian <pgman@candle.pha.pa.us>,
	Rick Gigger <rick@alpinenetworking.com>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	"Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
	pgsql-hackers@postgresql.org,
	Jaime Casanova <systemguards@gmail.com>,
	Peter Eisentraut <peter_e@gmx.net>
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de> <25849.1132238470@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="aAYbr14jHAy2Yyau"
Content-Disposition: inline
In-Reply-To: <25849.1132238470@sss.pgh.pa.us>
X-Editor: Vim http://www.vim.org/
X-Info: http://www.snowman.net
X-Operating-System: Linux/2.4.24ns.3.0 (i686)
X-Uptime: 10:01:46 up 159 days,  7:15,  2 users,  load average: 0.31, 0.23, 0.13
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


--aAYbr14jHAy2Yyau
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> This is exactly the point --- pretty much nobody has come to us and
> asked for a feature that does what Peter and Martijn say MERGE does.
> (I haven't bothered to look at the 2003 spec, I'm assuming they read it
> correctly.)  What we *have* been asked for, over and over, is an
> insert-or-update feature that's not so tedious and inefficient as the
> savepoint-insert-rollback-update kluge.  That's what we ought to be
> concentrating on providing.

I guess to be clear on what this distinction actually is, specifically:
MERGE under SQL2003 doesn't appear to be intended to be used
concurrently.  For data warehousing situations this can be just fine
such as in my case where I get a monthly update of some information and
need to merge that update in with the prior information.  In this case
there's only one MERGE running and I'd hope it'd be faster than doing
check for existance, insert/update on each row in plpgsql or something
(since there'd be multiple index lookups, etc, I think).  Concurrent
MERGEs running *can* fail, just like whole transactions which do the
check/insert/update can fail.

REPLACE/INSERT ON DUPLICATE UPDATE appears to essentially be a
transaction which is supposed to not fail but instead do locking to
ensure that it doesn't fail.  This requires predicate locking to be
efficient because you want to tell the concurrent transaction "if you
have the same key as me, just wait a second and you can do an update
'cause I'm going to create the key if it doesn't exist before I'm done".

I think REPLACE/INSERT ON DUPLICATE UPDATE is definitely harder to do
than MERGE because of the idea that it isn't supposed to fail generally.
I think SQL2003 MERGE would be reasonably easy to do and to get the
efficiency benefits out of it (assuming there are some to be had in the
end). =20

I don't think MERGE can really be made to be both though, in which case
it should really be the SQL2003 MERGE and we can make REPLACE/INSERT ON
DUPLICATE UPDATE something else.  Perhaps a special form of MERGE where
you know it's going to be doing that locking.  I really don't like the
idea of making the SQL2003 version of MERGE be the MERGE special case
(by requiring someone to take a table lock ahead of time or do something
else odd).

	Thanks,

		Stephen

--aAYbr14jHAy2Yyau
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDfJ6SrzgMPqB3kigRAjXWAJ9R/50PoocURxvi74g7dwhIO4akgQCcDEDG
4hGZAVR/9Age8pFtEOp4kfo=
=F91e
-----END PGP SIGNATURE-----

--aAYbr14jHAy2Yyau--

From sfrost@snowman.net Thu Nov 17 10:14:59 2005
Return-path: <sfrost@snowman.net>
Received: from ns.snowman.net (ns.snowman.net [66.92.160.21])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAHFEwu11635
	for <pgman@candle.pha.pa.us>; Thu, 17 Nov 2005 10:14:58 -0500 (EST)
Received: by ns.snowman.net (Postfix, from userid 1000)
	id EDB5717AD6; Thu, 17 Nov 2005 10:15:30 -0500 (EST)
Date: Thu, 17 Nov 2005 10:15:30 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Csaba Nagy <nagy@ecircle-ag.com>,
   Martijn van Oosterhout <kleptog@svana.org>,
   Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Dann Corbit <DCorbit@connx.com>, Simon Riggs <simon@2ndquadrant.com>,
   Bruce Momjian <pgman@candle.pha.pa.us>,
   Rick Gigger <rick@alpinenetworking.com>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
Subject: Re: [HACKERS] MERGE vs REPLACE
Message-ID: <20051117151530.GU6026@ns.snowman.net>
Mail-Followup-To: Tom Lane <tgl@sss.pgh.pa.us>,
	Csaba Nagy <nagy@ecircle-ag.com>,
	Martijn van Oosterhout <kleptog@svana.org>,
	Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
	Dann Corbit <DCorbit@connx.com>,
	Simon Riggs <simon@2ndquadrant.com>,
	Bruce Momjian <pgman@candle.pha.pa.us>,
	Rick Gigger <rick@alpinenetworking.com>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	"Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
	pgsql-hackers@postgresql.org,
	Jaime Casanova <systemguards@gmail.com>,
	Peter Eisentraut <peter_e@gmx.net>
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de> <25849.1132238470@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="aAYbr14jHAy2Yyau"
Content-Disposition: inline
In-Reply-To: <25849.1132238470@sss.pgh.pa.us>
X-Editor: Vim http://www.vim.org/
X-Info: http://www.snowman.net
X-Operating-System: Linux/2.4.24ns.3.0 (i686)
X-Uptime: 10:01:46 up 159 days,  7:15,  2 users,  load average: 0.31, 0.23, 0.13
User-Agent: Mutt/1.5.9i
Status: OR


--aAYbr14jHAy2Yyau
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> This is exactly the point --- pretty much nobody has come to us and
> asked for a feature that does what Peter and Martijn say MERGE does.
> (I haven't bothered to look at the 2003 spec, I'm assuming they read it
> correctly.)  What we *have* been asked for, over and over, is an
> insert-or-update feature that's not so tedious and inefficient as the
> savepoint-insert-rollback-update kluge.  That's what we ought to be
> concentrating on providing.

I guess to be clear on what this distinction actually is, specifically:
MERGE under SQL2003 doesn't appear to be intended to be used
concurrently.  For data warehousing situations this can be just fine
such as in my case where I get a monthly update of some information and
need to merge that update in with the prior information.  In this case
there's only one MERGE running and I'd hope it'd be faster than doing
check for existance, insert/update on each row in plpgsql or something
(since there'd be multiple index lookups, etc, I think).  Concurrent
MERGEs running *can* fail, just like whole transactions which do the
check/insert/update can fail.

REPLACE/INSERT ON DUPLICATE UPDATE appears to essentially be a
transaction which is supposed to not fail but instead do locking to
ensure that it doesn't fail.  This requires predicate locking to be
efficient because you want to tell the concurrent transaction "if you
have the same key as me, just wait a second and you can do an update
'cause I'm going to create the key if it doesn't exist before I'm done".

I think REPLACE/INSERT ON DUPLICATE UPDATE is definitely harder to do
than MERGE because of the idea that it isn't supposed to fail generally.
I think SQL2003 MERGE would be reasonably easy to do and to get the
efficiency benefits out of it (assuming there are some to be had in the
end). =20

I don't think MERGE can really be made to be both though, in which case
it should really be the SQL2003 MERGE and we can make REPLACE/INSERT ON
DUPLICATE UPDATE something else.  Perhaps a special form of MERGE where
you know it's going to be doing that locking.  I really don't like the
idea of making the SQL2003 version of MERGE be the MERGE special case
(by requiring someone to take a table lock ahead of time or do something
else odd).

	Thanks,

		Stephen

--aAYbr14jHAy2Yyau
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQFDfJ6SrzgMPqB3kigRAjXWAJ9R/50PoocURxvi74g7dwhIO4akgQCcDEDG
4hGZAVR/9Age8pFtEOp4kfo=
=F91e
-----END PGP SIGNATURE-----

--aAYbr14jHAy2Yyau--

From pgsql-hackers-owner+M76234@postgresql.org Thu Nov 17 22:19:04 2005
Return-path: <pgsql-hackers-owner+M76234@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAI3J3O11471
	for <pgman@candle.pha.pa.us>; Thu, 17 Nov 2005 22:19:04 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id CBDDBC4B337;
	Fri, 18 Nov 2005 03:18:59 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id BB822DB600
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Thu, 17 Nov 2005 23:12:08 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 30987-04
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Nov 2005 03:12:11 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126])
	by svr1.postgresql.org (Postfix) with ESMTP id 7CB16DB466
	for <pgsql-hackers@postgresql.org>; Thu, 17 Nov 2005 23:12:05 -0400 (AST)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.11.6) id jAI3BXS10887;
	Thu, 17 Nov 2005 22:11:33 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-ID: <200511180311.jAI3BXS10887@candle.pha.pa.us>
Subject: Re: [HACKERS] MERGE vs REPLACE
In-Reply-To: <25849.1132238470@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 17 Nov 2005 22:11:33 -0500 (EST)
cc: Csaba Nagy <nagy@ecircle-ag.com>,
   Martijn van Oosterhout <kleptog@svana.org>,
   Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Dann Corbit <DCorbit@connx.com>, Simon Riggs <simon@2ndquadrant.com>,
   Rick Gigger <rick@alpinenetworking.com>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
X-Mailer: ELM [version 2.4ME+ PL121 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.036 required=5 tests=[AWL=0.036]
X-Spam-Score: 0.036
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Tom Lane wrote:
> Csaba Nagy <nagy@ecircle-ag.com> writes:
> > OK, in this case I don't care about either MERGE or REPLACE, but for an
> > UPSERT which does the locking :-)
> 
> This is exactly the point --- pretty much nobody has come to us and
> asked for a feature that does what Peter and Martijn say MERGE does.
> (I haven't bothered to look at the 2003 spec, I'm assuming they read it
> correctly.)  What we *have* been asked for, over and over, is an
> insert-or-update feature that's not so tedious and inefficient as the
> savepoint-insert-rollback-update kluge.  That's what we ought to be
> concentrating on providing.

I am confused over the various options.  I have heard these syntaxes:

	SQL2003 MERGE
	MySQL REPLACE
		http://dev.mysql.com/doc/refman/5.1/en/replace.html
	MySQL INSERT VIOLATION ...
	UPSERT

So it seems MERGE does not have the use-case we most need, though it can
be bent to do it.  (Given their MATCH syntax, it doesn't seem there is
any logic that it tries INSERT first).

Looking at the MySQL URL above, REPLACE has three possible syntaxes with
normal (DELETE), SET (UPDATE), and SELECT.  Is this the direction we
need to go?  I don't like INSERT ... VIOLATION because I would like a
new keyword for this.  Is UPSERT the same as REPLACE?  Should we use
UPSERT instead?
	
-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

From DCorbit@connx.com Thu Nov 17 22:43:54 2005
Return-path: <DCorbit@connx.com>
Received: from postal.corporate.connx.com (postal.corporate.connx.com [65.212.159.187])
	by candle.pha.pa.us (8.11.6/8.11.6) with SMTP id jAI3hqO14288
	for <pgman@candle.pha.pa.us>; Thu, 17 Nov 2005 22:43:52 -0500 (EST)
content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----_=_NextPart_001_01C5EBF2.45ACB7DC"
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
Subject: RE: [HACKERS] MERGE vs REPLACE
Date: Thu, 17 Nov 2005 19:43:43 -0800
Message-ID: <D425483C2C5C9F49B5B7A41F8944154757D2EB@postal.corporate.connx.com>
Thread-Topic: [HACKERS] MERGE vs REPLACE
Thread-Index: AcXr7dUIILx3WyhrREu/dEAzLwToBgABFVZw
From: "Dann Corbit" <DCorbit@connx.com>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>, "Tom Lane" <tgl@sss.pgh.pa.us>
cc: "Csaba Nagy" <nagy@ecircle-ag.com>,
   "Martijn van Oosterhout" <kleptog@svana.org>,
   "Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at>,
   "Simon Riggs" <simon@2ndquadrant.com>,
   "Rick Gigger" <rick@alpinenetworking.com>,
   "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, <josh@agliodbs.com>,
   <pgsql-hackers@postgresql.org>, "Jaime Casanova" <systemguards@gmail.com>,
   "Peter Eisentraut" <peter_e@gmx.net>
Status: OR

This is a multi-part message in MIME format.

------_=_NextPart_001_01C5EBF2.45ACB7DC
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Attached web page is an extract from the SQL Standards working committee
document on MERGE

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Thursday, November 17, 2005 7:12 PM
> To: Tom Lane
> Cc: Csaba Nagy; Martijn van Oosterhout; Zeugswetter Andreas DCP SD;
Dann
> Corbit; Simon Riggs; Rick Gigger; Christopher Kings-Lynne; Jim C.
Nasby;
> josh@agliodbs.com; pgsql-hackers@postgresql.org; Jaime Casanova; Peter
> Eisentraut
> Subject: Re: [HACKERS] MERGE vs REPLACE
>=20
> Tom Lane wrote:
> > Csaba Nagy <nagy@ecircle-ag.com> writes:
> > > OK, in this case I don't care about either MERGE or REPLACE, but
for
> an
> > > UPSERT which does the locking :-)
> >
> > This is exactly the point --- pretty much nobody has come to us and
> > asked for a feature that does what Peter and Martijn say MERGE does.
> > (I haven't bothered to look at the 2003 spec, I'm assuming they read
it
> > correctly.)  What we *have* been asked for, over and over, is an
> > insert-or-update feature that's not so tedious and inefficient as
the
> > savepoint-insert-rollback-update kluge.  That's what we ought to be
> > concentrating on providing.
>=20
> I am confused over the various options.  I have heard these syntaxes:
>=20
> 	SQL2003 MERGE
> 	MySQL REPLACE
> 		http://dev.mysql.com/doc/refman/5.1/en/replace.html
> 	MySQL INSERT VIOLATION ...
> 	UPSERT
>=20
> So it seems MERGE does not have the use-case we most need, though it
can
> be bent to do it.  (Given their MATCH syntax, it doesn't seem there is
> any logic that it tries INSERT first).
>=20
> Looking at the MySQL URL above, REPLACE has three possible syntaxes
with
> normal (DELETE), SET (UPDATE), and SELECT.  Is this the direction we
> need to go?  I don't like INSERT ... VIOLATION because I would like a
> new keyword for this.  Is UPSERT the same as REPLACE?  Should we use
> UPSERT instead?
>=20
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
> 19073

------_=_NextPart_001_01C5EBF2.45ACB7DC
Content-Type: text/html;
	name="merge.htm"
Content-Transfer-Encoding: base64
Content-Description: merge.htm
Content-Disposition: attachment;
	filename="merge.htm"

[encoded data, 70224 characters]
aXY+DQoNCjwvYm9keT4NCg0KPC9odG1sPg0K

------_=_NextPart_001_01C5EBF2.45ACB7DC--

From pgsql-hackers-owner+M76240@postgresql.org Fri Nov 18 00:37:18 2005
Return-path: <pgsql-hackers-owner+M76240@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAI5bHO24992
	for <pgman@candle.pha.pa.us>; Fri, 18 Nov 2005 00:37:17 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 568F2C4BB5D;
	Fri, 18 Nov 2005 05:37:13 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id B2ABDDB7D0
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Fri, 18 Nov 2005 00:57:11 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 41664-06
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Nov 2005 04:57:10 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from zigo.dhs.org (ua-83-227-204-174.cust.bredbandsbolaget.se [83.227.204.174])
	by svr1.postgresql.org (Postfix) with ESMTP id E7930DB745
	for <pgsql-hackers@postgresql.org>; Fri, 18 Nov 2005 00:57:08 -0400 (AST)
Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1])
	by zigo.dhs.org (Postfix) with ESMTP
	id EC5488467; Fri, 18 Nov 2005 05:57:05 +0100 (CET)
Date: Fri, 18 Nov 2005 05:57:05 +0100 (CET)
From: Dennis Bjorklund <db@zigo.dhs.org>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Csaba Nagy <nagy@ecircle-ag.com>,
   Martijn van Oosterhout <kleptog@svana.org>, Dann Corbit <DCorbit@connx.com>,
   Simon Riggs <simon@2ndquadrant.com>,
   Rick Gigger <rick@alpinenetworking.com>, Tom Lane <tgl@sss.pgh.pa.us>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, <josh@agliodbs.com>,
   <pgsql-hackers@postgresql.org>, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
Subject: Re: [HACKERS] MERGE vs REPLACE
In-Reply-To: <200511180218.jAI2IPF04976@candle.pha.pa.us>
Message-ID: <Pine.LNX.4.44.0511180542100.17602-100000@zigo.dhs.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.479 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479]
X-Spam-Score: 0.479
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: ORr

On Thu, 17 Nov 2005, Bruce Momjian wrote:

> Unless you have a table lock, INSERT has to be before UPDATE, think
> UPDATE, UPDATE (both fail), INSERT, INSERT.

No matter what operation you start with you need a loop that try 
insert/update until one of them succeed like in this example:

http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Without a loop you might not get to execute neither the insert nor the 
update. Why? Think about this example:

BEGIN

INSERT      <- fail because there is a row already

            <- before we manage to do the update someone
               delete the row (which we can see in the
               default transaction isolation level)

UPDATE      <- fail because there is no row so we will loop
               and try the insert again

            <- before we manage to do the insert someone else does
               an insert

INSERT      <- fail because there is a row already

            <- before we manage to do the update someone
               delete the row 
....


You might need to loop any number of times before you manage to perform
one of the two operations. Which operation you should start with depends
on which of the two cases is the common one.

-- 
/Dennis Björklund


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

From ZeugswetterA@spardat.at Fri Nov 18 05:32:16 2005
Return-path: <ZeugswetterA@spardat.at>
Received: from smxsat1.smxs.net (smxsat1.smxs.net [213.150.10.1])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAIAWFO17231
	for <pgman@candle.pha.pa.us>; Fri, 18 Nov 2005 05:32:15 -0500 (EST)
Received: from m01x1.s-mxs.net [10.3.55.201]
	by smxsat1.smxs.net
	over TLS secured channel
	with XWall v3.35d. ;
	Fri, 18 Nov 2005 11:32:04 +0100
Received: from m0102.s-mxs.net [10.3.55.2]
	by m01x1.s-mxs.net
	with XWall v3.35d. ;
	Fri, 18 Nov 2005 11:33:38 +0100
Received: from m0143.s-mxs.net ([10.252.53.143]) by m0102.s-mxs.net with Microsoft SMTPSVC(6.0.3790.1830);
  Fri, 18 Nov 2005 11:33:38 +0100
X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0
content-class: urn:content-classes:message
Subject: RE: [HACKERS] MERGE vs REPLACE
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Date: Fri, 18 Nov 2005 11:32:01 +0100
Message-ID: <E1539E0ED7043848906A8FF995BDA5799A53C4@m0143.s-mxs.net>
Thread-Topic: [HACKERS] MERGE vs REPLACE
Thread-Index: AcXr5pYcJO3ZR4d/RwebJDquaJx6SQARC+uA
From: "Zeugswetter Andreas DCP SD" <ZeugswetterA@spardat.at>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
cc: "Csaba Nagy" <nagy@ecircle-ag.com>,
   "Martijn van Oosterhout" <kleptog@svana.org>,
   "Dann Corbit" <DCorbit@connx.com>, "Simon Riggs" <simon@2ndquadrant.com>,
   "Rick Gigger" <rick@alpinenetworking.com>, "Tom Lane" <tgl@sss.pgh.pa.us>,
   "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, <josh@agliodbs.com>,
   <pgsql-hackers@postgresql.org>, "Jaime Casanova" <systemguards@gmail.com>,
   "Peter Eisentraut" <peter_e@gmx.net>
X-OriginalArrivalTime: 18 Nov 2005 10:33:38.0046 (UTC) FILETIME=[8941B1E0:01C5EC2B]
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by candle.pha.pa.us id jAIAWFO17231
Status: OR


> Unless you have a table lock, INSERT has to be before UPDATE, think
UPDATE, UPDATE (both fail), INSERT, INSERT.

> > update
> > if no rows updated
> >   insert
> >   if duplicate key
> >     update
> >     if no rows updated goto insert

That is why you have the loop. This is not a problem with above code,
because only one insert succeeds
while the others then do the update.

Andreas

From pgsql-hackers-owner+M76266@postgresql.org Fri Nov 18 13:23:17 2005
Return-path: <pgsql-hackers-owner+M76266@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAIINGO19600
	for <pgman@candle.pha.pa.us>; Fri, 18 Nov 2005 13:23:17 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 33CABC4BB56;
	Fri, 18 Nov 2005 18:23:14 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 54076DBA92
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Fri, 18 Nov 2005 11:35:26 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 41216-03
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Nov 2005 15:35:29 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 84C42DBAAE
	for <pgsql-hackers@postgresql.org>; Fri, 18 Nov 2005 11:35:23 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jAIFYZcu006411;
	Fri, 18 Nov 2005 10:34:35 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Dennis Bjorklund <db@zigo.dhs.org>,
   Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Csaba Nagy <nagy@ecircle-ag.com>,
   Martijn van Oosterhout <kleptog@svana.org>, Dann Corbit <DCorbit@connx.com>,
   Simon Riggs <simon@2ndquadrant.com>,
   Rick Gigger <rick@alpinenetworking.com>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
Subject: Re: [HACKERS] MERGE vs REPLACE 
In-Reply-To: <200511181520.jAIFKpW02114@candle.pha.pa.us> 
References: <200511181520.jAIFKpW02114@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
	message dated "Fri, 18 Nov 2005 10:20:51 -0500"
Date: Fri, 18 Nov 2005 10:34:35 -0500
Message-ID: <6410.1132328075@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.005 required=5 tests=[AWL=0.005]
X-Spam-Score: 0.005
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: ORr

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Oh, good point.  I was thinking just about concurrent MERGEs.  However,
> it is more complicated than that.  By definitaion you can not see
> changes from other transactions while your statement is being run (even
> if you increment CommandCounter), so to be atomic, you would still see
> the row even though some other transaction had deleted it.

We would have to use the same semantics we use now for read-committed
UPDATE, that is look at the latest version of the row even though this
would not normally be visible to the transaction's snapshot.

In the case of a serializable transaction, no doubt we should fail if
any concurrent change actually happens.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

From pgsql-hackers-owner+M76315@postgresql.org Sun Nov 20 12:35:46 2005
Return-path: <pgsql-hackers-owner+M76315@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAKHZjO09346
	for <pgman@candle.pha.pa.us>; Sun, 20 Nov 2005 12:35:46 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 463E1C4B337;
	Sun, 20 Nov 2005 17:35:44 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 63416D7E05
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sun, 20 Nov 2005 13:27:09 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 12345-02
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 17:27:07 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126])
	by svr1.postgresql.org (Postfix) with ESMTP id 264B4D7E3B
	for <pgsql-hackers@postgresql.org>; Sun, 20 Nov 2005 13:27:05 -0400 (AST)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.11.6) id jAKHQI007897;
	Sun, 20 Nov 2005 12:26:18 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-ID: <200511201726.jAKHQI007897@candle.pha.pa.us>
Subject: Re: [HACKERS] MERGE vs REPLACE
In-Reply-To: <6410.1132328075@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 20 Nov 2005 12:26:18 -0500 (EST)
cc: Dennis Bjorklund <db@zigo.dhs.org>,
   Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Csaba Nagy <nagy@ecircle-ag.com>,
   Martijn van Oosterhout <kleptog@svana.org>, Dann Corbit <DCorbit@connx.com>,
   Simon Riggs <simon@2ndquadrant.com>,
   Rick Gigger <rick@alpinenetworking.com>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
   "Jim C. Nasby" <jnasby@pervasive.com>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
X-Mailer: ELM [version 2.4ME+ PL121 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.034 required=5 tests=[AWL=0.034]
X-Spam-Score: 0.034
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Oh, good point.  I was thinking just about concurrent MERGEs.  However,
> > it is more complicated than that.  By definitaion you can not see
> > changes from other transactions while your statement is being run (even
> > if you increment CommandCounter), so to be atomic, you would still see
> > the row even though some other transaction had deleted it.
> 
> We would have to use the same semantics we use now for read-committed
> UPDATE, that is look at the latest version of the row even though this
> would not normally be visible to the transaction's snapshot.
> 
> In the case of a serializable transaction, no doubt we should fail if
> any concurrent change actually happens.

I have some psaudocode to explain what we want for this feature,
whatever syntax we choose:

	Start
	Check unique index
	Found
		lock row for update
		if zero rows, return to start
		if more than one row, fail
		update row
	Notfound
		create savepoint
		insert row into heap
		lock index page
		if conflicting index entry, abort savepoint, return to start
		add index entry
		unlock index page

While the "notfound" case might look strange, we actually use this exact
method for inserts now, see ExecInsert() and _bt_doinsert(). 
Particularly see this comment in the second function:

    /*
     * If we're not allowing duplicates, make sure the key isn't already in
     * the index.
     *
     * NOTE: obviously,_bt_check_unique can only detect keys that are already in
     * the index; so it cannot defend against concurrent insertions of the
     * same key.  We protect against that by means of holding a write lock on
     * the target page.  Any other would-be inserter of the same key must
     * acquire a write lock on the same target page, so only one would-be
     * inserter can be making the check at one time.  Furthermore, once we are
     * past the check we hold write locks continuously until we have performed
     * our insertion, so no later inserter can fail to see our insertion.
     * (This requires some care in _bt_insertonpg.)
     *
     * If we must wait for another xact, we release the lock while waiting, and
     * then must start over completely.
     */

Here is the unique check error from _bt_check_unique():

                    ereport(ERROR,
                            (errcode(ERRCODE_UNIQUE_VIOLATION),
                    errmsg("duplicate key violates unique constraint \"%s\"",
                           RelationGetRelationName(rel))));

I think the problem here is that it is going to longjump() back to
postgres.c (and out of your code loop).  While we have savepoints, I
think they only work coming from client applications, rather than inside
our code.  Ideally you would like to be able to say:

	savepoint();
	func();
	rollback_to_savepoint();

but you can't, so I think you are going to have to factor out that
unique error callback and return a failure code to the caller.  I
suppose some boolean flag need to be added to _bt_doinsert(), but that
is called via a function pointer for the index type, so you are going to
have to update the insert function signatures for all access methods. 
The good news is that only btree supports unique indexes, according to
the documentation ("Only B-tree currently supports unique indexes") so
for the other access methods the extra parameter is just ignored.

Another issue is multiple unique indexes.  What if the first unique
index matches one row, but a different row matches the second unique
indexed column?  Fail because unique checks do not identify exactly one
row?

Or the _new_ value for the second indexed column conflicts with the
second unique index. The MERGE/REPLACE should fail.  The UPDATE block
will handle this on its own, but the INSERT block will need to check for
that an really error out, rather than return to the caller, so the loop
in ExecInsertIndexTuples() has to restart on unique failure _only_ on
the first index check, not the subsequent ones.

One simplification would be to allow MERGE/REPLACE only on a table that
has a single unique index.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

From pgsql-hackers-owner+M76369@postgresql.org Mon Nov 21 16:49:26 2005
Return-path: <pgsql-hackers-owner+M76369@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jALLnPO11702
	for <pgman@candle.pha.pa.us>; Mon, 21 Nov 2005 16:49:25 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id E0EF1C4BBF9;
	Mon, 21 Nov 2005 21:49:23 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 788FADA584
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Mon, 21 Nov 2005 17:44:39 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 70913-06
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Mon, 21 Nov 2005 21:44:40 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from flake.decibel.org (flake.decibel.org [67.100.216.10])
	by svr1.postgresql.org (Postfix) with ESMTP id 128D5DA48D
	for <pgsql-hackers@postgresql.org>; Mon, 21 Nov 2005 17:44:35 -0400 (AST)
Received: by flake.decibel.org (Postfix, from userid 1001)
	id AD10B1525F; Mon, 21 Nov 2005 15:44:36 -0600 (CST)
Date: Mon, 21 Nov 2005 15:44:36 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: mark@mark.mielke.cc, Tom Lane <tgl@sss.pgh.pa.us>,
   Csaba Nagy <nagy@ecircle-ag.com>,
   Martijn van Oosterhout <kleptog@svana.org>,
   Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Dann Corbit <DCorbit@connx.com>, Simon Riggs <simon@2ndquadrant.com>,
   Rick Gigger <rick@alpinenetworking.com>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
Subject: Re: [HACKERS] MERGE vs REPLACE
Message-ID: <20051121214436.GL19279@pervasive.com>
References: <20051117173117.GA27563@mark.mielke.cc> <200511180230.jAI2Uh406548@candle.pha.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200511180230.jAI2Uh406548@candle.pha.pa.us>
X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.019 required=5 tests=[AWL=0.019]
X-Spam-Score: 0.019
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

On Thu, Nov 17, 2005 at 09:30:43PM -0500, Bruce Momjian wrote:
> > Is the requirement for predicate locking, over and above a unique
> > constraint on an index that involves the record key, to deal with
> > the scenario of two inserts executing at the same time, both before
> > commit?
> 
> No.  If you have a primary key you can easily prevent duplicates.  You
> need a table lock or predicate locking to prevent duplicates if you do
> not have a primary key.

AFAIK you can also accomplish this without a table lock as long as you
have a unique index on the right set of fields and those fields are also
NOT NULL. ISTM it would be good to support that case as well, since you
might want to MERGE based on something other than the PK.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

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

From decibel@decibel.org Mon Nov 21 16:46:33 2005
Return-path: <decibel@decibel.org>
Received: from flake.decibel.org (flake.decibel.org [67.100.216.10])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jALLkWO11195
	for <pgman@candle.pha.pa.us>; Mon, 21 Nov 2005 16:46:32 -0500 (EST)
Received: by flake.decibel.org (Postfix, from userid 1001)
	id 78F981525F; Mon, 21 Nov 2005 15:46:30 -0600 (CST)
Date: Mon, 21 Nov 2005 15:46:30 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Tom Lane <tgl@sss.pgh.pa.us>, Csaba Nagy <nagy@ecircle-ag.com>,
   Martijn van Oosterhout <kleptog@svana.org>,
   Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>,
   Dann Corbit <DCorbit@connx.com>, Simon Riggs <simon@2ndquadrant.com>,
   Bruce Momjian <pgman@candle.pha.pa.us>,
   Rick Gigger <rick@alpinenetworking.com>,
   Christopher Kings-Lynne <chriskl@familyhealth.com.au>, josh@agliodbs.com,
   pgsql-hackers@postgresql.org, Jaime Casanova <systemguards@gmail.com>,
   Peter Eisentraut <peter_e@gmx.net>
Subject: Re: [HACKERS] MERGE vs REPLACE
Message-ID: <20051121214630.GM19279@pervasive.com>
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de> <25849.1132238470@sss.pgh.pa.us> <20051117151530.GU6026@ns.snowman.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20051117151530.GU6026@ns.snowman.net>
X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.9i
Status: OR

On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote:
> I don't think MERGE can really be made to be both though, in which case
> it should really be the SQL2003 MERGE and we can make REPLACE/INSERT ON
> DUPLICATE UPDATE something else.  Perhaps a special form of MERGE where
> you know it's going to be doing that locking.  I really don't like the
> idea of making the SQL2003 version of MERGE be the MERGE special case
> (by requiring someone to take a table lock ahead of time or do something
> else odd).

Anyone know off-hand what the big 3 do? If the industry consensus is
that merge should actually be REPLACE/INSERT ON DUPLICATE UPDATE then
it's probably better to follow that lead.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

From pgsql-hackers-owner+M76416@postgresql.org Mon Nov 21 22:20:45 2005
Return-path: <pgsql-hackers-owner+M76416@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAM3KiO01066
	for <pgman@candle.pha.pa.us>; Mon, 21 Nov 2005 22:20:45 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 16D40C4BC0E;
	Tue, 22 Nov 2005 03:20:42 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 19544D6E94
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Mon, 21 Nov 2005 23:20:13 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 65279-06
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Nov 2005 03:20:16 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from gatekeeper.pjmodos.bounceme.net (5.176.broadband4.iol.cz [85.71.176.5])
	by svr1.postgresql.org (Postfix) with ESMTP id 63160DA4D8
	for <pgsql-hackers@postgresql.org>; Mon, 21 Nov 2005 23:20:10 -0400 (AST)
Received: from modos ([10.12.0.96] ident=PJMODOS)
	by gatekeeper.pjmodos.bounceme.net
 with esmtp (Exim 4.50)
	id 1EeOhc-0004vs-1V; Tue, 22 Nov 2005 04:20:16 +0100
Message-ID: <43828E6C.60406@seznam.cz>
Date: Tue, 22 Nov 2005 04:20:12 +0100
From: Petr Jelinek <pjmodos@seznam.cz>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: cs, en, en-us
MIME-Version: 1.0
To: "Jim C. Nasby" <jnasby@pervasive.com>
cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] MERGE vs REPLACE
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de> <25849.1132238470@sss.pgh.pa.us> <20051117151530.GU6026@ns.snowman.net> <20051121214630.GM19279@pervasive.com>
In-Reply-To: <20051121214630.GM19279@pervasive.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Amavis-Alert: BAD HEADER Improper use of control character (char 0D hex) in message header 'Received': Received: ...keeper.pjmodos.bounceme.net\r with esmtp (Ex...
X-Spam-Status: No, score=1.44 required=5 tests=[DNS_FROM_RFC_POST=1.44]
X-Spam-Score: 1.44
X-Spam-Level: *
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Jim C. Nasby wrote:
> On Thu, Nov 17, 2005 at 10:15:30AM -0500, Stephen Frost wrote:
> 
>>I don't think MERGE can really be made to be both though, in which case
>>it should really be the SQL2003 MERGE and we can make REPLACE/INSERT ON
>>DUPLICATE UPDATE something else.  Perhaps a special form of MERGE where
>>you know it's going to be doing that locking.  I really don't like the
>>idea of making the SQL2003 version of MERGE be the MERGE special case
>>(by requiring someone to take a table lock ahead of time or do something
>>else odd).
> 
> 
> Anyone know off-hand what the big 3 do? If the industry consensus is
> that merge should actually be REPLACE/INSERT ON DUPLICATE UPDATE then
> it's probably better to follow that lead.

It was already said here that oracle and db2 both use MERGE, dunno about 
mssql.

And yes merge CAN be used to do REPLACE (oracle uses their dummy table 
for this, we can use the fact that FROM clause isn't required in postgres).

-- 
Regards
Petr Jelinek (PJMODOS)

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

From pgsql-hackers-owner+M76436@postgresql.org Tue Nov 22 05:58:26 2005
Return-path: <pgsql-hackers-owner+M76436@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAMAwPO08019
	for <pgman@candle.pha.pa.us>; Tue, 22 Nov 2005 05:58:25 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 73C27C4A48E;
	Tue, 22 Nov 2005 10:58:21 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 0E86EDAFA6
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Tue, 22 Nov 2005 06:57:56 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 82600-10
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Nov 2005 10:57:57 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svana.org (svana.org [203.20.62.76])
	by svr1.postgresql.org (Postfix) with ESMTP id EE3F8DAFA1
	for <pgsql-hackers@postgresql.org>; Tue, 22 Nov 2005 06:57:51 -0400 (AST)
Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian))
	id 1EeVqO-0003kj-00; Tue, 22 Nov 2005 21:57:48 +1100
Date: Tue, 22 Nov 2005 11:57:48 +0100
From: Martijn van Oosterhout <kleptog@svana.org>
To: Petr Jelinek <pjmodos@seznam.cz>
cc: "Jim C. Nasby" <jnasby@pervasive.com>,
   PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] MERGE vs REPLACE
Message-ID: <20051122105747.GC12548@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de> <25849.1132238470@sss.pgh.pa.us> <20051117151530.GU6026@ns.snowman.net> <20051121214630.GM19279@pervasive.com> <43828E6C.60406@seznam.cz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="aT9PWwzfKXlsBJM1"
Content-Disposition: inline
In-Reply-To: <43828E6C.60406@seznam.cz>
User-Agent: Mutt/1.3.28i
X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6
X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522  48A7 6394 F08A 0DC6 7BE6
X-PGP-Key-URL: <http://svana.org/kleptog/0DC67BE6.pgp.asc>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


--aT9PWwzfKXlsBJM1
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Nov 22, 2005 at 04:20:12AM +0100, Petr Jelinek wrote:
> It was already said here that oracle and db2 both use MERGE, dunno about=
=20
> mssql.
>=20
> And yes merge CAN be used to do REPLACE (oracle uses their dummy table=20
> for this, we can use the fact that FROM clause isn't required in postgres=
).

Statements about MERGE on the web:

http://www.dba-oracle.com/oracle_tips_rittman_merge.htm
http://databasejournal.com/features/db2/article.php/3322041
http://certcities.com/editorial/columns/story.asp?EditorialsID=3D51
http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=3D/com.ib=
m.sqls.doc/sqls578.htm
http://www.jdixon.dotnetdevelopersjournal.com/i_want_my_sql_2005_merge_stat=
ement.htm
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/=
admin/r0010873.htm
http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,s=
id63_gci978700,00.html

Not one (*not one!*) of these mentions any special handling of
duplicate keys. They even go to pains to say that any errors cause
everything to rollback. The last one is especially interesting:

: Is there any way to capture errors from a MERGE statement? Also, is
: there any way to know how many records were inserted or updated for the
: MERGE statement like SQL%ROWCOUNT? Any assistance greatly appreciated.=20
:
: You capture errors the same way you would if you were doing regular
: INSERT and UPDATE statements....with exception handlers. Just include a
: WHEN OTHERS exception handler in the block where your MERGE statement
: is and have to display SQLCODE and SQLERRM if an error occurs. Then you
: can figure out which specific errors are occurring and create
: individual exception handlers for those.=20

There are even places that tell you how to decompose your MERGE into an
INSERT plus UPDATE statement. The real advantage of MERGE is that the
semantics prevent your updating a row you just inserted, which is
harder in the general case but easy if the executor is handling the
rows one at a time.

Rather than trying to make MERGE do something it wasn't designed for,
we should probably be spending our efforts on triggers for error
conditions. Maybe something like:

CREATE TRIGGER foo AFTER ERROR ON bar EXECUTE baz();

Where baz would be passed NEW and OLD just like a normal trigger and if
the trigger return NULL, the update is ignored. In the meantime the
function can divert the insert to another table if it likes. This seems
like a much more workable and useful addition.

Have a nice day,
--=20
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.

--aT9PWwzfKXlsBJM1
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFDgvmqIB7bNG8LQkwRAr0eAKCBybl6qs2NN6kXG15tiGg+nObK0QCeP+2S
+S9F/7PZ70V8CQmZqqMn6sE=
=QsDZ
-----END PGP SIGNATURE-----

--aT9PWwzfKXlsBJM1--

From pgsql-hackers-owner+M76466@postgresql.org Tue Nov 22 15:31:18 2005
Return-path: <pgsql-hackers-owner+M76466@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAMKVHO11734
	for <pgman@candle.pha.pa.us>; Tue, 22 Nov 2005 15:31:17 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id A1AC1C4BC25;
	Tue, 22 Nov 2005 20:31:15 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 354D0DB257
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Tue, 22 Nov 2005 16:30:47 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 15163-07-3
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Tue, 22 Nov 2005 20:30:47 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from flake.decibel.org (flake.decibel.org [67.100.216.10])
	by svr1.postgresql.org (Postfix) with ESMTP id 3E9B9DBA86
	for <pgsql-hackers@postgresql.org>; Tue, 22 Nov 2005 16:30:43 -0400 (AST)
Received: by flake.decibel.org (Postfix, from userid 1001)
	id 781A61525F; Tue, 22 Nov 2005 14:30:45 -0600 (CST)
Date: Tue, 22 Nov 2005 14:30:45 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Martijn van Oosterhout <kleptog@svana.org>
cc: Petr Jelinek <pjmodos@seznam.cz>,
   PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] MERGE vs REPLACE
Message-ID: <20051122203045.GC99429@pervasive.com>
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de> <25849.1132238470@sss.pgh.pa.us> <20051117151530.GU6026@ns.snowman.net> <20051121214630.GM19279@pervasive.com> <43828E6C.60406@seznam.cz> <20051122105747.GC12548@svana.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20051122105747.GC12548@svana.org>
X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.9i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.012 required=5 tests=[AWL=0.012]
X-Spam-Score: 0.012
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

On Tue, Nov 22, 2005 at 11:57:48AM +0100, Martijn van Oosterhout wrote:
<excellent research snipped>

> Rather than trying to make MERGE do something it wasn't designed for,
> we should probably be spending our efforts on triggers for error
> conditions. Maybe something like:
> 
> CREATE TRIGGER foo AFTER ERROR ON bar EXECUTE baz();
> 
> Where baz would be passed NEW and OLD just like a normal trigger and if
> the trigger return NULL, the update is ignored. In the meantime the
> function can divert the insert to another table if it likes. This seems
> like a much more workable and useful addition.

I agree that we shouldn't try and distort MERGE into something fancy.
The AFTER ERROR trigger is a very interesting idea, since it could
handle many different cases. But I'm worried that people might not want
that behavior on by default for everything done against some table. I
think it'd be better to have some way to specify in a command that
you want to use some kind of error-handling trigger. Though presumably
the underlying framework would be same, so it shouldn't be hard to
support both.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

From pgsql-hackers-owner+M76499@postgresql.org Wed Nov 23 04:10:09 2005
Return-path: <pgsql-hackers-owner+M76499@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAN9A8O01978
	for <pgman@candle.pha.pa.us>; Wed, 23 Nov 2005 04:10:09 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 8270EC4BC25;
	Wed, 23 Nov 2005 09:10:04 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 39862DB27A
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Wed, 23 Nov 2005 05:09:40 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 89452-02
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Nov 2005 09:09:40 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svana.org (svana.org [203.20.62.76])
	by svr1.postgresql.org (Postfix) with ESMTP id C6404DB088
	for <pgsql-hackers@postgresql.org>; Wed, 23 Nov 2005 05:09:36 -0400 (AST)
Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian))
	id 1Eeqd7-0002FD-00; Wed, 23 Nov 2005 20:09:29 +1100
Date: Wed, 23 Nov 2005 10:09:29 +0100
From: Martijn van Oosterhout <kleptog@svana.org>
To: Petr Jelinek <pjmodos@seznam.cz>
cc: Jaime Casanova <systemguards@gmail.com>,
   PostgreSQL-development <pgsql-hackers@postgresql.org>,
   "Jim C. Nasby" <jnasby@pervasive.com>
Subject: Re: [HACKERS] MERGE vs REPLACE
Message-ID: <20051123090926.GA8374@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de> <25849.1132238470@sss.pgh.pa.us> <20051117151530.GU6026@ns.snowman.net> <20051121214630.GM19279@pervasive.com> <43828E6C.60406@seznam.cz> <c2d9e70e0511212307p2cd340fer113de21002be1e94@mail.gmail.com> <4383A8A8.30805@seznam.cz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="fdj2RfSjLxBAspz7"
Content-Disposition: inline
In-Reply-To: <4383A8A8.30805@seznam.cz>
User-Agent: Mutt/1.3.28i
X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6
X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522  48A7 6394 F08A 0DC6 7BE6
X-PGP-Key-URL: <http://svana.org/kleptog/0DC67BE6.pgp.asc>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


--fdj2RfSjLxBAspz7
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Nov 23, 2005 at 12:24:24AM +0100, Petr Jelinek wrote:
> Btw about that keys, oracle gives error on many-to-one or many-to-many=20
> relationship between the source and target tables.

The standard has something called a "cardinality violation" if the
to-be-merged table doesn't match 1-1 with the rest of the statement. If
I had access to an Oracle I'd run two tests on MERGE:

1. Does the joining column have to have an index? For example, make a
column that's full of unique values but no unique index. According to
my reading of the the standard, this should still work (just slower).

2. Additionally, only the rows involved in the MERGE need to be
uniquely referenced, so if you add duplicate values but add a WHERE
clause to exclude those, it should also work.

My feeling is that requiring an index will limit it's usefulness as a
general tool.

Have a nice day,
--=20
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.

--fdj2RfSjLxBAspz7
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFDhDHFIB7bNG8LQkwRAq+3AJ4kprR3WbWOegJGf9JTVsNLa11/jQCfTmH8
6o2CsvPgg1g8uOnjbHoGMHo=
=D4Cr
-----END PGP SIGNATURE-----

--fdj2RfSjLxBAspz7--

From pgsql-hackers-owner+M76510@postgresql.org Wed Nov 23 14:25:05 2005
Return-path: <pgsql-hackers-owner+M76510@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jANJP4O22462
	for <pgman@candle.pha.pa.us>; Wed, 23 Nov 2005 14:25:04 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 3EF3EC4BBCD;
	Wed, 23 Nov 2005 19:25:02 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 94C5FDB839
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Wed, 23 Nov 2005 15:24:39 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 81352-06
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Nov 2005 19:24:39 +0000 (GMT)
X-Greylist: delayed 00:31:59.455806 by SQLgrey-
Received: from mecho.sysmaster.com (unknown [65.119.37.45])
	by svr1.postgresql.org (Postfix) with ESMTP id 5078FDA7A5
	for <pgsql-hackers@postgresql.org>; Wed, 23 Nov 2005 15:24:36 -0400 (AST)
Received: from [127.0.0.1] (office.sysmaster.com [65.113.143.60])
	by mecho.sysmaster.com (8.11.6/8.11.6) with ESMTP id jANIqMB22433;
	Wed, 23 Nov 2005 10:52:22 -0800
Message-ID: <4384BA5F.4080402@sysmaster.com>
Date: Wed, 23 Nov 2005 10:52:15 -0800
From: Lyubomir Petrov <lpetrov@sysmaster.com>
User-Agent: Thunderbird 1.5 (Windows/20051025)
MIME-Version: 1.0
To: Martijn van Oosterhout <kleptog@svana.org>
cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] MERGE vs REPLACE
References: <E1539E0ED7043848906A8FF995BDA5799A524D@m0143.s-mxs.net> <1132228373.10890.313.camel@coppola.muc.ecircle.de> <20051117123250.GC22933@svana.org> <1132231474.10890.317.camel@coppola.muc.ecircle.de> <25849.1132238470@sss.pgh.pa.us> <20051117151530.GU6026@ns.snowman.net> <20051121214630.GM19279@pervasive.com> <43828E6C.60406@seznam.cz> <c2d9e70e0511212307p2cd340fer113de21002be1e94@mail.gmail.com> <4383A8A8.30805@seznam.cz> <20051123090926.GA8374@svana.org>
In-Reply-To: <20051123090926.GA8374@svana.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

Martijn,

Here is a quick test (Oracle 10.1.0.3/Linux):


SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SQL> select * from merge_test_1;
        ID NAME
---------- --------------------
         1 aaa
         2 bbb
         3 ccc
         4 ddd
         5 eee
         1 xxx
6 rows selected.

SQL> select * from merge_test_2;
        ID NAME
---------- --------------------
         1 AAA
         2 BBB
         6 FFF

SQL> select index_name from user_indexes where table_name like 
'merge_test%';
no rows selected

SQL> merge into merge_test_1 a1
  2  using merge_test_2 a2
  3      on (a1.id = a2.id)
  4  when matched then
  5      update set a1.name = a2.name
  6  when not matched then
  7      insert (id, name) values (a2.id, a2.name);
4 rows merged.

SQL> select * from merge_test_1;
        ID NAME
---------- --------------------
         1 AAA
         2 BBB
         3 ccc
         4 ddd
         5 eee
         1 AAA
         6 FFF
7 rows selected.



Regards,
Lubomir Petrov



Martijn van Oosterhout wrote:
> On Wed, Nov 23, 2005 at 12:24:24AM +0100, Petr Jelinek wrote:
>   
>> Btw about that keys, oracle gives error on many-to-one or many-to-many 
>> relationship between the source and target tables.
>>     
>
> The standard has something called a "cardinality violation" if the
> to-be-merged table doesn't match 1-1 with the rest of the statement. If
> I had access to an Oracle I'd run two tests on MERGE:
>
> 1. Does the joining column have to have an index? For example, make a
> column that's full of unique values but no unique index. According to
> my reading of the the standard, this should still work (just slower).
>
> 2. Additionally, only the rows involved in the MERGE need to be
> uniquely referenced, so if you add duplicate values but add a WHERE
> clause to exclude those, it should also work.
>
> My feeling is that requiring an index will limit it's usefulness as a
> general tool.
>
> Have a nice day,
>   


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

From pgsql-hackers-owner+M76513@postgresql.org Wed Nov 23 17:23:34 2005
Return-path: <pgsql-hackers-owner+M76513@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jANMNXO20323
	for <pgman@candle.pha.pa.us>; Wed, 23 Nov 2005 17:23:33 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id C020EC4A48E;
	Wed, 23 Nov 2005 22:23:30 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 03F71DBAA3
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Wed, 23 Nov 2005 18:23:06 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 92598-01
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Wed, 23 Nov 2005 22:23:06 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp018.mail.yahoo.com (smtp018.mail.yahoo.com [216.136.174.115])
	by svr1.postgresql.org (Postfix) with SMTP id 121D3DBA93
	for <pgsql-hackers@postgresql.org>; Wed, 23 Nov 2005 18:23:01 -0400 (AST)
Received: (qmail 47652 invoked from network); 23 Nov 2005 22:22:56 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=Yahoo.com;
  h=Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
  b=ikj/Ob+3p22lsO9ikWyS1RkIbs6fq47zO2HF4jU7ZStjg5F/smOj0F+YkTeoepYDBoiKtd5pr0vxODe6iSXdUmQgXKe4BvzaypEm8KFqgR2kKtlpW/QAy1arUUnD1DoFpx/SJgPFt1V4S//lgGTpWw5v99EeoYfapzNmNV0hgQI=  ;
Received: from unknown (HELO jupiter.black-lion.info) (janwieck@68.80.245.191 with login)
  by smtp018.mail.yahoo.com with SMTP; 23 Nov 2005 22:22:55 -0000
Received: from [172.21.8.23] (mars.black-lion.info [192.168.192.101])
	(authenticated bits=0)
	by jupiter.black-lion.info (8.12.10/8.12.9) with ESMTP id jANLtT1o054955;
	Wed, 23 Nov 2005 16:55:29 -0500 (EST)
	(envelope-from JanWieck@Yahoo.com)
Message-ID: <4384E54D.8000500@Yahoo.com>
Date: Wed, 23 Nov 2005 16:55:25 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.1) Gecko/20040707
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Peter Eisentraut <peter_e@gmx.net>, pgsql-hackers@postgresql.org,
   Josh Berkus <josh@agliodbs.com>, Jaime Casanova <systemguards@gmail.com>
Subject: Re: [HACKERS] someone working to add merge?
References: <c2d9e70e0511110603q1799d811u6e4564be516b10ce@mail.gmail.com> <200511111924.41532.peter_e@gmx.net> <20561.1131734697@sss.pgh.pa.us> <200511112004.35876.peter_e@gmx.net> <20899.1131736841@sss.pgh.pa.us>
In-Reply-To: <20899.1131736841@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.359 required=5 tests=[AWL=-0.120,
	DNS_FROM_RFC_ABUSE=0.479]
X-Spam-Score: 0.359
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

On 11/11/2005 2:20 PM, Tom Lane wrote:

> Peter Eisentraut <peter_e@gmx.net> writes:
>> Tom Lane wrote:
>>> Surely they require a unique constraint --- else the behavior isn't
>>> even well defined, is it?
> 
>> They require that the merge condition does not match for more than one 
>> row, but since the merge condition can do just about anything, there is 
>> no guarantee that a unique constraint encompasses it.
> 
> ISTM to be a reasonable implementation restriction that there be a
> constraint by which the system can prove that there is at most one
> matching row.  Per other comments in this thread, we'd not be the only
> implementation making such a restriction.
> 
> (Certainly, if I were a DBA and were told that the performance of MERGE
> would go to hell in a handbasket if I had no such constraint, I'd make
> sure there was one.  I don't think there is very much of a use-case for
> the general scenario.)

Such restriction does look reasonable. Especially because ...

The largest problem I see with MERGE is the question of BEFORE triggers. 
Consider a BEFORE INSERT trigger that modifies a third table, after 
which the constraint or whatever post-heap_insert-attempt we might use 
detects a conflict. How do we undo the actions of the BEFORE trigger? 
The only way to do that is to plan the query as a nestloop, with the 
USING part as the outer loop. If the (updating) scan of the INTO 
relation did not hit any tuple, then do the INSERT. We can only undo the 
side effects of any BEFORE trigger by wrapping each and evey nested INTO 
relation insert attempt into its own subtransaction.

Sure, we "could" of course do the insert and then rescan the whole thing 
with read-committed to see if our row is now the only one ... needless 
to say that in the case of a sequential scan inside the loop, that 
nestloop will suck big times even without that second scan. But ... hmmm 
... we could get away with that and if we don't find a constraint that 
will ensure uniqueness, then we do a rescan to check for it. But I would 
vote for a "please_no_notice_about_stupid_usage_of_merge" runtime option 
that suppresses the corresponding NOTICE.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

From pgsql-hackers-owner+M76524@postgresql.org Thu Nov 24 01:31:30 2005
Return-path: <pgsql-hackers-owner+M76524@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAO6VTO16396
	for <pgman@candle.pha.pa.us>; Thu, 24 Nov 2005 01:31:30 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id D9160C4A48E;
	Thu, 24 Nov 2005 06:31:25 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 42FE3DBC43
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Thu, 24 Nov 2005 02:31:01 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 56362-10
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Thu, 24 Nov 2005 06:31:00 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svana.org (svana.org [203.20.62.76])
	by svr1.postgresql.org (Postfix) with ESMTP id A1169DBC30
	for <pgsql-hackers@postgresql.org>; Thu, 24 Nov 2005 02:30:58 -0400 (AST)
Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian))
	id 1EfAcw-0004xu-00; Thu, 24 Nov 2005 17:30:38 +1100
Date: Thu, 24 Nov 2005 07:30:37 +0100
From: Martijn van Oosterhout <kleptog@svana.org>
To: Jan Wieck <JanWieck@Yahoo.com>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net>,
   pgsql-hackers@postgresql.org, Josh Berkus <josh@agliodbs.com>,
   Jaime Casanova <systemguards@gmail.com>
Subject: Re: [HACKERS] someone working to add merge?
Message-ID: <20051124063034.GA18750@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <c2d9e70e0511110603q1799d811u6e4564be516b10ce@mail.gmail.com> <200511111924.41532.peter_e@gmx.net> <20561.1131734697@sss.pgh.pa.us> <200511112004.35876.peter_e@gmx.net> <20899.1131736841@sss.pgh.pa.us> <4384E54D.8000500@Yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="45Z9DzgjV8m4Oswq"
Content-Disposition: inline
In-Reply-To: <4384E54D.8000500@Yahoo.com>
User-Agent: Mutt/1.3.28i
X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6
X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522  48A7 6394 F08A 0DC6 7BE6
X-PGP-Key-URL: <http://svana.org/kleptog/0DC67BE6.pgp.asc>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


--45Z9DzgjV8m4Oswq
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Nov 23, 2005 at 04:55:25PM -0500, Jan Wieck wrote:
> The largest problem I see with MERGE is the question of BEFORE triggers.=
=20
> Consider a BEFORE INSERT trigger that modifies a third table, after=20
> which the constraint or whatever post-heap_insert-attempt we might use=20
> detects a conflict. How do we undo the actions of the BEFORE trigger?=20
> The only way to do that is to plan the query as a nestloop, with the=20
> USING part as the outer loop. If the (updating) scan of the INTO=20
> relation did not hit any tuple, then do the INSERT. We can only undo the=
=20
> side effects of any BEFORE trigger by wrapping each and evey nested INTO=
=20
> relation insert attempt into its own subtransaction.

Umm, if there are any errors you abort the transaction, just like any
other case. ACID requires that either the whole statement is done, or
none. If a trigger causes the INSERT or UPDATE to fail you have no
choice but to abort the transaction.

Besides, someone posted an example on Oracle, they don't require an
index so I don't think we realistically can say that people need one.
If two concurrent MERGEs, which can't see eachothers output, both end
up INSERTing, that not an error unless the user has a UNIQUE
constraint, so the problem vanishes.

Have a nice day,
--=20
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.

--45Z9DzgjV8m4Oswq
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFDhV4HIB7bNG8LQkwRAgXZAKCBD3QOdBVT3GmCqEQj9tfapJq38QCeOdvW
VXixm+YDkCCWkSLEQcUtixM=
=3H5a
-----END PGP SIGNATURE-----

--45Z9DzgjV8m4Oswq--

From pgsql-hackers-owner+M76549@postgresql.org Thu Nov 24 11:12:14 2005
Return-path: <pgsql-hackers-owner+M76549@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAOGCDO07384
	for <pgman@candle.pha.pa.us>; Thu, 24 Nov 2005 11:12:13 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 305F4C4A48E;
	Thu, 24 Nov 2005 16:12:12 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6A539DBC1D
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Thu, 24 Nov 2005 12:11:46 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 60324-06-2
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Thu, 24 Nov 2005 16:11:44 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp011.mail.yahoo.com (smtp011.mail.yahoo.com [216.136.173.31])
	by svr1.postgresql.org (Postfix) with SMTP id 11B74DBBF3
	for <pgsql-hackers@postgresql.org>; Thu, 24 Nov 2005 12:11:42 -0400 (AST)
Received: (qmail 72237 invoked from network); 24 Nov 2005 16:11:41 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=Yahoo.com;
  h=Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
  b=dgj7516dhcBQJ4gSnC2f7PmbQi7TxXH8v8ahOhOPDm/53Yj9wVKNG2jTbp+j2crVe+Tw7VA5EEhS2YqaHTmD8Fs3np6yVP4J1VN/SF7+aywZkiiRJUSEAe3qYpGIGRFxtnKr3K3M6TufdeJZuEKu4O3YCW+IZi8SCulBwKV5jfM=  ;
Received: from unknown (HELO jupiter.black-lion.info) (janwieck@68.80.245.191 with login)
  by smtp011.mail.yahoo.com with SMTP; 24 Nov 2005 16:11:41 -0000
Received: from [172.21.8.23] (mars.black-lion.info [192.168.192.101])
	(authenticated bits=0)
	by jupiter.black-lion.info (8.12.10/8.12.9) with ESMTP id jAOGBc1o058226;
	Thu, 24 Nov 2005 11:11:38 -0500 (EST)
	(envelope-from JanWieck@Yahoo.com)
Message-ID: <4385E636.4070503@Yahoo.com>
Date: Thu, 24 Nov 2005 11:11:34 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.1) Gecko/20040707
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Martijn van Oosterhout <kleptog@svana.org>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net>,
   pgsql-hackers@postgresql.org, Josh Berkus <josh@agliodbs.com>,
   Jaime Casanova <systemguards@gmail.com>
Subject: Re: [HACKERS] someone working to add merge?
References: <c2d9e70e0511110603q1799d811u6e4564be516b10ce@mail.gmail.com> <200511111924.41532.peter_e@gmx.net> <20561.1131734697@sss.pgh.pa.us> <200511112004.35876.peter_e@gmx.net> <20899.1131736841@sss.pgh.pa.us> <4384E54D.8000500@Yahoo.com> <20051124063034.GA18750@svana.org>
In-Reply-To: <20051124063034.GA18750@svana.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.359 required=5 tests=[AWL=-0.120,
	DNS_FROM_RFC_ABUSE=0.479]
X-Spam-Score: 0.359
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

On 11/24/2005 1:30 AM, Martijn van Oosterhout wrote:

> On Wed, Nov 23, 2005 at 04:55:25PM -0500, Jan Wieck wrote:
>> The largest problem I see with MERGE is the question of BEFORE triggers. 
>> Consider a BEFORE INSERT trigger that modifies a third table, after 
>> which the constraint or whatever post-heap_insert-attempt we might use 
>> detects a conflict. How do we undo the actions of the BEFORE trigger? 
>> The only way to do that is to plan the query as a nestloop, with the 
>> USING part as the outer loop. If the (updating) scan of the INTO 
>> relation did not hit any tuple, then do the INSERT. We can only undo the 
>> side effects of any BEFORE trigger by wrapping each and evey nested INTO 
>> relation insert attempt into its own subtransaction.
> 
> Umm, if there are any errors you abort the transaction, just like any
> other case. ACID requires that either the whole statement is done, or
> none. If a trigger causes the INSERT or UPDATE to fail you have no
> choice but to abort the transaction.

I guess you misunderstood. What I am talking about is a problem in the 
order of execution. since we don't have predicate locking, there is a 
possibility that our implementation of MERGE decides to do an INSERT 
while another transaction does the same. What has to happen is that the 
BEFORE INSERT trigger is called, then the heap tuple inserted, then the 
index tuples created. At this time, the duplicate key error occurs, 
telling us that we had a conflict and that we have to try an UPDATE 
instead. That means, in the end this particular row's INSERT has never 
happened and we have to undo the BEFORE INSERT triggers actions too.

> 
> Besides, someone posted an example on Oracle, they don't require an
> index so I don't think we realistically can say that people need one.
> If two concurrent MERGEs, which can't see eachothers output, both end
> up INSERTing, that not an error unless the user has a UNIQUE
> constraint, so the problem vanishes.

Not following the semantics is an error. MERGE is not supposed to do 
multiple inserts for the same match, concurrency or not.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

From pgsql-hackers-owner+M76581@postgresql.org Fri Nov 25 07:14:48 2005
Return-path: <pgsql-hackers-owner+M76581@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAPCElB26143
	for <pgman@candle.pha.pa.us>; Fri, 25 Nov 2005 07:14:47 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id 46071C4BC79;
	Fri, 25 Nov 2005 12:14:46 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A32A1D72AA
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Fri, 25 Nov 2005 08:14:18 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86344-02
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Nov 2005 12:14:19 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svana.org (svana.org [203.20.62.76])
	by svr1.postgresql.org (Postfix) with ESMTP id 14D00D6FFA
	for <pgsql-hackers@postgresql.org>; Fri, 25 Nov 2005 08:14:14 -0400 (AST)
Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian))
	id 1EfcSp-00058u-00; Fri, 25 Nov 2005 23:14:03 +1100
Date: Fri, 25 Nov 2005 13:14:03 +0100
From: Martijn van Oosterhout <kleptog@svana.org>
To: Jan Wieck <JanWieck@Yahoo.com>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net>,
   pgsql-hackers@postgresql.org, Josh Berkus <josh@agliodbs.com>,
   Jaime Casanova <systemguards@gmail.com>
Subject: Re: [HACKERS] someone working to add merge?
Message-ID: <20051125121402.GB16970@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <c2d9e70e0511110603q1799d811u6e4564be516b10ce@mail.gmail.com> <200511111924.41532.peter_e@gmx.net> <20561.1131734697@sss.pgh.pa.us> <200511112004.35876.peter_e@gmx.net> <20899.1131736841@sss.pgh.pa.us> <4384E54D.8000500@Yahoo.com> <20051124063034.GA18750@svana.org> <4385E636.4070503@Yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="2B/JsCI69OhZNC5r"
Content-Disposition: inline
In-Reply-To: <4385E636.4070503@Yahoo.com>
User-Agent: Mutt/1.3.28i
X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6
X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522  48A7 6394 F08A 0DC6 7BE6
X-PGP-Key-URL: <http://svana.org/kleptog/0DC67BE6.pgp.asc>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


--2B/JsCI69OhZNC5r
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Thu, Nov 24, 2005 at 11:11:34AM -0500, Jan Wieck wrote:
> On 11/24/2005 1:30 AM, Martijn van Oosterhout wrote:
> >Umm, if there are any errors you abort the transaction, just like any
> >other case. ACID requires that either the whole statement is done, or
> >none. If a trigger causes the INSERT or UPDATE to fail you have no
> >choice but to abort the transaction.
>=20
> I guess you misunderstood. What I am talking about is a problem in the=20
> order of execution. since we don't have predicate locking, there is a=20
> possibility that our implementation of MERGE decides to do an INSERT=20
> while another transaction does the same. What has to happen is that the=
=20
> BEFORE INSERT trigger is called, then the heap tuple inserted, then the=
=20
> index tuples created. At this time, the duplicate key error occurs,=20
> telling us that we had a conflict and that we have to try an UPDATE=20
> instead. That means, in the end this particular row's INSERT has never=20
> happened and we have to undo the BEFORE INSERT triggers actions too.

But I'm not sure we're supposed to handle that case anyway. Oracle at
least doesn't require an index on the table being merged. And if I look
at it from a visibility view point, if someone else does an INSERT in
another transaction, then MERGE cannot see it and thus it will INSERT
too. This isn't an error.

Consider the case of a deferred unique constraint (Postgres doesn't
support these yet but bear with me). Say in one session you start a
transaction, do a MERGE and then a few other statements. In the
meantime in another session someone inserts a row on the table you
merged. Are you asserting that the first session should undo
everything, do an UPDATE instead of an INSERT and redo all your queries
since? Obviously not.

Though the above relies on something Postgres doesn't support, but you
would be able to emulate the some without a unique key. For example:

Session 1:
CREATE TEMP TABLE foo (id integer, val integer);

BEGIN;
SELECT * FROM foo;

Session 2:
BEGIN;

INSERT INTO foo (1,3);

Session 1:
MERGE INTO foo
  USING (SELECT 1)
  ON (foo.id =3D 1)
  WHEN MATCHED THEN UPDATE SET val =3D 1
  WHEN NOT MATCHED THEN INSERT (id,val) VALUES (1,2);

Session 2:
COMMIT;

Session 1:
COMMIT;

Now, (IMO) in serializable mode, the MERGE should block on reading the
row inserted by the second session and when that commits do the UPDATE,
thus leaving you with a table with one row (1.1).

In read committed mode, the MERGE shouldn't block and you should end up
with a table with two rows (1,3) and (1,2).

If you switch the order of the insert and merge you should get the same
results in both cases, (1,2) and (1,3).

I think you are arguing that the result should be (1,1) in all cases. I
honestly don't see how that is feasible and certainly not supported by
my reading of the standard. I would be interested to know how other
databases handle the above case.

> Not following the semantics is an error. MERGE is not supposed to do=20
> multiple inserts for the same match, concurrency or not.

Yes, any single MERGE cannot insert twice. However, two MERGEs running
concurrently, or a MERGE with an INSERT/UPDATE/DELETE in another
session could very well end up with multiple rows on the same key. I
maintain that MERGE has no special rules w.r.t. visibility or locking
and we should not be acting as if it does. If at the end of the
transaction there a duplicate key we should throw the error and let the
client deal with it.

Have a nice day,
--=20
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.

--2B/JsCI69OhZNC5r
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFDhwAKIB7bNG8LQkwRAvpQAJsHNO/wnfcH0QwNQ7AKXkV5CzCz9wCfVJBH
/iCYKxdxjTOkw6+M2PgPAjY=
=ERXt
-----END PGP SIGNATURE-----

--2B/JsCI69OhZNC5r--

From pgsql-hackers-owner+M76584@postgresql.org Fri Nov 25 09:15:33 2005
Return-path: <pgsql-hackers-owner+M76584@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAPEFWB13482
	for <pgman@candle.pha.pa.us>; Fri, 25 Nov 2005 09:15:32 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id AFCB6C4BC79;
	Fri, 25 Nov 2005 14:15:30 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 039CCD774A
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Fri, 25 Nov 2005 10:14:59 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 13125-10
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Nov 2005 14:15:01 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com [216.136.173.59])
	by svr1.postgresql.org (Postfix) with SMTP id 55EA1D6822
	for <pgsql-hackers@postgresql.org>; Fri, 25 Nov 2005 10:14:55 -0400 (AST)
Received: (qmail 6658 invoked from network); 25 Nov 2005 14:14:55 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=Yahoo.com;
  h=Received:Message-ID:Date:From:User-Agent:X-Accept-Language:MIME-Version:To:CC:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
  b=3IYwOeJgAhkmP/C6LDpv1wsSJCeqr4E0XFCc+cW2u3ZWf6NbmJ19+1NqiyPs/4RVzSH0M8FIPcRZDB1e3bkKQHt3wxdGJrfdxMi5d7DjlC59xirRpZGzUAD3yusU8lg3uE2G/TraLtC6VVpbJo8+rYerPOg+daspmuRqhlcXUjg=  ;
Received: from unknown (HELO jupiter.black-lion.info) (janwieck@68.80.245.191 with login)
  by smtp015.mail.yahoo.com with SMTP; 25 Nov 2005 14:14:55 -0000
Received: from [172.21.8.23] (mars.black-lion.info [192.168.192.101])
	(authenticated bits=0)
	by jupiter.black-lion.info (8.12.10/8.12.9) with ESMTP id jAPEEq1o062023;
	Fri, 25 Nov 2005 09:14:52 -0500 (EST)
	(envelope-from JanWieck@Yahoo.com)
Message-ID: <43871C57.6060000@Yahoo.com>
Date: Fri, 25 Nov 2005 09:14:47 -0500
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.1) Gecko/20040707
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Martijn van Oosterhout <kleptog@svana.org>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net>,
   pgsql-hackers@postgresql.org, Josh Berkus <josh@agliodbs.com>,
   Jaime Casanova <systemguards@gmail.com>
Subject: Re: [HACKERS] someone working to add merge?
References: <c2d9e70e0511110603q1799d811u6e4564be516b10ce@mail.gmail.com> <200511111924.41532.peter_e@gmx.net> <20561.1131734697@sss.pgh.pa.us> <200511112004.35876.peter_e@gmx.net> <20899.1131736841@sss.pgh.pa.us> <4384E54D.8000500@Yahoo.com> <20051124063034.GA18750@svana.org> <4385E636.4070503@Yahoo.com> <20051125121402.GB16970@svana.org>
In-Reply-To: <20051125121402.GB16970@svana.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.359 required=5 tests=[AWL=-0.120,
	DNS_FROM_RFC_ABUSE=0.479]
X-Spam-Score: 0.359
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR

On 11/25/2005 7:14 AM, Martijn van Oosterhout wrote:
> On Thu, Nov 24, 2005 at 11:11:34AM -0500, Jan Wieck wrote:
>> I guess you misunderstood. [...]
> 
> But I'm not sure we're supposed to handle that case anyway. Oracle at
> least doesn't require an index on the table being merged. And if I look
> at it from a visibility view point, if someone else does an INSERT in
> another transaction, then MERGE cannot see it and thus it will INSERT
> too. This isn't an error.

Hmmm ... so you maintain that MERGE without an explicit LOCK TABLE, done 
by the user before performing the MERGE, can create duplicate rows (WRT 
the merge condition) and consequently raise a duplicate key error if 
there is a UNIQUE constraint.

If that is what the standard describes, then it can be implemented 
without any sort of index or constraint requirement. The query tree for 
MERGE will have the INTO relation as a left outer join. In the case of a 
match of this outer join, one set of targetlist expressions is used to 
form the result tuple containing the INTO-relations ctid. That result 
tuple us useable for heap_update() or heap_delete(). In the case of 
no-match another set of target list expressions is used, suitable for 
heap_insert(). This way, MERGE will work with one single sequential scan 
of the INTO relation in case there is no suitable index.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

From pgsql-hackers-owner+M76588@postgresql.org Fri Nov 25 09:55:41 2005
Return-path: <pgsql-hackers-owner+M76588@postgresql.org>
Received: from ams.hub.org (ams.hub.org [200.46.204.13])
	by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id jAPEtfB01898
	for <pgman@candle.pha.pa.us>; Fri, 25 Nov 2005 09:55:41 -0500 (EST)
Received: from postgresql.org (svr1.postgresql.org [200.46.204.71])
	by ams.hub.org (Postfix) with ESMTP id D0574C4BCA2;
	Fri, 25 Nov 2005 14:55:39 +0000 (GMT)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9BABBD7693
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Fri, 25 Nov 2005 10:55:13 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 21958-02
	for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
	Fri, 25 Nov 2005 14:55:16 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svana.org (svana.org [203.20.62.76])
	by svr1.postgresql.org (Postfix) with ESMTP id C3689D7121
	for <pgsql-hackers@postgresql.org>; Fri, 25 Nov 2005 10:55:08 -0400 (AST)
Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian))
	id 1Efeyd-0005xQ-00; Sat, 26 Nov 2005 01:55:03 +1100
Date: Fri, 25 Nov 2005 15:55:03 +0100
From: Martijn van Oosterhout <kleptog@svana.org>
To: Jan Wieck <JanWieck@Yahoo.com>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net>,
   pgsql-hackers@postgresql.org, Josh Berkus <josh@agliodbs.com>,
   Jaime Casanova <systemguards@gmail.com>
Subject: Re: [HACKERS] someone working to add merge?
Message-ID: <20051125145501.GD16970@svana.org>
Reply-To: Martijn van Oosterhout <kleptog@svana.org>
References: <c2d9e70e0511110603q1799d811u6e4564be516b10ce@mail.gmail.com> <200511111924.41532.peter_e@gmx.net> <20561.1131734697@sss.pgh.pa.us> <200511112004.35876.peter_e@gmx.net> <20899.1131736841@sss.pgh.pa.us> <4384E54D.8000500@Yahoo.com> <20051124063034.GA18750@svana.org> <4385E636.4070503@Yahoo.com> <20051125121402.GB16970@svana.org> <43871C57.6060000@Yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="NklN7DEeGtkPCoo3"
Content-Disposition: inline
In-Reply-To: <43871C57.6060000@Yahoo.com>
User-Agent: Mutt/1.3.28i
X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6
X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522  48A7 6394 F08A 0DC6 7BE6
X-PGP-Key-URL: <http://svana.org/kleptog/0DC67BE6.pgp.asc>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Mailing-List: pgsql-hackers
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-Id: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org
Status: OR


--NklN7DEeGtkPCoo3
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Fri, Nov 25, 2005 at 09:14:47AM -0500, Jan Wieck wrote:
> Hmmm ... so you maintain that MERGE without an explicit LOCK TABLE, done=
=20
> by the user before performing the MERGE, can create duplicate rows (WRT=
=20
> the merge condition) and consequently raise a duplicate key error if=20
> there is a UNIQUE constraint.
>=20
> If that is what the standard describes, then it can be implemented=20
> without any sort of index or constraint requirement. The query tree for=
=20
> MERGE will have the INTO relation as a left outer join. In the case of a=
=20
> match of this outer join, one set of targetlist expressions is used to=20
> form the result tuple containing the INTO-relations ctid. That result=20
> tuple us useable for heap_update() or heap_delete(). In the case of=20
> no-match another set of target list expressions is used, suitable for=20
> heap_insert(). This way, MERGE will work with one single sequential scan=
=20
> of the INTO relation in case there is no suitable index.

Yes, that's the way I read the standard and how I was thinking it could
be implemented. It does simplify the case suggested by people that want
atomic REPLACE, because you only have one statement to repeat until you
get success.

do
   MERGE ...;
while( not error and modified_rows <> 1 )

I was thinking that we could make a seperate REPLACE command which can
only insert or update one row, but can do it atomically. Basically a
loop like above with builtin savepoints.

Alternativly, we could special case the MERGE-without-USING case, in
the cases where the plan simply devolves into an Index Scan, but I
don't like special casing. If we're going to have special semantics we
should have a seperate statement so it's clear that it's special.

Have a nice day,
--=20
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.

--NklN7DEeGtkPCoo3
Content-Type: application/pgp-signature
Content-Disposition: inline

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

iD8DBQFDhyXEIB7bNG8LQkwRAlxiAJ4tVMSPKRELg0H7na778cppeU1opACgkawW
xsYY7O6aMVjdXT4ye3fS6KI=
=XS8K
-----END PGP SIGNATURE-----

--NklN7DEeGtkPCoo3--

