Re: Last Id?

Hello all,

I know how do do this in IBM DB2 and MySQL, but now my interrest has
shifted towards postgresql; I need to be able to insert a row, and find
out which id it was assigned. (I'm talking about using sequences here).

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.

In IBM DB2 and MySQL there's a connection specific "last id" value
which can be queried. I assume there's something similar in postgresql
-- but what is it?

--
Kind Regards,
Jan Danielsson
Te audire no possum. Musa sapientum fixa est in aure.

Jan Danielsson <jan(dot)danielsson(at)gmail(dot)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

On Sep 5, 2005, at 3:01 AM, Jan Danielsson wrote:

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

Welcome to PostgreSQL!

This is a very common misconception. Your fears are unfounded.
Read the FAQ or the documentation for details.

http://www.postgresql.org/docs/faqs.FAQ.html#4.11.1
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html

Hope this helps.

Michael Glaesemann
grzm myrealbox com

On Mon, Sep 05, 2005 at 11:51:15PM -0400, Tom Lane wrote:
> Jan Danielsson <jan(dot)danielsson(at)gmail(dot)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.

...which is mentioned in both the PostgreSQL documentation and the FAQ:

http://www.postgresql.org/docs/8.0/static/functions-sequence.html
http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3

It's trivial to test the behavior yourself by opening multiple
sessions to the database and seeing if currval() in one session is
affected by inserts in the other sessions (it isn't).

--
Michael Fuhr

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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jan Danielsson" <jan(dot)danielsson(at)gmail(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Sent: Tuesday, September 06, 2005 5:51 AM
Subject: Re: [NOVICE] Last Id?

> Jan Danielsson <jan(dot)danielsson(at)gmail(dot)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
>

<me(at)alternize(dot)com> writes:
> 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.

If your connection pooler will flip the connection out from under you
within a transaction, you need another connection pooler. If you are
doing the insert and then reading currval in separate transactions,
I'd suggest changing the logic to make it one transaction. Whatever
you are going to do with the currval probably ought to be part of the
original inserting transaction anyway.

The nextval-then-insert approach is perfectly fine too of course,
but there's no reason you shouldn't be able to use currval.

regards, tom lane