Re: obtaining primary key/rowid following insert, redux...

Mark Dzmura <mdz(at)digital-mission(dot)com> writes:
> Trying to solve my problem mentioned in the email of half an hour ago,
> I searched through the archives of the interface list and found some
> of your replies suggesting using "currval()" to get the last value
> assigned from a sequence... However, here's what happens in a good
> database with multiple in-use sequences:

> db=# select currval('foo_foo_id_seq');
> ERROR: foo_foo_id_seq.currval is not yet defined in this session

That means you haven't actually done any nextval() yet in this backend,
therefore there is no "last value assigned" yet.

> As an alternative, I discovered that I can get the value this way:

> db=# select last_value from foo_foo_id_seq;
> last_value
> ---------
> 27

> My questions are, (1) why does the currval() approach give the error
> message, and (2) is it OK to use my alternative??

No, because you'd have a race condition across multiple backends.

> Finally, as far as I can tell, there is a real race condition problem
> here in a multiple-connection scenario (e.g. another task can cause
> the sequence to be incremented between the insert and the select)

Not with currval(), because that holds the last value assigned by the
current backend. Looking directly at the sequence's last_value would
indeed have a race problem.

Another way to do it, which might be more convenient than currval()
depending on your application logic, is to do select nextval() to get
a new sequence number assigned, and then explicitly insert that value
into the serial column as you insert the row, rather than relying on
the column's DEFAULT clause to compute it for you. I tend to see this
way as being logically cleaner than the insert-and-then-use-currval
way, but that's a matter of taste.

regards, tom lane