Re: TYPE_SCROLL_XXX and fetch size.

Kris Jurka wrote:
> 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.

Could you explain what kind of queries are these? I've read the
PostgreSQL documentation, and I've found the following:

" The SCROLL option should be specified when defining a cursor that will
be used to fetch backwards. This is required by the SQL standard.
However, for compatibility with earlier versions, PostgreSQL will allow
backward fetches without SCROLL, if the cursor's query plan is simple
enough that no extra overhead is needed to support it. However,
application developers are advised not to rely on using backward fetches
from a cursor that has not been created with SCROLL. If NO SCROLL is
specified, then backward fetches are disallowed in any case. "
(from http://www.postgresql.org/docs/current/static/sql-declare.html)

But it's not the problem you mentioned, right?! Could you give me some
related links?

> 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?

Depends on the duration of executeQuery() on a relatively large table.
If it's within an acceptable range (from a client application's point of
view), it would be useful. I think an ineffective implementation is
better than an OutOfMemoryError (which is in deed much better than an
UnsupportedOperationException). :) How much effort does it take to
implement it? I could offer some programming capacity to help you (if
there's a need for it, of course). I think it's better for both of us if
we enhance the driver. We make no work-arounds in our system, and
everybody will profit from a public available driver.

Thanks,

Chris

--
Fischer Krisztián <fischer(at)borganization(dot)com>
Tel: (+36)70/3843835, (+36)1/3360547
Borganization Kft.