RE: Exploring sequences (sequence context within a transaction)

curval retreives the current value of the sequence FOR THE CURRENT CLIENT.
There is this no concurrency problem : you can call curval on client 1
afeter having generated many values from the sequence, curval will always
return the same value for the same client.

NH

-----Message d'origine-----
De: NetBeans [SMTP:erik@cariboulake.com]
Date: mardi 5 juin 2001 09:21
�: pgsql-general@postgresql.org
Objet: [GENERAL] Exploring sequences (sequence context within a

transaction)

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

Show quoted text

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly