SEO

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@xcski.com> http://blog.xcski.com/
"Only a NAZI would try to invoke Godwin's [law] deliberately"
- Jeff Gostin in a.s.r

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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
>

---------------------------(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:14 AM, Heikki Linnakangas <heikki@enterprisedb.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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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?



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

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

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

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