Forcing use of cursor result sets

Hi, guys!

I'm making a write-up for my PostgreSQL-using friends, and wonder if
anyone could verify the correctness (or opposite) of the following:

---------------------------------------------------------------------
Memory Problems with the PostgreSQL JDBC Driver

For some reason, the official PostgreSQL JDBC caches the entire result
set in a Vector before returning stuff to the caller.  For most
applications that may work fine, but for applications that work with
gigantic result sets this may exhaust memory.  Recent (2003) versions
of the JDBC driver support "cursor result sets" that only reads a
limited number of rows before returning to the caller.  Those cursor
result sets are not the default, though.  A couple of constraints must
be fulfilled before they kick into action:

  1. The Connection must not be in auto commit mode:

       conn.setAutoCommit(false);

  2. We must tell the driver (through the Statement object) how many
     rows to prefetch, otherwise it will fetch all:

       stmt.setFetchSize(32);

  3. The statement must be a single SELECT statement.  No "batched
     queries".

In case you're interested: The code that checks for the magic is in
the function getQueryFragments() in
org.postgresql.jdbc1.AbstractJdbc1Statement, and it looks like this as
of this writing (2003-11-21):

    if (fetchSize > 0 && !connection.getAutoCommit() && isSingleSelect())
        return transformToCursorFetch();
---------------------------------------------------------------------

Thanks for your help.


Sverre.

--
shh@thathost.com
http://shh.thathost.com/