Re: Paged Query

On 07/09/2012 09:22 PM, Shaun Thomas wrote:
> On 07/09/2012 07:02 AM, Craig Ringer wrote:
>
>> Do do cursors.
>
> Did you mean "Do not use cursors" here?
>
Oops. "So do cursors".
>> Then the user goes away on a week's holiday and leaves their PC at
>> your "next" button.
>
> This exactly. Cursors have limited functionality that isn't directly
> disruptive to the database in general. At the very least, the
> transaction ID reservation necessary to preserve a cursor long-term
> can wreak havoc on your transaction ID wraparound if you have a fairly
> busy database. I can't think of a single situation where either client
> caching or LIMIT/OFFSET can't supplant it with better risk levels and
> costs.
>
My ideal is a cursor with timeout.

If I could use a cursor but know that the DB would automatically expire
the cursor and any associated resources after a certain inactivity
period (_not_ total life, inactivity) that'd be great. Or, for that
matter, a cursor the DB could expire when it began to get in the way.

I'm surprised more of the numerous tools that use LIMIT and OFFSET don't
instead use cursors that they hold for a short time, then drop if
there's no further activity and re-create next time there's interaction
from the user. ORMs that tend to use big joins would particularly
benefit from doing this.

I suspect the reason is that many tools - esp ORMs, web frameworks, etc
- try to be portable between DBs, and cursors are a high-quirk-density
area in SQL RDBMSs, not to mention unsupported by some DBs. Pity, though.

There's nothing wrong with using a cursor so long as you don't hang onto
it over user think-time without also setting a timeout of some kind to
destroy it in the background.

--
Craig Ringer