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

On Feb 20, 2008 8:50 AM, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> 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 connection pooling yet. I'm working on that for the
release after this one.

Like I said, I was operating under the assumption that
nextval/currval was bound to the transaction, not to the connection.
I guess I was wrong.

Be that as it may, I still don't see what you gain by doing
insert ... (nextval('idseq'),...
select currval('idseq');
when you could do
select nextval('idseq');
insert ...(?,...

--
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.