On Thu, Apr 19, 2007 at 08:27:30PM +0200, Markus Holzer wrote:
> How do I perform a conditional insert/update?
The short answer is that you can't, at least not the way you want to.
> already in the table, or an update if it is. I have currently solved this by
> SELECTing for the primary key, then looking if there is a row, and if there
> is I do an UPDATE otherwise I do an INSERT. But since this is a web app this
> way of course leaves a big race condition.
Only if you don't do it in one transaction. If you use a
savepoint to do this, then you can BEGIN, SELECT FOR UPDATE, then if
you get it UPDATE; if you don't, start a savepoint, insert, and if
you don't get a failure, commit the whole thing. If you _do_ get a
failure, you rollback the savepoint, and then do the update. There's
a (cleaner) example of this in the UPDATE doc from the 8.2 release.
But I have doubts that your program design is right if this is the
approach you're trying to take (it's not impossible that it's the
right way, just that this is often a workaround for a dodgy data
model in the first place). What is the conflict you're trying to
avoid?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun