Re: how do I update or insert efficently in postgres

In article <3557.1005685484@sss.pgh.pa.us>, Tom Lane wrote:
> marc@oscar.eng.cv.net (Marc Spitzer) writes:
>> I need to do the follwoing logic for a db I am building:
>> if row exists update some fields 
>> else insert all fields
> 
>> I have come across this befor and have used select to drive the
>> choice, if I could get the row update else insert.
> 
> Which case do you think will be more common?  If UPDATE is the more
> common scenario then it's a win to do
> 
>     UPDATE set modifiable-fields = whatever WHERE key = whatever
>     if (zero rows updated)
>         INSERT ...
> 
> Alternatively you can do
> 
>     INSERT ...
>     if (fail due to duplicate key)
>         UPDATE ...
> 
> if you think INSERT is the more common case.  (This all assumes you
> have a unique key for the table, but if you don't, then what do you
> mean by "the row already exists"?)
> 
> Neither of these are perfect, however.  The former has a race condition
> if two clients might try to insert the same key at about the same time.
> You can improve it to
> 
>     BEGIN;
>     UPDATE set modifiable-fields = whatever WHERE key = whatever
>     if (zero rows updated)
>     {
>         INSERT ...
>         if (fail due to duplicate key)
>         {
>             ABORT;
>             loop back to BEGIN;
>         }
>     }
>     COMMIT;
> 
> but this is kinda ugly.  (Of course, if you could have two clients
> independently inserting/updating the same row at the same time, you
> have problems anyway: which one should win, and why?  I think the
> coding difficulty may tell you you have a design problem.)
> 
> As for the INSERT-then-UPDATE approach, you have the same problem
> that you have to ABORT and start a new transaction if the INSERT
> fails.  This is uncool if you really want the whole thing to be part
> of a larger transaction.
> 
> But as long as you've guessed right about which case is more common,
> you have only one query most of the time.
> 
>             regards, tom lane
> 

after the first pass it will be mostly updates and I know I have a
flawed design, but I have only 1 batch job that loads information so
there should be no major problems there, yes I know famious last
words. And I need to get something finished quickly to start
generating reports off of it.  

Thanks 

marc



> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly