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

[Dave Cramer <pg(at)fastcrypt(dot)com>]

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

> 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.
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(at)postgresql(dot)org so that your
>      message can get through to the mailing list cleanly