Re: how do I update or insert efficently in postgres

In article <3557(dot)1005685484(at)sss(dot)pgh(dot)pa(dot)us>, Tom Lane wrote:
> marc(at)oscar(dot)eng(dot)cv(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly