Re: Problems with non scrollable cursors

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