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

Re: Retrieving last InsertedID : INSERT... RETURNING safe ?

From: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
To: "Paul Tomblin" <ptomblin(at)gmail(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Retrieving last InsertedID : INSERT... RETURNING safe ?
Date: 2008-02-20 13:50:06
Message-ID: 47BC300E.1030507@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Paul Tomblin wrote:
> On Feb 20, 2008 8:14 AM, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
>> Dave Cramer wrote:
>>> I was going to say there are absolutely no situations where this is not
>>> true, however in your case autocommit or not it doesn't matter.
>>> You have a single connection for the entire application and asynchronous
>>> events using that connection. Autocommit or not it will not work with
>>> currval.
>>>
>>> In your case you must use nextval before doing the insert.
>> Now you lost me. By asynchronous events, do you mean NOTIFY/LISTEN? What
>> exactly is the scenario you're talking about?
> 
> In my case, we're talking about a system that has dozens of Java
> processes, many of which access the database.  Because the system used
> to have autocommit on, one process could do the "insert nextval" and
> commit, and then another process could do an "insert nextval" and
> commit, and then the first process would do the "select currval" and
> would probably get the wrong value.

 From Dave's comment, I gather that those processes return the 
connection to the pool and grab a new one between the "insert nextval" 
and "select currval" steps? Yeah, I can see the problem in that case.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2008-02-20 13:59:55
Subject: Re: Retrieving last InsertedID : INSERT... RETURNING safe ?
Previous:From: Paul TomblinDate: 2008-02-20 13:41:56
Subject: Re: Retrieving last InsertedID : INSERT... RETURNING safe ?

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