Thread

1. Sequences in transaction context

   Erik Pearson <erik@cariboulake.com> — 2001-06-06T02:22:43Z

   I searched through mailing list archives but was unable to find full
   coverage of this question -- my apologies if this is a reposted question.
   
   As in the FAQ, I am trying to retrieve the value of a sequence value from a
   newly inserted row.  So, first I call something like:
   
       insert into foobar (foo, bar)
       values (nextval('foobar_foo_seq'), 'whatever');
   
   Then, I want to retrieve the value that generated from the sequence and
   inserted into the table, so I use a call to currval:
   
       insert into foobar_rel_table(foo_fk, baz)
       values (currval('foobar_foo_seq', 'something else');
   
   This is (one of the methods that is) prescribed in the FAQ.  However, I'm
   concerned that another transaction attempting to insert into the same table
   might make a call to nextval('foobar_foo_seq') between the two operations
   above.  This would mean that my second statement would use the wrong value
   from the sequence.
   
   I've tested this scenario with different transaction isolation levels, and
   it appears that any state changes to sequences become immediately visible to
   other transactions (obviously, a read-commited type strategy wouldn't work,
   however, serializing access to sequences, or explicit locking would solve
   this problem).
   
   Has anyone else come across this problem, and is there a workaround?  If
   not, are there any alternate suggestions for generating a PK on insert and
   immediately retrieving it that is free from concurrency issues?
   
   Any help would be appreciated.  Thanks!  -- Erik
   
   --
   Erik Pearson
   erik@cariboulake.com
   http://www.cariboulake.com
   
   
   
   
   
   
   

2. Re: Sequences in transaction context

   Eric G. Miller <egm2@jps.net> — 2001-06-06T02:57:50Z

   On Tue, Jun 05, 2001 at 09:22:43PM -0500, Erik Pearson wrote:
   > I searched through mailing list archives but was unable to find full
   > coverage of this question -- my apologies if this is a reposted question.
   > 
   > As in the FAQ, I am trying to retrieve the value of a sequence value from a
   > newly inserted row.  So, first I call something like:
   > 
   >     insert into foobar (foo, bar)
   >     values (nextval('foobar_foo_seq'), 'whatever');
   > 
   > Then, I want to retrieve the value that generated from the sequence and
   > inserted into the table, so I use a call to currval:
   > 
   >     insert into foobar_rel_table(foo_fk, baz)
   >     values (currval('foobar_foo_seq', 'something else');
   > 
   > This is (one of the methods that is) prescribed in the FAQ.  However, I'm
   > concerned that another transaction attempting to insert into the same table
   > might make a call to nextval('foobar_foo_seq') between the two operations
   > above.  This would mean that my second statement would use the wrong value
   > from the sequence.
   > 
   > I've tested this scenario with different transaction isolation levels, and
   > it appears that any state changes to sequences become immediately visible to
   > other transactions (obviously, a read-commited type strategy wouldn't work,
   > however, serializing access to sequences, or explicit locking would solve
   > this problem).
   > 
   > Has anyone else come across this problem, and is there a workaround?  If
   > not, are there any alternate suggestions for generating a PK on insert and
   > immediately retrieving it that is free from concurrency issues?
   
   AFAIK, postgresql uses some magic so the currval() call always returns
   the last sequence number used by the current process.  This is why
   currval() will generate an error in a new session if nextval() hasn't been
   called for the given sequence.
   
   -- 
   Eric G. Miller <egm2@jps.net>
   
   

3. Re: Sequences in transaction context

   Doug McNaught <doug@wireboard.com> — 2001-06-06T03:14:06Z

   "Erik Pearson" <erik@cariboulake.com> writes:
   
   > I searched through mailing list archives but was unable to find full
   > coverage of this question -- my apologies if this is a reposted question.
   > 
   > As in the FAQ, I am trying to retrieve the value of a sequence value from a
   > newly inserted row.  So, first I call something like:
   > 
   >     insert into foobar (foo, bar)
   >     values (nextval('foobar_foo_seq'), 'whatever');
   > 
   > Then, I want to retrieve the value that generated from the sequence and
   > inserted into the table, so I use a call to currval:
   > 
   >     insert into foobar_rel_table(foo_fk, baz)
   >     values (currval('foobar_foo_seq', 'something else');
   > 
   > This is (one of the methods that is) prescribed in the FAQ.  However, I'm
   > concerned that another transaction attempting to insert into the same table
   > might make a call to nextval('foobar_foo_seq') between the two operations
   > above.  This would mean that my second statement would use the wrong value
   > from the sequence.
   
   This does not happen.  I just tested it:
   
   [doug@shaggy doug]$ createdb foo
   CREATE DATABASE
   [doug@shaggy doug]$ psql foo
   Welcome to psql, the PostgreSQL interactive terminal.
   
   Type:  \copyright for distribution terms
          \h for help with SQL commands
          \? for help on internal slash commands
          \g or terminate with semicolon to execute query
          \q to quit
   
   foo=# create sequence foo_seq;
   CREATE
   foo=# select nextval('foo_seq');
    nextval 
   ---------
          1
   (1 row)
   
   foo=# select currval('foo_seq');
    currval 
   ---------
          1
   (1 row)
   
   At this point, in another window, I do:
   
   foo=# select nextval('foo_seq');
    nextval 
   ---------
          2
   (1 row)
   
   Back to the first window:
   
   foo=# select currval('foo_seq');
    currval 
   ---------
          1
   (1 row)
   
   Are you not seeing this behavior?  Since you mention the FAQ, question 
   4.16.3 addresses this very issue in very clear language.
   
   Welcome to MVCC...
   
   -Doug
   -- 
   The rain man gave me two cures; he said jump right in,
   The first was Texas medicine--the second was just railroad gin,
   And like a fool I mixed them, and it strangled up my mind,
   Now people just get uglier, and I got no sense of time...          --Dylan
   
   

4. Re: Sequences in transaction context

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-06-06T03:16:36Z

   On Tue, 5 Jun 2001, Erik Pearson wrote:
   
   > I searched through mailing list archives but was unable to find full
   > coverage of this question -- my apologies if this is a reposted question.
   > 
   > As in the FAQ, I am trying to retrieve the value of a sequence value from a
   > newly inserted row.  So, first I call something like:
   > 
   >     insert into foobar (foo, bar)
   >     values (nextval('foobar_foo_seq'), 'whatever');
   > 
   > Then, I want to retrieve the value that generated from the sequence and
   > inserted into the table, so I use a call to currval:
   > 
   >     insert into foobar_rel_table(foo_fk, baz)
   >     values (currval('foobar_foo_seq', 'something else');
   > 
   > This is (one of the methods that is) prescribed in the FAQ.  However, I'm
   > concerned that another transaction attempting to insert into the same table
   > might make a call to nextval('foobar_foo_seq') between the two operations
   > above.  This would mean that my second statement would use the wrong value
   > from the sequence.
   
   No, because currval should return the value of the sequence last given to
   your session.  If it doesn't that's a problem (and not one I've noticed
   reported before).
   
   > I've tested this scenario with different transaction isolation levels, and
   > it appears that any state changes to sequences become immediately visible to
   > other transactions (obviously, a read-commited type strategy wouldn't work,
   > however, serializing access to sequences, or explicit locking would solve
   > this problem).
   
   I'm not sure how you're testing this precisely, but none of the tests I've
   tried has ever given this kind of result.  Can you give a particular
   sequence of events in multiple transactions you've done that's given you
   the wrong currval results?
   
   
   

5. RE: Sequences in transaction context

   Erik Pearson <erik@cariboulake.com> — 2001-06-06T03:33:08Z

   Thanks for the reply!  I'm afraid I didn't test this thoroughly.  I noticed
   that nextval changed the sequence for all transactions, and assumed that
   currval simply translated to the (nextval() - 1), which it does not, as I
   now understand it.  I was also unclear on the FAQ -- I guess I thought that
   "race condition" referred to concurrent access to the underlying store,
   either delivering duplicate values or something.
   
   One last followup question -- what's MVCC?
   
     -- Erik
   
   > -----Original Message-----
   > From: doug@belphigor.mcnaught.org [mailto:doug@belphigor.mcnaught.org]On
   > Behalf Of Doug McNaught
   > Sent: Tuesday, June 05, 2001 10:14 PM
   > To: Erik Pearson
   > Cc: pgsql-general@postgresql.org
   > Subject: Re: [GENERAL] Sequences in transaction context
   >
   >
   > "Erik Pearson" <erik@cariboulake.com> writes:
   >
   > > I searched through mailing list archives but was unable to find full
   > > coverage of this question -- my apologies if this is a reposted
   > question.
   > >
   > > As in the FAQ, I am trying to retrieve the value of a sequence
   > value from a
   > > newly inserted row.  So, first I call something like:
   > >
   > >     insert into foobar (foo, bar)
   > >     values (nextval('foobar_foo_seq'), 'whatever');
   > >
   > > Then, I want to retrieve the value that generated from the sequence and
   > > inserted into the table, so I use a call to currval:
   > >
   > >     insert into foobar_rel_table(foo_fk, baz)
   > >     values (currval('foobar_foo_seq', 'something else');
   > >
   > > This is (one of the methods that is) prescribed in the FAQ.
   > However, I'm
   > > concerned that another transaction attempting to insert into
   > the same table
   > > might make a call to nextval('foobar_foo_seq') between the two
   > operations
   > > above.  This would mean that my second statement would use the
   > wrong value
   > > from the sequence.
   >
   > This does not happen.  I just tested it:
   >
   > [doug@shaggy doug]$ createdb foo
   > CREATE DATABASE
   > [doug@shaggy doug]$ psql foo
   > Welcome to psql, the PostgreSQL interactive terminal.
   >
   > Type:  \copyright for distribution terms
   >        \h for help with SQL commands
   >        \? for help on internal slash commands
   >        \g or terminate with semicolon to execute query
   >        \q to quit
   >
   > foo=# create sequence foo_seq;
   > CREATE
   > foo=# select nextval('foo_seq');
   >  nextval
   > ---------
   >        1
   > (1 row)
   >
   > foo=# select currval('foo_seq');
   >  currval
   > ---------
   >        1
   > (1 row)
   >
   > At this point, in another window, I do:
   >
   > foo=# select nextval('foo_seq');
   >  nextval
   > ---------
   >        2
   > (1 row)
   >
   > Back to the first window:
   >
   > foo=# select currval('foo_seq');
   >  currval
   > ---------
   >        1
   > (1 row)
   >
   > Are you not seeing this behavior?  Since you mention the FAQ, question
   > 4.16.3 addresses this very issue in very clear language.
   >
   > Welcome to MVCC...
   >
   > -Doug
   > --
   > The rain man gave me two cures; he said jump right in,
   > The first was Texas medicine--the second was just railroad gin,
   > And like a fool I mixed them, and it strangled up my mind,
   > Now people just get uglier, and I got no sense of time...          --Dylan
   >
   
   
   

6. Re: Sequences in transaction context

   Doug McNaught <doug@wireboard.com> — 2001-06-06T03:36:54Z

   "Erik Pearson" <erik@cariboulake.com> writes:
   
   > Thanks for the reply!  I'm afraid I didn't test this thoroughly.  I noticed
   > that nextval changed the sequence for all transactions, and assumed that
   > currval simply translated to the (nextval() - 1), which it does not, as I
   > now understand it.  I was also unclear on the FAQ -- I guess I thought that
   > "race condition" referred to concurrent access to the underlying store,
   > either delivering duplicate values or something.
   > 
   > One last followup question -- what's MVCC?
   
   Multi-Version Concurrency Control.  See:
   
   http://postgresql.crimelabs.net/users-lounge/docs/7.1/postgres/mvcc.html
   
   The currval/nextval stuff actually isn't strict MVCC (since it doesn't 
   require that the usages be inside a transaction) but it's the same
   concept. 
   
   -Doug
   -- 
   The rain man gave me two cures; he said jump right in,
   The first was Texas medicine--the second was just railroad gin,
   And like a fool I mixed them, and it strangled up my mind,
   Now people just get uglier, and I got no sense of time...          --Dylan
   
   

7. Re: Sequences in transaction context

   Neil Conway <nconway@klamath.dyndns.org> — 2001-06-06T03:45:06Z

   On Tue, Jun 05, 2001 at 10:33:08PM -0500, Erik Pearson wrote:
   > One last followup question -- what's MVCC?
   
   http://www.postgresql.org/idocs/index.php?mvcc.html
   
   Cheers,
   
   Neil
   
   
   

8. Re: Sequences in transaction context

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-06T05:32:42Z

   "Erik Pearson" <erik@cariboulake.com> writes:
   > Thanks for the reply!  I'm afraid I didn't test this thoroughly.  I noticed
   > that nextval changed the sequence for all transactions, and assumed that
   > currval simply translated to the (nextval() - 1), which it does not, as I
   > now understand it.
   
   No.  Each backend process caches the last nextval() result it got for
   every sequence object it's ever nextval'd, and this data is what
   currval() consults.  That's the only way to ensure cross-process
   consistency.  What you see in currval is the last value you obtained,
   regardless of what anyone else is doing.
   
   > One last followup question -- what's MVCC?
   
   Multi-version concurrency control.  See
   http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/mvcc.html
   or my slides on Postgres' transaction model at
   http://www.ca.postgresql.org/osdn/index.html
   
   			regards, tom lane