Application Note: Integrating Posgresql queries into an event loop.

Here's a note on using the async API. It's not runnable code (due to
the variances in event loop packages), but I'm happy to add this
to whatever documentation package is appropriate...

Advice and feedback welcomed...
Mark

Application Note: Integrating Posgresql queries into an event loop.

Mark Harrison
mh(at)pixar(dot)com
May 27, 2004

Problem
-------

The commonly used postgresql APIs will block until completed.
If you are in a GUI event loop, this will block your GUI
responsiveness until a query is completed.

If the queries are small and finish quickly, there is probably
OK. Hanging for a few milliseconds will not be a problem.

However, if you have a large or slow query,this can be significant.
For example, one of my tables (call it "big") has about
14 million rows. It takes about two minutes for

res = PQexec(conn, "select * from big");

to return. An additional unpleasant side effect is that
the process then requires about 1.2 gig of memory to buffer
the returned data.

Solution, part 1:
-----------------

First, we need to break up the returned data into more
manageable chunks. For this, we use an SQL cursor. Here
are the relevant bits of code (error checking elided in
order to make the flow more clear):

res = PQexec(conn, "BEGIN");
res = PQexec(conn, "DECLARE cur CURSOR FOR select * from big");

while (1) {
res = PQexec(conn, "FETCH 1000 in cur");
if (PQntuples(res) == 0)
break
else
//process rows of data
}

res = PQexec(conn, "CLOSE cur");
res = PQexec(conn, "END");

This has two immediate benefits:

1. There is not a two minute pause while the data is being
transferred and buffered from the server to the client.

2. The memory requirements for the client program are much
lower.

Solution, part 2
----------------

Now that we have broken our data retrieval into managable
chunks, we need to integrate this logic into the event loop.

As is typical for event loop programming, there are two
main bits of code:

1. Set up query and callback.

// establish the database connection. Set the connection to
// non-blocking mode, and create a cursor for fetching our data.
// Flush the request to the server.
conn = PQconnectdb("...");
rc = PQsetnonblocking(conn, 1);
res = PQexec(conn, "BEGIN");
res = PQexec(conn, "DECLARE cur CURSOR FOR select * from big");
rc = PQsendQuery(conn, "FETCH 1000 in cur");
PQflush(conn);

// Get the postgresql socket and add it to our event loop
sock = PQsocket(conn);
add_to_event_loop(READABLE, sock, myhandler);

2. The callback which processes the returned data. This is referred
to as myhandler() in the previous step.

// Consume the input from the server. This will clear the file
// descriptor "readable" flag. Process as much data as we can
// in the while loop. When we have retrieved all the data for
// this FETCH, issue another FETCH command. When there are no
// more tuples returned, the query is finished and you can clean
// up the request.
rc = PQconsumeInput(conn);
while (!PQisBusy(conn)) {
rc = PQconsumeInput(conn); // (is this necessary?)
res = PQgetResult(conn);
if (res == NULL) {
// we have finished all the rows for this FETCH. We need
// to send another FETCH to the server.
rc = PQsendQuery(conn, "FETCH 1000 in cur");
PQflush(conn);
return;
}
else {
if (PQntuples(res) == 0)
// finished processing all rows. Clean up the
// result and remove your callback from the
// event loop.
else
//process rows of data
}
}

If you wish to cancel a query midway through processing (e.g.,
if the user presses "cancel"), call

PQrequestCancel(conn);

Copying and Acknowledgements
----------------------------

This document is released under the same license as the Postgresql
documentation. Thanks to Tom Lane for his help in understanding
this API.