Re: Replaceing records

On Fri, 2003-09-05 at 08:29, Jan Wieck wrote:
> It was not meant against anyone in person and I agree that nested 
> transactions and/or catchable exceptions and continuing afterwards is 
> usefull and missing in PostgreSQL. What Stephan and Richard where 
> actually discussing was more like emulating the REPLACE INTO, and I was 
> responding to that.
> 
> However, even with nested transactions and exceptions and all that, your 
> problem will not be cleanly solvable. You basically have 2 choices, 
> trying the INSERT first and if that fails with a duplicate key then do 
> the UPDATE, or try the UPDATE first and if no rows got hit do an INSERT. 
> Now if 2 concurrent transactions do try the UPDATE they can both not 
> find the row and do INSERT - one has a dupkey error. But if you try to 
> INSERT and get a duplicate key, in the time between you get the error 
> and issue the UPDATE someone else can issue a DELETE - the row is gone 
> and your UPDATE will fail.

SERIALIZABLE transactions will solve this.

-- 
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

Thanks to the good people in Microsoft, a great deal of the data 
that flows is dependent on one company. That is not a healthy 
ecosystem. The issue is that creativity gets filtered through 
the business plan of one company.
Mitchell Baker, "Chief Lizard Wrangler" at Mozilla