Re: Out of memory error on huge resultset - Mailing list pgsql-jdbc

From snpe
Subject Re: Out of memory error on huge resultset
Date
Msg-id 200210111427.41955.snpe@snpe.co.yu
Whole thread Raw
In response to Re: Out of memory error on huge resultset  (Barry Lind <barry@xythos.com>)
Responses Re: Out of memory error on huge resultset
List pgsql-jdbc
Barry,
  Is it true ?
I create table with one column varchar(500) and enter 1 milion rows with
length 10-20 character.JDBC query 'select * from a' get error 'out of
memory', but psql not.
I insert 8 milion rows and psql work fine yet (slow, but work)

In C library is 'execute query' without fetch - in jdbc execute fetch all rows
and this is problem - I think that executequery must prepare query and fetch
(ResultSet.next or ...) must fetch only fetchSize rows.
I am not sure, but I think that is problem with jdbc, not postgresql
Hackers ?
Does psql fetch all rows and if not how many  ?
Can I change fetch size in psql ?
CURSOR , FETCH and MOVE isn't solution.
If I use jdbc in third-party IDE, I can't force this solution

regards

On Thursday 10 October 2002 06:40 pm, Barry Lind wrote:
> 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@ontko.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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: NullPointer error returned from ResultSet.java
Next
From: Jeffrey Duffy
Date:
Subject: Re: NullPointer error returned from ResultSet.java