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(dot)sykora(at)compaq(dot)com

> -----Original Message-----
> From: NetBeans [mailto:erik(at)cariboulake(dot)com]
> Sent: Tuesday, June 05, 2001 2:21 AM
> To: pgsql-general(at)postgresql(dot)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(at)cariboulake(dot)com
> http://www.cariboulake.com
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>