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

["Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>]

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