PostgreSQL, WebObjects and fetchSize

От:

Jan Willem Luiten <jwl@secondmove.com>

Дата:

11 мая 2005 г. в 10:47:16

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

Re: PostgreSQL, WebObjects and fetchSize

От:

Jan Willem Luiten <jwl@secondmove.com>

Дата:

11 мая 2005 г. в 11:39:22

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

Re: PostgreSQL, WebObjects and fetchSize

От:

Oliver Jowett <oliver@opencloud.com>

Дата:

11 мая 2005 г. в 10:59:20

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

Re: PostgreSQL, WebObjects and fetchSize

От:

Oliver Jowett <oliver@opencloud.com>

Дата:

11 мая 2005 г. в 11:27:39

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

Re: PostgreSQL, WebObjects and fetchSize

От:

Oliver Jowett <oliver@opencloud.com>

Дата:

11 мая 2005 г. в 18:49:34

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

Re: PostgreSQL, WebObjects and fetchSize

От:

David Teran <david.teran@cluster9.com>

Дата:

12 мая 2005 г. в 06:21:47

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

Re: PostgreSQL, WebObjects and fetchSize

От:

Jan Willem Luiten <jwl@secondmove.com>

Дата:

11 мая 2005 г. в 11:17:48

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

Re: PostgreSQL, WebObjects and fetchSize

От:

Kris Jurka <books@ejurka.com>

Дата:

11 мая 2005 г. в 19:04:16

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