Alex Howansky <alex@wankwood.com> writes:
> It seems that I'd need to do this:
> begin
> declare thing cursor for select ...
> move N from thing
> fetch 4 from thing
> close thing
> commit
> for every single page that gets viewed. That seems pretty silly though. So,
> three questions:
> 1) Would this method be better than doing the "select ... offset N
> limit 4" for each page?
No. The actual query work will be the same, plus you have some (small)
bookkeeping overhead to define the cursor. You cannot come out ahead.
In fact you could come out behind, for small N, since the straight
SELECT will get optimized with the knowledge that only a few tuples need
be retrieved, whereas the cursor will not be.
> 2) What happens if a cursor is never closed / transaction is never commited?
Nothing catastrophic, but it's best not to hold a transaction open
longer than you have to (long-running transactions cause various sorts
of inefficiencies).
I'm not clear on why this is a problem. If you are able to use a cursor
across queries at all, then you must have *some* persistent state that
is holding the connection to the backend that you sent the cursor
declaration to. It doesn't seem that hard to add a little more info to
keep track of the fact that you have an open transaction on that
connection. Maybe keep track of the last time the cursor was used,
and drop it after N minutes of inactivity. Worst case is that you have
to create the cursor again if you drop it too soon...
regards, tom lane
