Re: psql sequence question

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(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.
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter(at)virginia(dot)edu <mailto:jkanter(at)virginia(dot)edu>