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

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