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

[Shane Kerr]

Stephen,

On Thursday, 2012-10-04 16:02:12 +0100, 
Stephen Morris <stephen at isc.org> wrote:
> On 04/10/12 15:23, Shane Kerr wrote:
> > 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.

The problem with using a temporary table is that we're either storing
the results on disk (which will greatly impact performance) or
in-memory. If we store it in-memory then we're basically repeating our
problem of not being able to incrementally read a result, except using
memory on the server side instead of in the BIND 10 client.

We could reduce this memory footprint by only storing a query-unique
number (recnum in your example) and a reference to the records table:

CREATE TEMPORARY TABLE pghack (recnum SERIAL, id INTEGER);
INSERT INTO pghack(id) 
    SELECT id FROM records 
        WHERE (zone_id = ?1) AND (rname >= ?2) AND (rdtype >= ?3)
        ORDER by rname, rdtype;

And then performing a join to get the actual results:

SELECT rdtype, ttl, sigtype, rdata, name, rname FROM pghack, records
    WHERE (pghack.recnum > lowval) AND (pghack.recnum <= highval) AND
          (records.id = pghack.id);

It might be better than doing multiple selects like I proposed, but
intuitively it seems more complicated and likely to have worse
performance. Only actual measurements can be sure though!

Cheers,

--
Shane