Re: psql sequence question

On Mon, Jun 16, 2003 at 11:24:28 -0400,
Jodi Kanter <jkanter(at)virginia(dot)edu> wrote:
> If I'm using transactions (not autocommit), are sequences atomic?

Yes.

> In other words, after inserting a record to a table that
> uses sequence A, am I guaranteed that select last_value on
> sequence A is atomic, and cannot be interfered with by other
> transactions using that same sequence? Sequence A is used by
> several tables.

Probably not in the way you mean. Every transaction is going to see
a consistant view of the sequence table. However transactions proceeding
in parallel may seem the same value for the last value. To make this
work you would need to use serializable mode to do any updates based
on the value of the last value or lock the table exclusively to prevent
concurrent updates. This defeats the function of sequences providing
unique values using light weight locking.

The right way to use sequences is to use nextval to get new values
and use currval to reuse the value you got from the latest call to
nextval in the same session.