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(dot)l(dot)johnson(at)cox(dot)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