RE: Exploring sequences (sequence context within a tran saction)
Erik,
If your bar column has unique values, perhaps you could SELECT foo
FROM foobar where bar='whatever'; after your insert. Or perhaps write a
procedure that stores the currval into a variable named by user/session/etc
if this is possible.
dale.sykora@compaq.com
Show quoted text
-----Original Message-----
From: NetBeans [mailto:erik@cariboulake.com]
Sent: Tuesday, June 05, 2001 2:21 AM
To: pgsql-general@postgresql.org
Subject: [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
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
On Tue, Jun 12, 2001 at 02:08:13PM -0500,
"Sykora, Dale" <Dale.Sykora@COMPAQ.com> wrote:
Erik,
If your bar column has unique values, perhaps you could SELECT foo
FROM foobar where bar='whatever'; after your insert. Or perhaps write a
procedure that stores the currval into a variable named by user/session/etc
if this is possible.
currval is already tied to the current session. You can use it to refer
to the last serial value used in the current session.