Re: OutOfMemory

Earlier versions of the PostgreSQL JDBC driver do not stream data from
the back end: they fetch everything in one go.

The PostgreSQL 7.4 JDBC driver supports JDBC's setFetchSize()
operation, and will use PostgreSQL cursors internally. If you set the
fetch size to something >0, it will correctly [*] and transparently
stream data on demand. The driver works perfectly with earlier
versions of PostgreSQL.

With earlier versions of the driver, you can emulate the behaviour by
first doing this:

stmt.executeUpdate("declare foo cursor for select * from bar");

and then for each batch, as an executeQuery():

rs = stmt.executeQuery("fetch forward 200 from foo");

and when you're done with the cursor,

stmt.executeUpdate("close foo");

[*] Beware of transactions with many queries. The JDBC driver never
explicitly closes its cursors, and instead relies on the back end to
close them when the transaction is committed or aborted. In my
testing, the back end consistently runs out of memory in such cases.
We are, however, talking about thousands of queries. For all I know
this may have been fixed after the 7.4.1 release.

Alexander.

on 2004-03-29 17:42 postgres(at)nitwit(dot)de wrote:

> Hi!
>
> I'm not sure whether this is a Java or an DB issue. I select a lot of data
> from database, more than would fit into memory - and get an
> OutOfMemoryException.
>
> Well, why is this? This is actually what the idea of an Iterator is about,
> that the data is progressively fetch and not all at once put into memory,
> isn't it?
>
> Now I do have to manually run the query multiple times using LIMIT/OFFSET
> (manually adapted to the amount of RAM of the host machine...).
>
> Timo
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>