Thread: Re: OutOfMemory hibernate scroll with 2M records | Postgresql 8.4 DB

Re: OutOfMemory hibernate scroll with 2M records | Postgresql 8.4 DB

From
"Kevin Grittner"
Date:
Craig Ringer  wrote:
> Greg Stark wrote:
>> Ankit Kumar wrote:
>>> Thanks for your response. Hibernate works well when I change the
>>> DB to SQL server but somehow the moment I point to Postgresql it
>>> start generating OutOfMemory. Is there some configuration at DB
>>> end to ensure it starts using the cursors.
>>
>> No Postgresql is not generating these errors -- they're Java
>> errors and Postgresql is not written in Java.
>
> However, PostgreSQL's JDBC driver *is*.

Yeah, and PostgreSQL is unique among the database products I've used
in defaulting to materializing the entire result set on the client
side before returning from the execute call.  Most databases fire off
the query on the server at execute and wait for client requests to
pull data before generating the result rows on the server side (maybe
with a small read-ahead for performance).  It would appear that
hibernate, at least as configured and used here, is not overcoming
this default PostgreSQL behavior.

Take a look at this page and see where it might be going wrong:

http://jdbc.postgresql.org/documentation/84/query.html#query-with-cursor

-Kevin

Re: OutOfMemory hibernate scroll with 2M records | Postgresql 8.4 DB

From
Ankit Kumar
Date:
All

Thanks alot for your help. Got it working by setting the following: -

1. Connection to AutoCommit(false);
2. Criteria.scroll(ScrollMode.FORWARD_ONLY);
3. Set hibernate.jdbc.fetch_size=1000

Regards
Ankit

Kevin Grittner wrote:
> Craig Ringer  wrote:
>
>> Greg Stark wrote:
>>
>>> Ankit Kumar wrote:
>>>
>>>> Thanks for your response. Hibernate works well when I change the
>>>> DB to SQL server but somehow the moment I point to Postgresql it
>>>> start generating OutOfMemory. Is there some configuration at DB
>>>> end to ensure it starts using the cursors.
>>>>
>>> No Postgresql is not generating these errors -- they're Java
>>> errors and Postgresql is not written in Java.
>>>
>> However, PostgreSQL's JDBC driver *is*.
>>
>
> Yeah, and PostgreSQL is unique among the database products I've used
> in defaulting to materializing the entire result set on the client
> side before returning from the execute call.  Most databases fire off
> the query on the server at execute and wait for client requests to
> pull data before generating the result rows on the server side (maybe
> with a small read-ahead for performance).  It would appear that
> hibernate, at least as configured and used here, is not overcoming
> this default PostgreSQL behavior.
>
> Take a look at this page and see where it might be going wrong:
>
> http://jdbc.postgresql.org/documentation/84/query.html#query-with-cursor
>
> -Kevin
>
>