Re: Re: Race conditions... - Mailing list pgsql-general

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.