Re: how do I update or insert efficently in postgres
От | marc@oscar.eng.cv.net (Marc Spitzer) |
---|---|
Тема | Re: how do I update or insert efficently in postgres |
Дата | |
Msg-id | slrn9v37ho.314k.marc@oscar.eng.cv.net обсуждение исходный текст |
Ответ на | Re: how do I update or insert efficently in postgres (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: