Re: Sequences in transaction context

On Tue, 5 Jun 2001, Erik Pearson wrote:

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

No, because currval should return the value of the sequence last given to
your session.  If it doesn't that's a problem (and not one I've noticed
reported before).

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

I'm not sure how you're testing this precisely, but none of the tests I've
tried has ever given this kind of result.  Can you give a particular
sequence of events in multiple transactions you've done that's given you
the wrong currval results?