Thread: Retrieving PK of inserted row

I want to retrieve the primary key, which is a SERIAL, of a row I just
inserted.

In 8.2, I could use RETURNING, but I'm using 8.1.

Reading around, I've seen the following methods discussed:
(1) Within a transation, do the INSERT, and then do a SELECT CURVAL
(2) Not necessarily within a transaction, get a candidate for the pk using
SELECT NEXTVAL, then INSERT the row.
(3) Use LASTVAL

My questions:
* Are any of these methods flawed?
* Is there any reason to prefer (1) to (2)?
* I'm not sure whether LASTVAL is guaranteed to work outside of a
transaction.  Its description in the 8.1 manual starts with "Return the
value most recently returned by nextval in the current session. ..." but I'm
not sure of the formal sense in which "session" is being used.  (I'm
connecting to the DB from mod_php in apache, so I guess "session" could mean
"during the existence of a given db connection," but I'm not sure.)

TIA

Le mardi 30 janvier 2007 12:19, woger151 a écrit :
> (3) Use LASTVAL
for this one : look at
http://people.planetpostgresql.org/xzilla/index.php?/archives/169-Is-lastval-evil.html
>
> My questions:
> * Are any of these methods flawed?
> * Is there any reason to prefer (1) to (2)?
> * I'm not sure whether LASTVAL is guaranteed to work outside of a
> transaction.  Its description in the 8.1 manual starts with "Return the
> value most recently returned by nextval in the current session. ..." but
> I'm not sure of the formal sense in which "session" is being used.  (I'm
> connecting to the DB from mod_php in apache, so I guess "session" could
> mean "during the existence of a given db connection," but I'm not sure.)

"woger151" <woger151@jqpx37.cotse.net> writes:
> Reading around, I've seen the following methods discussed:
> (1) Within a transation, do the INSERT, and then do a SELECT CURVAL
> (2) Not necessarily within a transaction, get a candidate for the pk using
> SELECT NEXTVAL, then INSERT the row.
> (3) Use LASTVAL

You don't need a transaction for any of these; at least not unless
you're using connection-pooling code that might swap you to a different
physical session at transaction boundaries.

Since all of them are going to cost you two round trips to the server,
it's probably about a wash performance-wise.  I'd tend to go with (2)
just because it requires no assumptions about anything.  Even without
the session-change risk, (1) and especially (3) can bite you in a
sufficiently complex application: some other part of your own code
could touch this or another sequence between the two steps.

            regards, tom lane