Re: Last Id?

unfortunately as i had to find out some days ago, "session-local" is a bit
dependend on how (and when) you actually open up new db connections to the
db, especially when you maybe have connection pooling enabled. i am now
using the other concept as described in [1]:

-------------------
One approach is to retrieve the next SERIAL value from the sequence object
with the nextval() function before inserting and then insert it explicitly.
Using the example table in 4.11.1, an example in a pseudo-language would
look like this:

    new_id = execute("SELECT nextval('person_id_seq')");
    execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise
Pascal')");

You would then also have the new value stored in new_id for use in other
queries (e.g., as a foreign key to the person table).
-------------------

this works fine.

cheers,
thomas

[1] http://www.postgresql.org/docs/faqs.FAQ.html#4.11.1

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Jan Danielsson" <jan.danielsson@gmail.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, September 06, 2005 5:51 AM
Subject: Re: [NOVICE] Last Id?


> Jan Danielsson <jan.danielsson@gmail.com> writes:
>>    I have seen a few "currval", "last_value", tricks and such, but they
>> aren't good enough, AFAICT, since they only work when I can guarantee
>> that no one else will insert a new record between the time that I insert
>> one, and query for the last_value.
>
> You evidently do not understand how currval works.  It is session-local,
> so the race condition you are imagining does not exist.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>