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

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@pop.jaring.my]
Sent: Wednesday, August 04, 2004 6:49 PM
To: Peter Darley; Mark Harrison; pgsql-general@postgresql.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