473,715 Members | 2,766 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

HOWTO: Integrating Posgresql queries into an event loop

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 = PQsetnonblockin g(conn, 1);
res = PQexec(conn, "BEGIN");
res = PQexec(conn, "DECLARE cur CURSOR FOR select * from big");
rc = PQsendQuery(con n, "FETCH 1000 in cur");
PQflush(conn);

sock = PQsocket(conn);
add_to_event_lo op(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(con n);
if (res == NULL) {
// we have finished all the rows for this FETCH. We need
// to send another FETCH to the server.
rc = PQsendQuery(con n, "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

Nov 23 '05 #1
0 1508

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2810
by: news | last post by:
We're being asked to get Quickbooks Enterprise edition for our business. We use a completely hand-made online store (PHP and mySQL) and used to simply create CSV sheets that were imported as invoices into regular Quickbooks. But I guess that's neither 100% accurate and may not even be doable in newer Quickbooks. We want to be able to integrate Quickbooks into the system without replacing our system. Has anyone done this?
9
4352
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my predecessor, I hasten to add) so that each day it creates a copy of the record for each company, changes the date to today's date, and prompts the user for any changes of ratings on that day. The resulting data table grows by approx 600 records per...
4
2851
by: Ray Dukes | last post by:
What I am looking to do is map the implementation of interface properties and functions to an inherited method of the base class. Please see below. '**************************************************************************** ' Issues '****************************************************************************
0
1272
by: swong4 | last post by:
Hi all, I am trying to use an ActiveX control on the server-side of an ASP.NET 2.0 application written in C#. The ActiveX control is a 3rd-party interface to a data feed used by my application and does not have a GUI. To accomplish this, I am trying to create a Windows.Form to host the ActiveX control with the intent that the events generated by the control could be used by the server for data processing.
1
2346
by: Benny Schudel | last post by:
Howto put these two queries together? sql = "SELECT DISTINCT medium_id, COUNT(tag) AS count FROM tags WHERE tag = 'test' GROUP BY medium_id ORDER BY count DESC" This query is working. Id like to try something like sql = "SELECT * FROM media WHERE id IN (SELECT DISTINCT medium_id FROM
3
2037
by: Mike-deerenews | last post by:
I would like to check for the existence of an attribute in order to prevent an exception from firing. Could someone provide an example? tia Mike
7
20330
by: =?Utf-8?B?QVRT?= | last post by:
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g. Please help, I'm trying to write an ASP page to use ADO to run a long query against an Oracle 10g database, to create tables, if they do not already exist. In terms of ASP/ADO, that would be fine in a SQL Server Sense by a simply ASP/Server-Side JavaScript as such: var cnTemp = Server.CreateObject("ADODB.Connection");
2
1603
by: Dorish3 | last post by:
I want to apologize ahead of time for being a novice with MS Access and VBA. I desperately need help with 2 queries that I am trying to put together. I want to thank anyone that can help me out with this situation. I want to put a select query(Query1) that uses one table and the criteria would be . When an event number is entered it will give me the Event Number, Event Name, Event Date, Event time, and House Size. Next, I need to...
7
1944
by: Protoman | last post by:
Is this an efficient way to integrate an explicit function of x from a to b: #include <iostream> using namespace std; class Integrate { public: Integrate(long double (*f)(long double& x)):fn(f){}
0
8823
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8718
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9343
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9198
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9104
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6646
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5967
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3175
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2541
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.