[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    Re: [BUGS] OutOfMemory hibernate scroll with 2M records |	 Postgresql
From:       Ankit Kumar <ankitk () xebia ! com>
Date:       2009-12-22 16:59:38
Message-ID: 4B30FAE3.1040100 () xebia ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt><big>All<br>
<br>
Thanks alot for your help. Got it working by setting the following: - <br>
<br>
1. Connection to AutoCommit(false);<br>
2. Criteria.scroll(ScrollMode.FORWARD_ONLY);<br>
3. Set hibernate.jdbc.fetch_size=1000<br>
<br>
Regards<br>
Ankit</big><br>
</tt><br>
Kevin Grittner wrote:
<blockquote cite="mid:4B309352020000250002D8BA@gw.wicourts.gov"
 type="cite">
  <pre wrap="">Craig Ringer  wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">Greg Stark wrote:
    </pre>
    <blockquote type="cite">
      <pre wrap="">Ankit Kumar wrote:
      </pre>
      <blockquote type="cite">
        <pre wrap="">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.
        </pre>
      </blockquote>
      <pre wrap="">No Postgresql is not generating these errors -- they're Java
errors and Postgresql is not written in Java.
      </pre>
    </blockquote>
    <pre wrap="">However, PostgreSQL's JDBC driver *is*.
    </pre>
  </blockquote>
  <pre wrap=""><!----> 
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:
 
<a class="moz-txt-link-freetext" \
href="http://jdbc.postgresql.org/documentation/84/query.html#query-with-cursor">http://jdbc.postgresql.org/documentation/84/query.html#query-with-cursor</a>
  
-Kevin

  </pre>
</blockquote>
</body>
</html>



[prev in list] [next in list] [prev in thread] [next in thread]

Configure | About | News | Add a list | Sponsored by KoreLogic