Re: Sequences in transaction context

On Tue, Jun 05, 2001 at 09:22:43PM -0500, 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.
>
> 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?

AFAIK, postgresql uses some magic so the currval() call always returns
the last sequence number used by the current process. This is why
currval() will generate an error in a new session if nextval() hasn't been
called for the given sequence.

--
Eric G. Miller <egm2(at)jps(dot)net>