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

Hello everyone,

This is my very first POST in this list. I am just starting to work with postgresql and the jdbc driver and so far so good !
Only one exception is regarding the best method to retrieve the last inserted id (serial). There are many posts on this topic but i couldn t find a definitive response.
I am using postgresql version 8.2.6 and jdbc 8.2_p505, i ve been experiencing INSERT statement with RETURNING myID, using an executeQuery it works great, i retrieve the proper value everytime at least from the few tests i have been doin locally.
Is it THAT easy or could this method break somehow in a live environement ? If yes could you advise on another method ?

Thanks in advance

Sebastien.

spastor(at)motionsponsor(dot)com wrote:
> Only one exception is regarding the best method to retrieve the last inserted id (serial). There are many posts on this topic but i couldn t find a definitive response.
> I am using postgresql version 8.2.6 and jdbc 8.2_p505, i ve been experiencing INSERT statement with RETURNING myID, using an executeQuery it works great, i retrieve the proper value everytime at least from the few tests i have been doin locally.
> Is it THAT easy or could this method break somehow in a live environement ?

It is that easy, and that is a good way to do it.

Alternatively, if you need to support older PostgreSQL version for
example, you could use a "SELECT currval('sequence')" query after the
INSERT.

Much of the of discussion you saw were probably about implementing the
JDBC getGeneratedKeys() function that some applications need for
portability reasons, or because of middleware that doesn't know how to
do INSERT RETURNING. PostgreSQL driver doesn't support that at the
moment, but as long as you don't need it, you'll be fine.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

"Heikki Linnakangas" <heikki 'at' enterprisedb.com> writes:

> spastor(at)motionsponsor(dot)com wrote:
>> Only one exception is regarding the best method to retrieve the last
>> inserted id (serial). There are many posts on this topic but i
>> couldn t find a definitive response. I am using postgresql version
>> 8.2.6 and jdbc 8.2_p505, i ve been experiencing INSERT statement
>> with RETURNING myID, using an executeQuery it works great, i
>> retrieve the proper value everytime at least from the few tests i
>> have been doin locally.
>> Is it THAT easy or could this method break somehow in a live environement ?
>
> It is that easy, and that is a good way to do it.
>
> Alternatively, if you need to support older PostgreSQL version for
> example, you could use a "SELECT currval('sequence')" query after the
> INSERT.

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

--
Guillaume Cottenceau

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

Dave

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

Dave

Dave Cramer <davec 'at' fastcrypt.com> writes:

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

Doh! Sorry for the noise. I should have double checked,
especially because Heikki is really not the kind of guy to talk
bullshit :/

--
Guillaume Cottenceau

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.

--
Paul Tomblin <ptomblin(at)xcski(dot)com> http://blog.xcski.com/
"Only a NAZI would try to invoke Godwin's [law] deliberately"
- Jeff Gostin in a.s.r

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.

No, autocommit has nothing to do with it.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

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.

Dave

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?

One problematic scenario for nextval+currval is an INSERT trigger that
calls nextval() behind your back, but you can fool any method with a
trigger if you really want to.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki

On 20-Feb-08, at 8:14 AM, Heikki Linnakangas 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?
>
> One problematic scenario for nextval+currval is an INSERT trigger
> that calls nextval() behind your back, but you can fool any method
> with a trigger if you really want to.
>
As far as I can tall Paul has inherited an application which uses a
single connection for all database operations, and is a swing app
which has callbacks which do the following

Callback code

grab the global connection object
create a statement
do something
close statement

in this scenario, since currval has connection scope if two callbacks
are called at the same time, only one will have the right answer .

Paul am I correct in my assumptions above ?

Dave
>

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.

On Feb 20, 2008 8:32 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
> As far as I can tall Paul has inherited an application which uses a
> single connection for all database operations, and is a swing app
> which has callbacks which do the following
>
> Callback code
>
> grab the global connection object
> create a statement
> do something
> close statement
>
> in this scenario, since currval has connection scope if two callbacks
> are called at the same time, only one will have the right answer .
>
> Paul am I correct in my assumptions above ?

Pretty much, except with the added complication that there are a dozen
or so daemons that are also updating the same tables, and up until now
they've all had autocommit on. I thought the currval had transaction
scope not connection scope, at least that's what my testing in pgsql
seemed to indicate, which is why I stated that autocommit was a
problem.

Are you saying that if in one connection I do a nextval and commit,
and somebody else in a different connection does a hundred nextvals
and commits, then 20 minutes later I do the currval I'll get the one
from my old transaction?

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

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

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

On 20-Feb-08, at 8:41 AM, Paul Tomblin wrote:

> On Feb 20, 2008 8:32 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>> As far as I can tall Paul has inherited an application which uses a
>> single connection for all database operations, and is a swing app
>> which has callbacks which do the following
>>
>> Callback code
>>
>> grab the global connection object
>> create a statement
>> do something
>> close statement
>>
>> in this scenario, since currval has connection scope if two callbacks
>> are called at the same time, only one will have the right answer .
>>
>> Paul am I correct in my assumptions above ?
>
> Pretty much, except with the added complication that there are a dozen
> or so daemons that are also updating the same tables, and up until now
> they've all had autocommit on. I thought the currval had transaction
> scope not connection scope, at least that's what my testing in pgsql
> seemed to indicate, which is why I stated that autocommit was a
> problem.
>
> Are you saying that if in one connection I do a nextval and commit,
> and somebody else in a different connection does a hundred nextvals
> and commits, then 20 minutes later I do the currval I'll get the one
> from my old transaction?
Yes it has connection scope, not transaction scope. currval stores the
current value in connections memory.
>
>
>
>
> --
> 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.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

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.

Paul Tomblin wrote:
> 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 ...(?,...

Oh, sure, there's not gain either way. Select nextval + insert works
just as well.

Theoretically, you could "pipeline" the "INSERT nextval" + "SELECT
currval" method, by sending both commands at once and only then block
and wait for response, saving one round-trip to the server. You could do
that with a batch statement, I think, or by single executeQuery("INSERT
... ; SELECT currval(...)"). But if you don't do that, there's no
difference.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com