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

[Stephen Morris]

On 04/10/12 15:23, Shane Kerr wrote:

> 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.

How about using a stored procedure to execute the query on the server
and store the result in a temporary table? If that temporary table
were to include an additional column of type SERIAL, we end up with a
table containing the records we want, each associated with a unique
(incrementing) number.

CREATE TABLE TEMP (recnum SERIAL, rdtyppe ...);
INSERT INTO TEMP(rdtype, ttl, sigtype, rdata, name, rname)
   SELECT rdtype, ttl, sigtype, rdata, name, rname FROM records
      WHERE (zone_id = ?1) AND (rname >= ?2) AND (rdtype >= ?3)
      ORDER by rname, rdtype;

The retrieval then selects records from the temporary table in
successive queries using only the record number.

SELECT rdtype, ttl, sigtype, rdata, name, rname FROM TEMP
       WHERE recnum > lowval AND recnum <= highval;

... and when the query is finished, drop the table.

Stephen