Re: INSERT WHERE NOT EXISTS

scott.marlowe wrote:
> On Wed, 25 Jun 2003, Ian Barwick wrote:
>
>>On Wednesday 25 June 2003 21:37, Mike Mascari wrote:
>>
>>>I proposed that same solution 3 years ago. Tom shoots it down:
>>>
>>>http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3A4D611
>>>6.1A613402%40mascari.com&rnum=1&prev=/groups%3Fq%3DMike%2BMascari%2BINSERT%2
>>>BNOT%2BEXISTS%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den
>>>
>>>Reuben must be prepared for unique key violation, I'm afraid. And,
>>>despite the optimism in the link, we still don't have savepoints. :-(
>>
>>aha, useful to know. Thanks.
>
> Oh yeah, in my example you need to do a select for update to be race safe.
>

But if two simultaneous "selects for update" fail to find rows, both
clients will then attempt the INSERT, which will cause one of them to
abort due to a unique key violation. In these "replace" scenarios, the
application must be prepared for the unique key violation with the
current version of PostgreSQL.

Mike Mascari
mascarm@mascari.com