Re: TYPE_SCROLL_XXX and fetch size.

On Thu, 29 Jan 2004, [ISO-8859-2] Fischer Kriszti�n wrote:

> Hi Kris!
>
> Kris Jurka wrote:
>  > The problem is that the fetch size is ignored when using a ResultSet of
>  > TYPE_SCROLL_INSENSITIVE.  This only works for TYPE_FORWARD_ONLY.
>
> Do you plan to implement this feature for TYPE_SCROLL_XXX ? Our system
> depends on this feature, and we have to port it to PostreSQL. So we
> either have to rewrite a part of the system, or wait till it's included
> in the driver. Currently, I'm trying to collect some information to be
> able to decide which way we should go...
>

The setFetchSize directive to limit the number of rows returned is
implemented by rewriting the SQL statement to use a cursor in the backend.
To allow scrolling within a cursor you must specify the SCROLL option when
creating it.  The problem with this is that certain query plans don't
allow scrolling by default, so the backend is forced to retrieve all of
the results and store them on the server and implement the scrolling
there.  So while it is possible to implement this the performance
advantage won't necessarily be seen because the server must still
retrieve all rows from the query and store them.  There are certainly
some advantages to doing this, namely the server can spool to a file for
large resultsets so it won't get the dreaded java out of memory error,
but the complexity of implementing this combined with the potentially low
return value has kept this item off of the top of people's todo lists.
With the above restrictions I've mentioned is this still something that
would be useful to you?

Kris Jurka