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