Re: Fetching generated keys

I understand that currval is the way to select the value that was used
for the inserted row. I've been doing this for quite some time now - it
works.

BUT... That works only in a perfect world with perfectly isolated
transactions. The real world doesn't meet this theoretical perfection.
The default transaction isolation level of Postgres is "read committed",
which means somebody else's insert (and sequence increment), once
committed, could be read by my transaction.

The FAQ you posted suggests that "currval" uses a level of isolation
that is more strict than the default "read committed". If so, setting
isolation level to serializable would be unnecessary. Is that true? Or
should I do it just to be safe? I'd hate to do it if unnecessary due to
the performance and locking implications.

Also, is there any way the JDBC driver will (someday?) support the
Connection.preparedStatement() commands that would eliminate this entire
issue *and* do it all in a single call, obviating the need to send a
separate SQL command just to get the generated key?

Thanks for the tip on INSERT... RETURNING but at first glance it seems
to be unique to Postgres, not supported by other databases we talk to
(DB2, Oracle, etc.). I could be wrong...

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of A.M.
Sent: Monday, March 05, 2007 4:58 PM
To: PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org