Re: Queries with large ResultSets

Andrea Aime wrote:

> Ugh... those limitation are really frightening, this means we cannot fetch
> big quantities of data outside of a transaction... this is a problem with
> application servers like GeoServer that keep a connection pool and
> need to fetch big quantities of data also outside a transaction... any hope
> to see this fixed soon? Is it a driver problem or a server limitation?

Cursor are implicitly closed at the end of a transaction unless they are
declared WITH HOLD. Declaring a cursor WITH HOLD has an associated cost
on the backend (namely it will copy the cursor's contents at the end of
the transaction). If autocommit is on, you have an implicit transaction
around every query, so it doesn't make sense to use a non-holdable
cursor with autocommit on -- you'd never be able to fetch any results.

This could be controllable via the JDBC3 resultset holdability methods,
but currently it isn't and all resultsets effectively default to
ResultSet.CLOSE_CURSORS_AT_COMMIT.

I don't think you want a holdable cursor for this case anyway since the
backend would end up doing a lot of unnecessary copying results around.
If you're accessing big quantities of data, the overhead of an explicit
commit() after you're done with the resultset is going to be
insignificant compared to the cost of actually transferring and handling
that data. Use something like this:

connection.setAutoCommit(false);
PreparedStatement stmt = connection.prepareStatement("SELECT ....");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
// process data
}
rs.close();
connection.commit();

-O