Skip site navigation (1) Skip section navigation (2)

Re: Fetching generated keys

From: "A(dot)M(dot)" <agentm(at)themactionfaction(dot)com>
To: PostgreSQL JDBC List <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Fetching generated keys
Date: 2007-03-05 21:58:07
Message-ID: 25DFEDBF-C104-44AB-AE75-4FEE1D99B49B@themactionfaction.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2007-03-05 22:10:10
Subject: Re: Fetching generated keys
Previous:From: Dave CramerDate: 2007-03-05 21:50:47
Subject: Re: Fetching generated keys

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group