Re: going crazy with serial type

"Gregory Wood" <gregw@com-stock.com> writes:
> [ excellent explanation ]

Somebody ought to pull out the better parts of this thread and put
together an article for the techdocs.postgresql.org site...

            regards, tom lane


> Yes and/or no. The way currval is handled is a bit... weird at first glance.
> It's that way for a reason though:

> Lets say you have two users, Ann and Bob. Ann inserts a row, which receives
> a value of 1. Bob inserts a row with a value of 2. Ann then inserts a row
> into another table, which references that first table. Because of the
> reference, she wants to use the id value that was just inserted by her,
> which is 1. If currval just grabbed the last sequence value, she would be
> inserting a value of 2, which actually refers to Bob's insert. Bad.

> So the way currval works is it grabs the last value of nextval, as executed
> by the USER (or more specifically, that user's connection, i.e. session).
> This means that when Ann does her insert, the nextval increments to 1 (the
> initial value) and her currval is 1. When Bob does his insert, his nextval
> increments to 2 and he gets a currval of 2, while Ann still retains her
> currval of 1 (since it was Bob's session that incremented nextval to 2 and
> not hers). That way Ann can use the sequence value that she just inserted
> elsewhere, without fear of mistakenly using Bob's id value.

> Now, to go back to the "state_vectors_id_seq.currval is not yet definted in
> this session" error you received. Since the currval is populated by the last
> value of nextval in that session, it can only be used after nextval has been
> called (either explicitly, or through a default serial value). In other
> words, you can only read currval after you perform an INSERT, or explicitly
> call nextval.

> Greg