HOWTO: Integrating Posgresql queries into an event loop

[Mark Harrison <mh@pixar.com>]

HOWTO: Integrating Posgresql queries into an event loop.

Mark Harrison
mh@pixar.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
not a problem.  Hanging for a few milliseconds will not
be cause a problem.

However, if you have a really large or really slow query,
this will be a significant problem.
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:

    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.

    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);

    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.

    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);

Notes
-----

This HOWTO is released under the same license as the Posgresql
documentation...  Share and Enjoy!  Thanks to Tom Lane for pointing
out the need to use a cursor.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster