Re: most idiomatic way to "update or insert"?

Peter,

The "does not work" part is not refering to the method not working at
all, but to the fact that it is not safe when you have multiple
transactions operating on the same row at the same time.
There are plenty of discussions about the insert-or-update race
conditions on this list, and the final conclusion was always that it is
not possible to solve the race condition without being prepared to catch
exceptions and retry the whole thing until it succedes...
The reason of the race condition: let's say 2 transactions A and B try
to insert-or-update the same row which does not exist. They do the
update statement at the same time, and BOTH OF THEM gets as a result
that no rows were updated, since the row does not exist yet. Now both
transactions try to insert the row, and obviously one of them will fail.
So your code must be prepared that the insert can fail, and in that case
it should retry with the update.
People tried to devise a method to avoid the race condition and throwing
exception, but it is just not possible.
Now the one bad thing in postgres which people complained about in this
context is that the transaction gets rolled back on any error, so
actually instead of just retrying the update, you will have to redo your
whole transaction.

HTH,
Csaba.

On Thu, 2004-08-05 at 15:28, Peter Darley wrote:
> Lincoln,
> It works for me...
> I think what you said is wrong because it updates first (if there is a row
> to update), then inserts. If there is a row to update the insert won't
> insert anything. If there is no row to update the insert inserts a row.
> Either way, the insert is the last thing in the transaction. Plus, as shown
> in the code to follow, I have almost this exact thing in my application and
> I know that it does work for me. :)
>
> Code (Perl):
> $Neo::DB::Neo->do ("UPDATE Sample_Settings SET Value=" . Quote($Args{Value})
> . " WHERE Sample_ID=" . Quote($Args{SampleID}) . " AND Setting=" .
> Quote($Args{Setting}) . "; INSERT INTO Sample_Settings (Sample_ID, Setting,
> Value) (SELECT " . Quote($Args{SampleID}) . ", " . Quote($Args{Setting}) .
> ", " . Quote($Args{Value}) . " WHERE NOT EXISTS (SELECT 1 FROM
> Sample_Settings WHERE Sample_ID=" . Quote($Args{SampleID}) . " AND Setting="
> . Quote($Args{Setting}) . "));");
>
> Thanks,
> Peter Darley
>
> -----Original Message-----
> From: Lincoln Yeoh [mailto:lyeoh(at)pop(dot)jaring(dot)my]
> Sent: Wednesday, August 04, 2004 6:49 PM
> To: Peter Darley; Mark Harrison; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] most idiomatic way to "update or insert"?
>
>
> I don't think that works - there's a race condition if you do not do any
> locking.
>
> Why:
> Before a transaction that inserts rows is committed, other transactions are
> not aware of the inserted rows, so the select returns no rows.
>
> So:
> You can either create a unique index and catch insert duplicate failures.
>
> Or:
> lock the relevant tables, then do the select ... update/insert or insert
> ... select , or whatever it is you want to do.
>
> Or:
> both.
>
> Test it out yourself.
>
> At 07:51 AM 8/5/2004, Peter Darley wrote:
> >Mark,
> > It's not canonical by any means, but what I do is:
> >
> >update foo set thing='stuff' where name = 'xx' and thing<>'stuff';
> >insert into foo (name, thing) (select 'xx' as name, 'stuff' as thing where
> >not exists (select 1 from foo where name='xx'));
> >
> > I believe if you put these on the same line it will be a single
> >transaction. It has the benefit of not updating the row if there aren't
> >real changes. It's plenty quick too, if name is indexed.
> >
> >Thanks,
> >Peter Darley
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org