Thread: Sequences in transaction context
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
"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
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>
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?
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
>
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
"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
"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