Thread: Problems with non scrollable cursors
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
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
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 |
'-----------------------------------------------------------------'
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
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.