Jonathan Gold wrote:
> i know there has been large discussion in the last year or two about
> the fact that the code
>
> ResultSet rs = someStatement.executeQuery( queryString )
>
> causes an OutOfMemoryException for large result sets, as the backend
> returns the entire result set at once, and so it must be stored in
> memory while the result set exists. the current accepted solution
> seems to be the use of a result set in combination with two prepared
> statements -- one to create a cursor, the other to fetch from it.
> there was some comment in these threads that it would be nice if that
> could be fixed, and to have this functionality added to the jdbc
> implementation for postgres.
>
> what is the state of this? do people still want to do it? if so, is
> anyone working on it yet?
The current driver will automatically use cursors behind the scenes if
all of these conditions are true:
1) the statement's fetchsize (via setFetchSize()) is greater than 0
2) the query is a single SELECT
3) autocommit is off, and
4) the statement produces non-scrollable resultsets (actually I think
currently it'll use cursors for scrollable resultsets, but the resultset
doesn't actually work correctly).
As part of the V3 protocol work I'm starting on now, it might be
possible to relax some of these restrictions. specifically:
1) could be made the default more easily
2) can be relaxed to any query that produces results (probably
multistatement queries, too)
3) can be "fixed" by implementing support for JDBC3's holdable
resultsets (if the server supports holdable cursors).
4) is a bug; I have a patch pending (maybe applied now?) that works
around it just by disabling cursors for scrollable resultsets. We need a
proper fix here.
-O