Re: UPSERT

Simon Riggs <simon@2ndquadrant.com>

From: "Simon Riggs" <simon@2ndquadrant.com>

To: "Tom Lane" <tgl@sss.pgh.pa.us>

Cc: "Heikki Linnakangas" <heikki@enterprisedb.com>, "Andrew Dunstan" <andrew@dunslane.net>, "Jonathan Scher" <js@oxado.com>, <pgsql-hackers@postgresql.org>

Date: 2007-03-02T18:39:22Z

Lists: pgsql-hackers

On Fri, 2007-03-02 at 13:19 -0500, Tom Lane wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > Seems like we should try to locate a row first, then INSERT if we cannot
> > find one. That's slower on INSERT but more balanced overall
> 
> Except it still has the race condition.

I'm not saying it didn't; but dropping in two dead copies of a tuple
isn't acceptable either.

> > I'm a bit surprised the TODO didn't mention the MERGE statement, which
> > is the SQL:2003 syntax for specifying this as an atomic statement.
> 
> I believe we concluded that MERGE doesn't actually do quite what people
> want/expect.  Please go back and read the archives.

Yes, it was my thread. I recall that there wasn't any acceptable answer
to how it could be done with reasonable efficiency.

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com