Re: Race conditions...

> Whenever I do inserts like yours, I do it in
> this kind of manner (I tried
> to use your pseudocode style):

> SELECT ID FROM ITEM WHERE URL='X' FOR UPDATE
> IF (ROW RETURNED) {
> $ID = ITEM.ID
> } ELSE {
> INSERT INTO ITEM ...
> GET THE OID [via PQoidStatus or your
> environment's equivalent]
> SELECT ID FROM ITEM WHERE oid = [THE OID WE > GOT FROM ABOVE]
> $ID = ITEM.ID
> }

2 comments: the SELECT via the OID is unnecessary if the ID you're looking for is based on a sequence, you can just say:

SELECT currval('sequence_name');

and get what you're looking for with only
a single call.

More importantly, while your pseudocode is a perfectly valid way of doing things, it still doesn't solve the race condition. It really seems that SQL itself is seriously lacking something here--the ability to atomically perform a UNIQUE insertion--but you can come pretty close in PostgreSQL with the right combination of unique indices. This requires you to turn your code inside out from the order both you and the original poster use:

insert into item (...) values (...)
-- above insert OMITS the ID field
if an error occurs
{
if it's a duplicate-key error
{
select ... from item ... for update
update item set x = y where
}
panic -- something bad happened
}

Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com