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
--
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu