Re: Cursors and Transactions, why?

On Wed, 7 Apr 2004, Eric Ridge wrote:

>
> My real problem is that the JDBC drivers (and I assume this is true for
> all client interfaces) buffer the results of a SELECT in memory,
> because the backend pushes out all the tuples as the response. I'm not
> dealing with a large number of rows (only a few thousand), but they've
> very wide, and many contain fields with multi-megabyte data. In some
> situations, when I've got a lot of open ResultSets, the JVM throws
> OutOfMemory errors.
>

The 7.4 jdbc driver has the ability to use cursors behind the scenes on
queries. This is done by calling Statement.setFetchSize(n) to retrieve n
rows at a time. There are a number of other restrictions: you must be in
a transaction and the ResultSet type must be FORWARD_ONLY. You can use
the 7.4 jdbc driver against a 7.3 server as well so this may provide some
relief.

Kris Jurka