See? So now that INSERT statement will insert the row if it doesn't exist,> or insert zero rows if it does. You are then guaranteed that your> transaction will not fail and rollback, so you can repeat your update, or do> the insert first and then the update, etc.

Uh, why exactly do you think this is race-free?>

It looks fancy, but AFAICS the SELECT will return info that is correct> as of its starting timestamp; which is not enough to guarantee that the> INSERT won't conflict with another transaction doing the same thing> concurrently.

How about:

INSERT INTO table SELECT 1, 'foo' WHERE NOT EXISTS (SELECT TRUE FROM table> WHERE pkcol=1 FOR UPDATE);

It's a lot more straightforward and has a FOR UPDATE. Can this still cause> unique constraint failures?

Hi Guys,>

I just thought I'd share with you guys a very clever solution to the old>'update row. if no rows affected, then insert the row' race condition>problem. A guy at my work came up with it.>

We were discussing this earlier on -hackers, but no-one could find a>solution that didn't involve locking the entire table around the>update...insert­ commands.>

The problem is that sometimes the row will be inserted by another process>between your update and insert, causing your insert to fail with a unique>constraint violation.>

So, say this is the insert:>

INSERT INTO table VALUES (1, 'foo'); // 1 is in the primary key column>

Rewrite it like this:>

INSERT INTO table SELECT 1, 'foo' EXCEPT SELECT 1, 'foo' FROM table WHERE>pkcol=1;>

See? So now that INSERT statement will insert the row if it doesn't exist,>or insert zero rows if it does. You are then guaranteed that your>transaction will not fail and rollback, so you can repeat your update, or do>the insert first and then the update, etc.>

Hope that's handy for people,>

Chris>

------------------­---------(end of broadcast)---------­------------------>T­IP 1: subscribe and unsubscribe commands go to majordomo@postgresq­l.org

AFAIK the "except" select won't see other inserts in uncommitted> transactions. If those transactions are committed you will end up with the> same problem. You can try it yourself, by manually doing two separate> transactions in psql.

You either have to lock the whole table, or lock at the application layer.> Some time back I suggested a "lock on arbitrary string" feature for> postgresql for this and various other purposes, but that feature probably> wouldn't scale in terms of management (it requires 100% cooperation

There's no "select * from table where pkey=x for insert;" which would

You either have to lock the whole table, or lock at the application > layer. Some time back I suggested a "lock on arbitrary string" feature > for postgresql for this and various other purposes, but that feature > probably wouldn't scale in terms of management (it requires 100% > cooperation amongst all apps/clients involved).>

There's no "select * from table where pkey=x for insert;" which would > block on uncommitted inserts/updates of pkey=x and other selects for > insert/update.>

In contrast "select ... for update" blocks on committed stuff.>

Regards,> Link.>

At 09:55 AM 3/27/03 +0800, Christopher Kings-Lynne wrote:>

-------------------­--------(end of broadcast)---------­------------------> TIP 6: Have you searched our list archives?>

http://archives.pos­tgresql.org>

Uh, why exactly do you think this is race-free?>

It looks fancy, but AFAICS the SELECT will return info that is correct> as of its starting timestamp; which is not enough to guarantee that the> INSERT won't conflict with another transaction doing the same thing> concurrently.

How about user locks? Isn't there something in contrib/ for that??? I>could do a userlock on the primary key, whether it existed or not?