Sequences in transaction context

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>

"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, 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

"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

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.

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