Re: Fetching generated keys

On Mar 5, 2007, at 15:08 , Mike Clements wrote:

> In Postgres the Connection.prepareStatement() calls that return
> generated keys are not supported. Because of this we need a workaround
> to get the generated keys for inserts into tables that use
> sequences to
> auto-generate their primary keys.
>
> Up to now, we were selecting the current value of the sequence
> immediately after the insert. I thought this was safe because
> transactions should be isolated. But now I realize this could
> potentially fail because the default transaction isolation is "read
> committed". Thus if another connection inserts into the same table,
> causing the sequence to increment, if it commits before we read the
> sequence value, we might read the wrong value (the value as
> incremented
> by the other transaction, not the value as it was for our own insert).
>
> What is the best workaround for this? Ideally the JDBC calls should be
> supported because (1) we would only need a single round trip and (2)
> it's transactionally safe. But without that, what is the recommended
> best practice workaround?
>
> I believe I could set the transaction isolation level to
> "serializable",
> but this seems heavy handed. Is that really the best option?

This is basically a FAQ. But don't be embarrassed- I asked the same
thing six years ago:
http://archives.postgresql.org/pgsql-general/2002-03/msg01257.php

From the docs:
http://www.postgresql.org/docs/current/static/functions-sequence.html
currval() - Return the value most recently obtained by nextval for
this sequence in the current session. (An error is reported if
nextval has never been called for this sequence in this session.)
Notice that because this is returning a session-local value, it gives
a predictable answer whether or not other sessions have executed
nextval since the current session did.

So, currval() will always return the correct last primary key integer
inserted in your current transaction.

If you are using 8.2, you can also try the easier-to-use
INSERT...RETURNING... syntax.

Also, you're better off staying away from lastval(), because its
return value is ambiguous if you use any triggers:
http://people.planetpostgresql.org/xzilla/index.php?/archives/169-Is-
lastval-evil.html

Cheers,
M