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

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