Re: Out of memory error on huge resultset

From: Barry Lind <barry(at)xythos(dot)com>
To: nickf(at)ontko(dot)com
Cc: pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Out of memory error on huge resultset
Date: 2002-10-10 16:40:19
Message-ID: 3DA5AD73.7090502@xythos.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-general pgsql-hackers pgsql-jdbc

Nick,

This has been discussed before on this list many times. But the short
answer is that that is how the postgres server handles queries. If you
issue a query the server will return the entire result. (try the same
query in psql and you will have the same problem). To work around this
you can use explicit cursors (see the DECLARE CURSOR, FETCH, and MOVE
sql commands for postgres).

thanks,
--Barry

Nick Fankhauser wrote:
> I'm selecting a huge ResultSet from our database- about one million rows,
> with one of the fields being varchar(500). I get an out of memory error from
> java.
>
> If the whole ResultSet gets stashed in memory, this isn't really surprising,
> but I'm wondering why this happens (if it does), rather than a subset around
> the current record being cached and other rows being retrieved as needed.
>
> If it turns out that there are good reasons for it to all be in memory, then
> my question is whether there is a better approach that people typically use
> in this situation. For now, I'm simply breaking up the select into smaller
> chunks, but that approach won't be satisfactory in the long run.
>
> Thanks
>
> -Nick
>
> --------------------------------------------------------------------------
> Nick Fankhauser nickf(at)ontko(dot)com Phone 1.765.935.4283 Fax 1.765.962.9788
> Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Neil Conway 2002-10-10 17:28:47 Re: inline newNode()
Previous Message Dave Cramer 2002-10-10 15:35:48 Re: Out of memory error on huge resultset

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kevin Schmidt 2002-10-10 16:47:31 JDBC MetaData Problem
Previous Message Alexaki Sofia 2002-10-10 16:16:07 Time type error

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Welche 2002-10-10 17:48:39 Re: trivial sql help
Previous Message Stephan Szabo 2002-10-10 16:14:41 Re: trivial sql help