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
>