[bind10-dev] missing C API for PostgreSQL: row-by-row query result retrieval

[Shane Kerr]

Mukund,

On Thursday, 2012-10-04 18:57:11 +0530, 
Mukund Sivaraman <muks at isc.org> wrote:
> Hi Shane
> 
> On Wed, Oct 03, 2012 at 02:52:31PM +0200, Shane Kerr wrote:
> > So, for example, we assuming we want to transfer a zone, we could do
> > something like:
> > 
> >     SELECT rdtype, ttl, sigtype, rdata, name, rname FROM records 
> >         WHERE zone_id = ?1
> >         ORDER by rname, rdtype
> > 	LIMIT 100 
> > 
> > This would give us no more than 100 rows. 
> > 
> > We can then call again, using the last rname and rdtype, like this:
> > 
> >     SELECT rdtype, ttl, sigtype, rdata, name, rname FROM records 
> >         WHERE (zone_id = ?1) AND (rname >= ?2) AND (rdtype >= ?3)
> >         ORDER by rname, rdtype
> > 	LIMIT 100 
> > 
> > We continue this until we get less than our maximum 100 rows back.
> > 
> > There is a bit of a corner case when our 100th record is in the
> > middle of the same rname/rdtype (which is why I have >= and not >
> > there).
> 
> There is a LIMIT syntax supported by PostgreSQL where we can provide
> the starting row too, so something like:
> 
> SELECT ... FROM records WHERE ... ORDER BY ... LIMIT 100 OFFSET 1
> SELECT ... FROM records WHERE ... ORDER BY ... LIMIT 100 OFFSET 101
> SELECT ... FROM records WHERE ... ORDER BY ... LIMIT 100 OFFSET 201
> 
> http://www.postgresql.org/docs/8.1/static/queries-limit.html

I considered the OFFSET option, but I have had bad luck with it in my
previous attempts to use it like this. The problem is that to use
OFFSET the server basically needs to do a query and scan through the
results to get to the offset, so you turn an O(N) operation into an
O(N^2) operation.

That is, OFFSET 101 means "go to OFFSET 1, then scan through 100
records to find the right place to start returning results".

The database could include some metadata in the indexes to tell you
what the row number is, but I doubt this is done, as it's not generally
useful. Some quick command-line benchmarking could tell us for sure; it
would probably be worthwhile because it avoids the corner cases.

This is why I suggested using the actual key, which we know will take
us to the correct place using our indexes.

Cheers,

--
Shane