Ron Johnson wrote:
> 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.
Sure will they.
Care to elaborate a bit about the side effects of SERIALIZABLE? I mean
semantics AND performance wise ... people tend to use suggestions like
this without thinking (about the consequences).
Jan :-T
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #