Re: TYPE_SCROLL_XXX and fetch size.

On Thu, 29 Jan 2004, [ISO-8859-2] Fischer Krisztin wrote:

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

This is about the only documentation there is, on the same page under the
description of the SCROLL / NO SCROLL option is also mentioned "Depending
upon the complexity of the query's execution plan, specifying SCROLL may
impose a performance penalty on the query's execution time." This is
a pretty vague description, to see what's really going on we'll have to
consult the archives.

Here is the original complaint about backwards scrolling not working,
towards the end of the thread Tom Lane mentions which types of query plans
can support a backwards fetch with no additional overhead.

http://archives.postgresql.org/pgsql-hackers/2002-12/msg00454.php

Here is a message describing the method of storing the query results on
the server to all the scroll.

http://archives.postgresql.org/pgsql-hackers/2003-03/msg00318.php

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

The driver solution shouldn't be a large amount of code, it is just tricky
to make sure you get all the transitions correct when moving out of the
subset of results the driver has currently cached.

Kris Jurka