Re: Replaceing records

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.

So you have to go into a loop and try INSERTorUPDATEorINSERT... until
you either get bored or succeed ... that's not exactly what I call a
solution.

Jan

Csaba Nagy wrote:
> [rant mode]
> I have to answer this: I'm not trying to use a non-standard feature, I
> try to solve a problem. Namely to be able to try to insert and on
> failure continue the transaction. This is by no means a non-standard
> feature.
> AFAIKT the standard says nothing about rolling back automatically a
> transaction on error, it just says that YOU should be able to roll it
> back or commit it, and then all or nothing of the changes should be
> executed.
> The application design can be "fixed", but that means ugly workarounds.
> In my case a simple fix would be to always insert all the possible
> records before any update would happen, but that would bloat the table
> 10-fold - I think you agree this is unacceptable.
> Please understand me: I'm not after pissing off the postgres developers
> by telling Postgres is not up to it, I try to insist that nested
> transactions are a very important feature, which can solve lots of
> problems which apparently might have nothing to do with nested
> transactions.
>
> Cheers,
> Csaba.
>
>
> On Fri, 2003-09-05 at 04:38, Jan Wieck wrote:
>> Whatever you guy's try or suggest, it's doomed to suffer.
>>
>> The whole problem stems from using a non-standard feature. And in my
>> opinion MySQL's "REPLACE INTO" is less a feature or extension to the
>> standard than more another stupid and lesser thought through addition of
>> apparently speed gaining crap at the cost of proper design.
>>
>> One possible reason why this sort of "feature" was left out of the SQL
>> standard could be that the source of an ID, that is supposed to be
>> unique in the end, should by default ensure it's uniqueness. Defining a
>> column UNIQUE is a last line of defense, and aborted actions because of
>> constraint violation should be the exception, not the normal mode of
>> operation. If it's the DB to ensure uniqueness, it has to generate the
>> ID and one can use a sequence. If it's the application to generate it,
>> the application should know if this is an INSERT or an UPDATE.
>>
>> Wherever one is using this "REPLACE INTO" language violation, the client
>> application or even something in front of it is generating ID's but it's
>> not sure if it is sending down a new or existing one. The real question
>> is "why is this piece of garbage unable to tell the ID is newly created
>> or has to exist already?"
>>
>> I don't think there should be a way to subsitute this. Fix the
>> application design instead.
>>
>>
>> Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #