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

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