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

On Feb 20, 2008 8:14 AM, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
>
> Dave Cramer wrote:
> >
> > On 20-Feb-08, at 7:19 AM, Paul Tomblin wrote:
> >
> >> Dave Cramer wrote:
> >>>> Well, that other solution is dangerous in case multiple inserts
> >>>> to that table are done concurrently; a quite common usage pattern
> >>>> with java web applications handling multiple HTTP requests with
> >>>> concurrent java threads..
> >>>>
> >>> No it is not dangerous. It is the right way to do it. There is
> >>> absolutely no danger in using currval in this manner.
> >>
> >> Unless you have autocommit on.
> >>
> > 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. That's one reason why I find it
simpler to do a "select nextval" and then "insert ?" with the value
returned instead of messing around with currval.

--
For my assured failures and derelictions I ask pardon beforehand of my
betters and my equals in my Calling here assembled, praying that in
the hour of my temptations, weakness and weariness, the memory of this
my Obligation and of the company before whom it was entered into, may
return to me to aid, comfort and restrain.