Обсуждение: 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