Re: Race conditions...

Is currval defined by the session or the current state of the sequence?

I am an Oracle person, and just started using Postgres. I know that in
Oracle using a select currval of a sequence after an insert is *very*
dangerous, especially in the situation that is described here. This is
because Oracle handles it as the current state of the sequence and therefore
if there are any cases where there are two sessions trying to insert into
the same table, close to or at the same time, then the following could
easily happen:

1. (session1)insert into foo... (assigns sequence number 1)
2. (session2)insert into foo... (insert of other sesssion performed
immediately after first insert,
assigns seq number 2)
3. (session1)select currval (curval currently 2!)

I know this has nothing to do with the original poster's question, but what
I saw scared me a little. In the case above I have been using the oid of
the inserted row or explicitly retrieving the sequence number before the
insert so that you have it throughout the session. Am I wrong in assuming
that the currval acts this way?

Thanks!
Darrin

From: "K Parker" <kparker@eudoramail.com>
Reply-To: kparker@eudoramail.com
To: pgsql-general@hub.org
Subject: [GENERAL] Re: Race conditions...
Date: Sat, 09 Sep 2000 09:35:05 -0700

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

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at
http://profiles.msn.com.