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.


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

spastor@motionsponsor.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

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

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

> spastor@motionsponsor.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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

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

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


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

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

> On Feb 20, 2008 8:32 AM, Dave Cramer <pg@fastcrypt.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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Feb 20, 2008 8:50 AM, Heikki Linnakangas <heikki@enterprisedb.com> 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 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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings