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

Ankit Kumar wrote:
> Hi
>
> I am running a Criteria.scroll() on postgresql on a DB containing 2M
> records. The memory keeps increasing and finally it generates an
> OutOfMemoryException. Please can you advice how to fix this.
>
> *Postgresql DB version:* 8.4
> *Postgresql Driver Used:* postgresql-8.4-701.jdbc4.jar
>
> Some forums do mention that "/scroll/" is slow on Postgresql.
>
> Is this a known issue or we need to do something specific to make
> scroll work on Postgresql?
>
> It appears the driver class is loading all entity objects in memory
> and not releasing them as expected in a scroll operation.
>
>
> Appreciate any guidance/suggestion.
>
> *Exception StackTrace*:
> java.lang.OutOfMemoryError: Java heap space
>
>

This link might be useful for you to look at:

https://forums.hibernate.org/viewtopic.php?p=2398604&sid=f277fe884089594fe39272f69b68e329

It would appear that you need to encourage Hibernate to use a cursor,
instead of buffering the entire resultset in memory - I haven't looked
too much at the hibernate docs (never used it before...) so I have no
idea how to do this myself, but if I have time I'll dig a bit.

Do you have a self contained example to show us how you are setting up
the scrollable resultset?

regards

Mark