Horton
Menu

Re: UPSERT

Hannu Krosing <hannu@skype.net>

View thread
From: Hannu Krosing <hannu@skype.net>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Andrew Dunstan <andrew@dunslane.net>, Jonathan Scher <js@oxado.com>, pgsql-hackers@postgresql.org
Date: 2007-03-04T13:54:54Z
Lists: pgsql-hackers 
Ühel kenal päeval, P, 2007-03-04 kell 07:46, kirjutas Bruno Wolff III:
> On Sun, Mar 04, 2007 at 14:55:47 +0200,
>   Hannu Krosing <hannu@skype.net> wrote:
> > 
> > UPDATE
> > IF NOT FOUND THEN 
> >   INSERT
> >   IF DUPLICATE KEY THEN
> >     UPDATE
> >   END IF
> > END IF
> 
> I believe it is possible for the above to fail. For example another
> transaction could create a matching record between the update and insert
> and then another transaction could delete it between the insert and the
> second update.

Then we may do the second part as a loop and hope that eventually we hit
the right point with either INSERT or UPDATE:

 UPDATE
 WHILE NOT FOUND THEN 
   INSERT
   IF DUPLICATE KEY THEN
     UPDATE
   END IF
 END WHILE

-- 
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com