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

At 08:27 AM 10/11/2002, snpe wrote:
>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)

The way the code works in JDBC is, in my opinion, a little poor but
possibly mandated by JDBC design specs.

It reads the entire result set from the database backend and caches it in a
horrible Vector (which should really be a List and which should at least
make an attempt to get the # of rows ahead of time to avoid all the
resizing problems).

Then, it doles it out from memory as you go through the ResultSet with the
next() method.

I would have hoped (but was wrong) that it streamed - WITHOUT LOADING THE
WHOLE THING - through the result set as each row is returned from the
backend, thus ensuring that you never use much more memory than one line.
EVEN IF you have to keep the connection locked.

The latter is what I expected it to do. The former is what it does. So, it
necessitates you creating EVERY SELECT query which you think has more than
a few rows (or which you think COULD have more than a few rows, "few" being
defined by our VM memory limits) into a cursor based query. Really klugy. I
intend to write a class to do that for every SELECT query for me automatically.

Cheers,

Doug


>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
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)