Thread: psql sequence question

If I'm using transactions (not autocommit), are sequences atomic?

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.

Thanks,
Jodi

On Mon, Jun 16, 2003 at 11:24:28 -0400,
  Jodi Kanter <jkanter@virginia.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.

At 17:24 16.06.2003, Jodi Kanter said:
--------------------[snip]--------------------

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

Yes. But you should use currval('seq_name') since this guarantees to return
the last value returned to the current connection. Check the recent posts -
there was a discussion on just this topic.

--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/

so it sounds like I just need to tell my programmer friend to change his use of last value to currval instead?
I just hate silly mistakes like this! Thanks for the help.
Jodi


Bruno Wolff III wrote:

On Mon, Jun 16, 2003 at 11:24:28 -0400,
  Jodi Kanter <jkanter@virginia.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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Wed, Jun 18, 2003 at 09:49:58 -0400,
  Jodi Kanter <jkanter@virginia.edu> wrote:
> so it sounds like I just need to tell my programmer friend to change his
> use of last value to currval instead?

This is most likely what you want to do.