Thread: Problems with non scrollable cursors

Problems with non scrollable cursors

From
Daniele Bufarini
Date:
Hi,
I'm developing a web application. I haveto know exactly how many pages I
have and I have to allow the user to jump to a specific page( this is
where I used limit and offset): I know that this solution is VERY slow
and I tried to use cursors.... BUT (from PostgreSQL docs):
" The Statement must be created with a ResultSet type of
ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need
to be rewritten to take advantage of this, but it also means that you
cannot scroll backwards or otherwise jump around in the ResultSet." !!!
So I cannot, for example, use the ResultSet.absolute() to jump back in
the result set!
Have you some ideas how to speed up that operations without using the
LIMIT/OFFSET combo?
Thank you very much!


Daniele Bufarini

Re: Problems with non scrollable cursors

From
Kris Jurka
Date:

On Fri, 21 Jan 2005, Daniele Bufarini wrote:

> I'm developing a web application. I haveto know exactly how many pages I
> have and I have to allow the user to jump to a specific page( this is
> where I used limit and offset): I know that this solution is VERY slow
> and I tried to use cursors.... BUT (from PostgreSQL docs):
> " The Statement must be created with a ResultSet type of
> ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need
> to be rewritten to take advantage of this, but it also means that you
> cannot scroll backwards or otherwise jump around in the ResultSet." !!!
> So I cannot, for example, use the ResultSet.absolute() to jump back in
> the result set!

You are kind of out of luck.  The only way a scrollable cursor will be
faster than a limit/offset is if you use it multiple times.  To use it
multiple times you need to keep this cursor open across numerous page
requests.  This is a complicated piece of backend state because it may
take up significant server resources and then there is the business of
"finding" this cursor again when the user comes back.  Are you really
going to try and keep an open database connection for every user?  Not in
any web app that I know.

Kris Jurka

Re: [S] Re: Problems with non scrollable cursors

From
Daniele Bufarini
Date:
Kris Jurka wrote:

>On Fri, 21 Jan 2005, Daniele Bufarini wrote:
>
>
>
>>I'm developing a web application. I haveto know exactly how many pages I
>>have and I have to allow the user to jump to a specific page( this is
>>where I used limit and offset): I know that this solution is VERY slow
>>and I tried to use cursors.... BUT (from PostgreSQL docs):
>>" The Statement must be created with a ResultSet type of
>>ResultSet.TYPE_FORWARD_ONLY. This is the default, so no code will need
>>to be rewritten to take advantage of this, but it also means that you
>>cannot scroll backwards or otherwise jump around in the ResultSet." !!!
>>So I cannot, for example, use the ResultSet.absolute() to jump back in
>>the result set!
>>
>>
>
>You are kind of out of luck.  The only way a scrollable cursor will be
>faster than a limit/offset is if you use it multiple times.  To use it
>multiple times you need to keep this cursor open across numerous page
>requests.  This is a complicated piece of backend state because it may
>take up significant server resources and then there is the business of
>"finding" this cursor again when the user comes back.  Are you really
>going to try and keep an open database connection for every user?  Not in
>any web app that I know.
>
>Kris Jurka
>
>

Actually using the cursor multiple time is what I was thinking...
In fact, I can keep an open database connection only for large result
set and use a one time connection for all the others.
My problem is that I cannot scroll back using a cursor... I'm wondering
If I have to modify the jdbc driver or the back end or if there are
other solutions, less time consuming...

        Daniele Bufarini


--
.-----------------------------------------------------------------.
| Daniele Bufarini          Informazioni Editoriali I.E. S.p.A.   |
| software architect        Via Bergonzoli 1/5 - 20127 Milano (IT)|
|                           Tel: +3902283151 Fax: +390228315900   |
| http://www.ie-online.it   daniele.bufarini@ie-online.it         |
| There are only two truly infinite things, the universe and human|
| stupidity; I am unsure of the former. - Albert Einstein         |
'-----------------------------------------------------------------'


Re: [S] Re: Problems with non scrollable cursors

From
Kris Jurka
Date:

On Mon, 24 Jan 2005, Daniele Bufarini wrote:

> Actually using the cursor multiple time is what I was thinking...
> In fact, I can keep an open database connection only for large result
> set and use a one time connection for all the others.
> My problem is that I cannot scroll back using a cursor... I'm wondering
> If I have to modify the jdbc driver or the back end or if there are
> other solutions, less time consuming...

The easiest thing to do is to forgo parts of the JDBC interface and do it
manually.

Statement.execute("DECLARE mycursor SCROLL CURSOR FOR ...");

Then:

Statement.execute("MOVE ABSOLUTE 2000 IN mycursor");
ResultSet = Statement.executeQuery("FETCH FORWARD 10 FROM mycursor");


To fix this in the driver there are two avenues of attack here.  The 7.4
driver used explicit DECLARE and FETCH calls for cursors.  The driver
would need to be modified to add a SCROLL keyword and then handle moving
back and forth.  A patch was produced to do this, which I had some
concerns about, that the poster never responded to:

http://archives.postgresql.org/pgsql-jdbc/2004-05/msg00164.php

The 8.0 driver has been changed to use protocol level portals which are
pretty much the same as cursors.  The problem is that protocol only
has support for forward cursors in both declaration and fetching.  To fix
things along these lines would require pretty much all of the work in the
driver as the 7.4 path, but would also require some backend work and more
importantly, convincing people to change the frontend/backend protocol.

Kris Jurka


Re: [S] Re: Problems with non scrollable cursors

From
Daniele Bufarini
Date:
Kris Jurka wrote:

>On Mon, 24 Jan 2005, Daniele Bufarini wrote:
>
>
>
>>Actually using the cursor multiple time is what I was thinking...
>>In fact, I can keep an open database connection only for large result
>>set and use a one time connection for all the others.
>>My problem is that I cannot scroll back using a cursor... I'm wondering
>>If I have to modify the jdbc driver or the back end or if there are
>>other solutions, less time consuming...
>>
>>
>
>The easiest thing to do is to forgo parts of the JDBC interface and do it
>manually.
>
>Statement.execute("DECLARE mycursor SCROLL CURSOR FOR ...");
>
>Then:
>
>Statement.execute("MOVE ABSOLUTE 2000 IN mycursor");
>ResultSet = Statement.executeQuery("FETCH FORWARD 10 FROM mycursor");
>
>
>To fix this in the driver there are two avenues of attack here.  The 7.4
>driver used explicit DECLARE and FETCH calls for cursors.  The driver
>would need to be modified to add a SCROLL keyword and then handle moving
>back and forth.  A patch was produced to do this, which I had some
>concerns about, that the poster never responded to:
>
>http://archives.postgresql.org/pgsql-jdbc/2004-05/msg00164.php
>
>The 8.0 driver has been changed to use protocol level portals which are
>pretty much the same as cursors.  The problem is that protocol only
>has support for forward cursors in both declaration and fetching.  To fix
>things along these lines would require pretty much all of the work in the
>driver as the 7.4 path, but would also require some backend work and more
>importantly, convincing people to change the frontend/backend protocol.
>

Thank you very much for your explanations, Kris!
First I'll try to use cursors without the JDBC interface: If that
shouldn't work as required, I'll consider the two other avenues of attack.
I'll let you know...

                Best regards,
                    Daniele Bufarini.