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

On 20-Feb-08, at 8:50 AM, Heikki Linnakangas wrote:

> Paul Tomblin wrote:
>> On Feb 20, 2008 8:14 AM, Heikki Linnakangas
>> <heikki@enterprisedb.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.
>
No, there is no pool, only one connection.

> --
>  Heikki Linnakangas
>  EnterpriseDB   http://www.enterprisedb.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly