Thread: PostgreSQL, WebObjects and fetchSize
Hello, I just recently installed PostgreSQL. I'm using this in combination with WebObjects 5.2. and am fairly impressed with Postgres. Unfortunately I ran into a problem. I have a table containing a large number of rows ( > 600.000). When using a displaygroup for batched access I get an Out of Memory error. This seems to be caused by the jdbc adaptor reading the complete resultset instead of using a scrollable cursor. Is there a way to solve this, eg by setting the fetchsize in some way? Kind regards, Jan Willem Luiten
Jan Willem Luiten wrote: > This seems to be caused by the jdbc adaptor reading the complete > resultset instead of using a scrollable cursor. Is there a way to solve > this, eg by setting the fetchsize in some way? Yes: Statement.setFetchSize(). Currently it only takes effect with autocommit off and TYPE_FORWARD_ONLY resultsets. -O
Hello Oliver, Thanks for the prompt reply. Unfortunately I've got no access to Statement.setFetchSize() from within WebObjects. Is there any other way to solve this? Kind regards, Jan Willem On May 11, 2005, at 3:59 PM, Oliver Jowett wrote: > Jan Willem Luiten wrote: > > >> This seems to be caused by the jdbc adaptor reading the complete >> resultset instead of using a scrollable cursor. Is there a way to >> solve >> this, eg by setting the fetchsize in some way? >> > > Yes: Statement.setFetchSize(). Currently it only takes effect with > autocommit off and TYPE_FORWARD_ONLY resultsets. > > -O > >
Jan Willem Luiten wrote: > Thanks for the prompt reply. Unfortunately I've got no access to > Statement.setFetchSize() from within WebObjects. Is there any other way > to solve this? This is up to WebObjects to support -- it's a standard JDBC mechanism. The driver doesn't give you another way to set it. -O
Hello again,
When using Openbase in the exact same setup, the out-of-memory error
does not occur and the the results are batched properly. So somehow
the jdbc adaptor for Openbase is able to perform this "trick" in
combination with WebObjects, while the PostgreSQL jdbc adaptor is
not. Yet, WO has no special provisions to work in conjunction with
Openbase.
Besides that, on the TODO list it says under "Performance":
Allow scrollable ResultSets to not fetch all results in one batch.
Could this have to do with the problem I encountered?
kind regards,
Jan Willem.
On May 11, 2005, at 4:27 PM, Oliver Jowett wrote:
> Jan Willem Luiten wrote:
>
>
>> Thanks for the prompt reply. Unfortunately I've got no access to
>> Statement.setFetchSize() from within WebObjects. Is there any
>> other way
>> to solve this?
>>
>
> This is up to WebObjects to support -- it's a standard JDBC mechanism.
> The driver doesn't give you another way to set it.
>
> -O
>
>
Jan Willem Luiten wrote: > Besides that, on the TODO list it says under "Performance": > > Allow scrollable ResultSets to not fetch all results in one batch. > > Could this have to do with the problem I encountered? Possibly. As I originally said: > Yes: Statement.setFetchSize(). Currently it only takes effect with > autocommit off and TYPE_FORWARD_ONLY resultsets. Perhaps WebObjects is using resultsets of a different type, or has autocommit on. I couldn't say without seeing the code in question. -O
On Thu, 12 May 2005, Oliver Jowett wrote: > Jan Willem Luiten wrote: > > > Thanks for the prompt reply. Unfortunately I've got no access to > > Statement.setFetchSize() from within WebObjects. Is there any other way > > to solve this? > > This is up to WebObjects to support -- it's a standard JDBC mechanism. > The driver doesn't give you another way to set it. > Well defaulting it to a nonzero value is a reasonable thing to do, we have just chosen not to for the pg driver because of possible performance problems specific to pg. We could consider exposing it via a URL parameter. Kris Jurka
Hi, >>> Thanks for the prompt reply. Unfortunately I've got no access to >>> Statement.setFetchSize() from within WebObjects. Is there any other >>> way >>> to solve this? >> >> This is up to WebObjects to support -- it's a standard JDBC mechanism. >> The driver doesn't give you another way to set it. >> > > Well defaulting it to a nonzero value is a reasonable thing to do, we > have > just chosen not to for the pg driver because of possible performance > problems specific to pg. We could consider exposing it via a URL > parameter. > That would be fine. What default value do you suggest? Project Wonder for WebObjects has a postgres plugin for WebObjects and i already added some features to the plugin so in the meantime i would add this reasonable default value to the plugin so people using the plugin and postgres and webobjects are happy. All i need to know is a good default value. And WebObjects is not using autocommit=true for Postgres, it respects the transaction capability from postgres and uses normal transactions. regards, David