Most applications that use PostgreSQL will probably open a connection to the server and then use PQexec() to send SQL queries to the server. So did mine and it usually works just fine.
The other day, however, I happened to restart the database server while my ECR (electronic cash register) software was still running and had a connection to the database open. Strange things happened: The ECR process went to almost 100% CPU usage and was totally iresponsive. Obviously a bug in the software, which was not properly handling the (TCP) connection to the DB server being closed due to the server restart.
A quick inspection revealed that we never check the database connection status again once we opened the connection. And then, since we add an X11 input to the socket of the database connection to be notified when an asynchronous notification arrives, the software went into an endless loop at one point.
The cure for this problem is quite simple: Check the connection status using PQstatus() and if it is not CONNECTION_OK, try to reset (or reestablish) the connection to the DB server using PQreset(). The nasty thing is that you have to perform this check after (and probably before) each invocation of PQexec() and re-send the SQL command if you had to reset the connection. In our case we could do it in a single function, since we do not use PQexec() at all in out software, but PQvexec(), a function that takes a variable number of arguments, e.g. "PQvexec(conn, "select id from t1 where name = '%s'", name);".
Now I can restart the PostgreSQL server at any time while the ECRs are in full service, besides a short delay, nothing is noticed at the frontend.
Sunday, June 20. 2010
Using PostgreSQL to Decouple Applications, or, OO meets SQL (Part II)
Copyright © 2010 Marc Balmer. All rights reserved.
In the first part of this small series of blog entries about decoupling applicactions using PostgreSQL, I described the basics on how asynchronous notifications can be used to implement the observer design pattern, leading to a model-view-controller architecture.
In part II, I will go a bit more in-depth and present some ideas how to use asynchronous notifications to actually decouple applications.
Continue reading "Using PostgreSQL to Decouple Applications, or,..." »
In the first part of this small series of blog entries about decoupling applicactions using PostgreSQL, I described the basics on how asynchronous notifications can be used to implement the observer design pattern, leading to a model-view-controller architecture.
In part II, I will go a bit more in-depth and present some ideas how to use asynchronous notifications to actually decouple applications.
Continue reading "Using PostgreSQL to Decouple Applications, or,..." »
Thursday, February 18. 2010
Using PostgreSQL to Decouple Applications, or, OO meets SQL (Part I)
In the object oriented world, people like design patterns, or, elements of reusable software. One such pattern is the "Observer" pattern, where a piece of software, the "Observer", registers with another piece of software, the "Model", to be notified when the internal state of the "Model" changes. The "Observer" can then query the "Model" for the data changed. This design pattern is often applied in the so-called Model-View-Controller programming paradigm, where a "View" observes a "Model" and a "Controller" changes the state of the "Model".
What works at the object level in an object oriented programming language can be applied to distributed PostgreSQL applications as well, allowing for proper decoupling of applications or application parts. The tools needed for this are PostgreSQL's trigger procedures and the asynchronous notification mechanism. In this first installment of a small series of blog entries, I will talk about the basics needed to implement a distributed decoupled software solution. Continue reading "Using PostgreSQL to Decouple Applications, or,..." »
What works at the object level in an object oriented programming language can be applied to distributed PostgreSQL applications as well, allowing for proper decoupling of applications or application parts. The tools needed for this are PostgreSQL's trigger procedures and the asynchronous notification mechanism. In this first installment of a small series of blog entries, I will talk about the basics needed to implement a distributed decoupled software solution. Continue reading "Using PostgreSQL to Decouple Applications, or,..." »
(Page 1 of 1, totaling 3 entries)
