Thread: setFetchSize() not working

I'm using the 7.3.3 backend, and the JDBC 8.0dev jdbc driver
(pgdev.306.jdbc3.jar).

I'm calling PreparedStatement.setFetchSize(32), on a table fetching 32KB
BYTEA records, and I'm blowing out memory on the executeQuery() call
of the prepared statement.

PreparedStatement.getFetchDirection reports 1000, which is
ResultSet.FETCH_FORWARD.

Any clues?


Here's the trace catching the driver red-handed with all the memory:

SITES BEGIN (ordered by live bytes) Sun Oct 10 09:55:56 2004
           percent         live       alloc'ed  stack class
  rank   self  accum    bytes objs   bytes objs trace name
     1 80.92% 80.92% 55023456  578 80423168 1051 62286 [B

TRACE 62286:
org.postgresql.core.PGStream.Receive(PGStream.java:410)
org.postgresql.core.PGStream.ReceiveTupleV2(PGStream.java:395)
org.postgresql.core.v2.QueryExecutorImpl.processResults(QueryExecutorImpl.java:337)
org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:251)
org.postgresql.core.v2.QueryExecutorImpl.execute(QueryExecutorImpl.java:159)
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:346)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:294)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:209)
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)

Jeffrey Tenny wrote:
> I'm using the 7.3.3 backend, and the JDBC 8.0dev jdbc driver
> (pgdev.306.jdbc3.jar).
>
> I'm calling PreparedStatement.setFetchSize(32), on a table fetching 32KB
> BYTEA records, and I'm blowing out memory on the executeQuery() call
> of the prepared statement.
>
> PreparedStatement.getFetchDirection reports 1000, which is
> ResultSet.FETCH_FORWARD.
>
> Any clues?

Use a newer backend or an older driver. The development driver only
supports cursors when using a V3 protocol connection (supported from 7.4
onwards). You probably want a newer backend anyway even if only the
newest 7.3.x, there are data-loss bugs in earlier 7.3 versions.

There's no real reason that V2 can't use cursors, it's just that the
support has not been reimplemented for the changed protocol layer framework.

In general all the new driver work is happening against 7.4/8.0
backends. We do test for correct behaviour against 7.3 servers, but
there are no guarantees that the driver is going to perform as well.

-O

On Sun, 10 Oct 2004, Jeffrey Tenny wrote:

> I'm using the 7.3.3 backend, and the JDBC 8.0dev jdbc driver
> (pgdev.306.jdbc3.jar).
>
> I'm calling PreparedStatement.setFetchSize(32), on a table fetching 32KB
> BYTEA records, and I'm blowing out memory on the executeQuery() call
> of the prepared statement.
>
> PreparedStatement.getFetchDirection reports 1000, which is
> ResultSet.FETCH_FORWARD.
>
> Any clues?
>

The development driver has had cursor based fetching support removed when
connecting to servers older than 7.4.  The 7.4 release had a new
frontend/backend protocol added which allows for an alternate method of
streaming ResultSets.  The 7.4 driver used the old method, but
the development driver was updated to use the new method and in doing that
support for streaming using the old protocol was removed.  Your options
seem to be:

 - update your server to 7.4 or later
 - use the 7.4 driver against your 7.3 server
 - figure out had to re-add support for streaming resultsets from old
   servers to the new driver.


Kris Jurka