On Wed, 2005-06-08 at 05:31, Joseph Shraibman wrote:
> I want to do the following:
>
> BEGIN;
> SELECT ... FROM table WHERE a = 1 FOR UPDATE;
> UPDATE table SET ... WHERE a = 1;
> if that resturns zero then
> INSERT INTO table (...) VALUES (...);
> END;
>
> The problem is that I need to avoid race conditions. Sometimes I get
> primary key exceptions on the INSERT.
>
> I think I need to lock the table in share mode to keep inserts from
> happening, but that blocks vacuums, and blocked vacuums block other
> things behind them. So how do I get around this?
>
I think there's no way to avoid the race condition, I got to this
conclusion while following past discussions (which were many of them,
look for "insert or update" for example).
There is though a solution starting with 8.0, namely you should place a
save point before the inserts which are susceptible to fail, and roll
back to that save point on failure. Then you can update while still
keeping the transaction running. The strategy would be then "insert
first, update if failed".
HTH,
Csaba.