Re: mysql's last_insert_id

[Dennis Björklund <db(at)zigo(dot)dhs(dot)org>]

On 29 Aug 2003, Bo Lorentsen wrote:

> > There are various solutions. Some people use functions to do inserts,
> > I just use the string "currval(whatever)" in the application layer
> > which the database replaces with the appropriate value.
>
> Hmm, but what happends if more than one connection does this at the same
> time ? Then, only one of the concurrent connections will have a insert
> on the returned value, and the rest will get a wrong row reference
> returned.

That is not a problem, it's perfectly safe. It's all described in

http://www.postgresql.org/docs/7.3/static/functions-sequence.html

Basicly, currval() gives the last id for that sequence in that session. So 
other sessions does not break anything.
 
> The only safe methode would be to do a "select nextval(whatever)", and
> aply this number by "hand" to the insert, but that remove the
> possibility to make general code even more, but it will be safe.

It's not needed. The following works fine (if the tables exists of course)
and has no problems with concurrency:

INSERT INTO foo(id,x) VALUES (DEFAULT, 'value');
INSERT INTO bar(id,foo_ref) VALUES (DEFAULT, currval('foo_id_seq'));

-- 
/Dennis