469,631 Members | 1,643 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,631 developers. It's quick & easy.

HOWTO: Integrating Posgresql queries into an event loop

HOWTO: Integrating Posgresql queries into an event loop.

Mark Harrison
May 27, 2004


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

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

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");
else {
if (PQntuples(res) == 0)
// finished processing all rows. Clean up the
// result and remove your callback from the
// event loop.
//process rows of data

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



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

Nov 23 '05 #1
0 1258

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Dom Boyce | last post: by
1 post views Thread by Benny Schudel | last post: by
2 posts views Thread by Dorish3 | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.