472,096 Members | 1,224 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,096 software developers and data experts.

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 1352

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 leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.