Thread: AbstractJdbc2Statement.setFetchSize()

All,
I had hoped to use this method, which is implemented in PG 7.4, to limit
the number of rows retrieved (for display purposes) out of some massive
datasets we have. I know the JDBC spec says that this is just a hint,
but calling the method on a Statement doesn't seem to have any effect on
the SELECT. It still seesm to try to load the whole dataset.

Is there any way to avoid cursored FETCH approaches to this problem?
                    Thanks,
                    Rich Cullingford
                    rculling@sysd.com

On Thu, Oct 16, 2003 at 06:09:47PM -0400, Rich Cullingford wrote:
> All,
> I had hoped to use this method, which is implemented in PG 7.4, to limit
> the number of rows retrieved (for display purposes) out of some massive
> datasets we have. I know the JDBC spec says that this is just a hint,
> but calling the method on a Statement doesn't seem to have any effect on
> the SELECT. It still seesm to try to load the whole dataset.
>
> Is there any way to avoid cursored FETCH approaches to this problem?

Recent drivers should transparently transform queries into a cursor-based
form if you set a non-zero fetchsize. However there are a few gotchas:

 - it doesn't do this if (JDBC-level) autocommit is on
 - it doesn't do this if you use a PreparedStatement with parameters
 - it tries to do this even if the statement is something other than a
    SELECT statement, and gets horribly confused in the process.

I have patches for the second two if needed; they probably won't reach CVS
until after 7.4 is out, though, since they're a bit invasive.

-O

Oliver Jowett wrote:
> On Thu, Oct 16, 2003 at 06:09:47PM -0400, Rich Cullingford wrote:
>
>>All,
>>I had hoped to use this method, which is implemented in PG 7.4, to limit
>>the number of rows retrieved (for display purposes) out of some massive
>>datasets we have. I know the JDBC spec says that this is just a hint,
>>but calling the method on a Statement doesn't seem to have any effect on
>>the SELECT. It still seesm to try to load the whole dataset.
>>
>>Is there any way to avoid cursored FETCH approaches to this problem?
>
>
> Recent drivers should transparently transform queries into a cursor-based
> form if you set a non-zero fetchsize. However there are a few gotchas:
>
>  - it doesn't do this if (JDBC-level) autocommit is on
>  - it doesn't do this if you use a PreparedStatement with parameters
>  - it tries to do this even if the statement is something other than a
>     SELECT statement, and gets horribly confused in the process.
>
> I have patches for the second two if needed; they probably won't reach CVS
> until after 7.4 is out, though, since they're a bit invasive.

Oliver,
Thanks for your help. I checked that the Connection I'm using had
autoCommit=false, and that the Statement (not PreparedStatement with
parameters) had a small fetchsize (200 rows) just before sending an
executeQuery() for a SELECT against a 7 million-record dataset. It just
sat there. The query works against smaller datasets.

I tried attaching a fetchsize to the ResultSet, but that lead to an
exception (as expected, since one is apparently expected to provide a
custom cursored-fetch in this case).

I wonder what I'm doing wrong...
                                   Rich C.