> An "update or insert" would be useful sometimes, but it's not always
> necessary. Indeed, if I find I don't know whether I'm adding or updating
> something I take a long hard look at my design - it ususally means I've
> not thought clearly about something.
...
> Can you give an actual example of where you need this?
We have an environment where our data collection occurs by screen scraping
(er, web scraping?). Unfortunately, it takes two passes, once across
search results which provide partial data, then a second time over a
detail page loaded for each item in the search results we were given.
Since time is of the essence, we provide the partial data to our
customers, which means dealing with the insert or update. Additionally,
the process is multithreaded, so search results can be touching things
concurrently with details being loaded, otherwise we can't keep up.
I dealt with the problem by wrapping every touch of an item in a single
transaction with a loop around it, as has been recommended here many times
before. Any DB-exception (Python) inside the loop caused by concurrency
type problems causes a restart. As it turns out, the insert/update race
has yet to result in a retry. The real payoff in this design has proven to
be dealing with FK locking... without putting way more effort into fixing
it than the deadlocks are worth, we get around a dozen deadlocks a day
that are automatically retried.
