Re: Last ID

"Brett W. McCoy" <bmccoy@chapelperilous.net> writes:
> On Fri, 2 Mar 2001, Catalin CIOCOIU wrote:
>> How can I get Last ID inserted ???

> currval('<seq name>') is the way to get the value that was last inserted
> into the database.  However, there is no guarantee, in a multiuser
> environment, that the value you got was the value you actually used.

Quite a few people don't seem to understand how currval() and nextval()
work.

1. nextval() advances the sequence object, generating a new value that
   will not be the same as any other nextval() call returns, in either
   this backend or any other one.

2. currval() gives the last value generated by a nextval() *IN THIS
   BACKEND*.  It is undefined until the current backend has done at
   least one nextval() on the sequence object.

There is no "multiuser risk" from either one: in particular, currval()
will give you the value you last generated, regardless of what other
backends may be doing.

> You can also user last_value in an SQL statement, but you still have
> the problem of having multiple backends generating sequence values.

Yes, looking directly at the sequence's last_value does open up race
conditions.

            regards, tom lane