Re: going crazy with serial type

> OK, next question.  I'm trying to use nextval/currval and I'm getting
> this:
>
> search_info=# select currval('state_vectors_id_seq');
> ERROR:  state_vectors_id_seq.currval is not yet defined in this session
> search_info=# select id from state_vectors;
>  id
> ----
>   1
> (1 row)
>
>
> shouldn't the first select have returned 1?

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