Thread: Re: TYPE_SCROLL_XXX and fetch size.

Re: TYPE_SCROLL_XXX and fetch size.

From
Kris Jurka
Date:

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



Re: TYPE_SCROLL_XXX and fetch size.

From
Fischer Krisztián
Date:
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@borganization.com>
Tel: (+36)70/3843835, (+36)1/3360547
Borganization Kft.


Re: TYPE_SCROLL_XXX and fetch size.

From
Kris Jurka
Date:

On Thu, 29 Jan 2004, [ISO-8859-2] Fischer Kriszti�n 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