Thread: Last ID

How can I get Last ID inserted ???
The problem is for a multiuser(symultans) database. Exist a statment SQL
witch get the last id inserted for a session ?

Thanks !

On Fri, 2 Mar 2001, Catalin CIOCOIU wrote:

> How can I get Last ID inserted ???
> The problem is for a multiuser(symultans) database. Exist a statment SQL
> witch get the last id inserted for a session ?

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.  You
can also user last_value in an SQL statement, but you still have the
problem of having multiple backends generating sequence values.

A more reliable way is to explicitly call nextval('<seq name>') and use
the value returned to insert into the database.

Please see the CREATE SEQUENCE documentation at
http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createsequence.htm

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
While my BRAINPAN is being refused service in BURGER KING, Jesuit
priests are DATING CAREER DIPLOMATS!!

"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

"Brett W. McCoy" wrote:
>
> On Fri, 2 Mar 2001, Catalin CIOCOIU wrote:
>
> > How can I get Last ID inserted ???
> > The problem is for a multiuser(symultans) database. Exist a statment SQL
> > witch get the last id inserted for a session ?
>
> 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.  You
> can also user last_value in an SQL statement, but you still have the
> problem of having multiple backends generating sequence values.
>
> A more reliable way is to explicitly call nextval('<seq name>') and use
> the value returned to insert into the database.

    This method don't encourage using serial data type. Practically, in the
joined table I can't use "serial" data type for primary key. I nead to
use sequences by hand.
    The last solution work fine, but I need a variable for store the
nextval('<seq name>').

There is not a other solution ?




>
> Please see the CREATE SEQUENCE documentation at
> http://www.postgresql.org/users-lounge/docs/7.0/user/sql-createsequence.htm
>
> -- Brett
>                                      http://www.chapelperilous.net/~bmccoy/
> ---------------------------------------------------------------------------
> While my BRAINPAN is being refused service in BURGER KING, Jesuit
> priests are DATING CAREER DIPLOMATS!!

On Fri, 2 Mar 2001, Tom Lane wrote:

> 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.

How ironic... I got into an argument not too long ago with someone on the
Perl-DBI list who insisted that currval could not be reliably used in a
multi-user environment and I argued what you say above.  I eventually
conceded the argument, but am glad to know that I was right all along.
But now I feel bad for passing on wrong information...

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
"The chain which can be yanked is not the eternal chain."
        -- G. Fitch

> How can I get Last ID inserted ???
> The problem is for a multiuser(symultans) database. Exist a statment SQL
> witch get the last id inserted for a session ?

Ignoring the usefulness you may find using something like nextval, when I
insert something into a database I already know something about how to
locate what makes the inserted row unique.  Either I have something like a
firstname, lastname, address or some combination of values I've just
inserted that are fairly unique when combined together.

So I probably did something like:

Insert Into MyTable
(firstname, lastname, address, city, state)
Values
('$MyFirstName', '$MyLastName', '$MyAddress', '$MyCity', '$MyState');

So after I do that, I just query the table again with something like:

Select max(UniqueID)
From MyTable
Where
    firstname = '$MyFirstName'
        AND
    lastname = '$MyLastName'
        AND
    address = '$MyAddress'

And I just add anything else in the "Where" clause that will give me more
uniqueness.  And I obviously know these things because I just inserted them.
Using "max()" provides the newest UniqueID where those variables are true.

This only works though if you have some arrangement of variables that when
combined with max have a very high likely-hood of producing the unique id
you are looking for.  If your variables are not very unique, and in a
multi-user environment they may or may not be, this won't be very effective
for you.  If they were all numeric fields with a high chance of duplication,
and your users were all inserting them with rapid fire, using max may give
you something someone has inserted since your data insertion...  Game Over.

-Chuck

On Fri, 2 Mar 2001, Catalin CIOCOIU wrote:

>     This method don't encourage using serial data type. Practically, in the
> joined table I can't use "serial" data type for primary key. I nead to
> use sequences by hand.
>     The last solution work fine, but I need a variable for store the
> nextval('<seq name>').
>
> There is not a other solution ?

It turns out I was wrong -- using currval is session-based, as Tom Lane
has pointed out.  However, still see the notes in the docs on caching
more than one values for each backend.  This may or not be pertinent.

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
While money doesn't buy love, it puts you in a great bargaining position.