Re: Large queries; fetchsize, cursors and limit/offset

On Mon, 2003-11-03 at 17:53, Dave Tenny wrote:
> The LIMIT/OFFSET technique, last time I checked, is a poor choice for
> PostgreSQL

> In other words, it isn't a pragmatic solution for really large result sets.
> I'll be curious to hear other people's solutions here.

Cursors work in that you can ask for the first 100 rows, then the next
100, then the next 100.... It'll sit and wait for you to request the
next group.

The disadvantage is you need to get the same database connection over
and over for the same client. With web stuff that can be tricky.

If you can tie a db connection to a clients session (return it to the
pool when the client session expires) or have a single connection handle
all of these scroll type queries it becomes fairly painless.

With cursors that can cross transaction boundaries (7.4) it is just that
much easier.