This has been discussed for many times on this list, but shortly: when inserting a new row, there's no previous row to select for update. If you have 2 concurrent transactions, both of them can execute the select for update at the same time, select nothing, and then try to insert the same key, and bang: one of them fails.
Cheers, Csaba. On Mon, 2003-07-14 at 11:31, Shridhar Daithankar wrote: > On 14 Jul 2003 at 5:18, Mike Mascari wrote: > > > I agree. However a common scenario that has appeared on these lists is > > a request for an atomic 'CREATE IF NOT EXISTS, ELSE REPLACE' without > > race conditions. Because Oracle doesn't rollback the transaction, it > > is implementable in SQL. For PostgreSQL, you either need to use > > various locking techniques which reduces concurrency or be prepared to > > resubmit the entire transaction. Savepoints and/or nested transactions > > may alleviate the situation in the future, however. > > Recognising the need of such, SQL standard has been extended to accommodate a > merge command which is create if not exists else update types. > > Correct me if I am wrong.. > > BTW, what's wrong with select for update in such scenario? > > > > > Bye > Shridhar > > -- > Feel free to contact me (flames about my english and the useless of thisdriver > will be redirected to /dev/null, oh no, it's full...).(Michael Beck, describing > the PC-speaker sound device) > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html