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.
Example Application
Let's assume the following application scenario: A Point of Sale (POS) application that consists of three parts: A graphical frontend that is operated by the cashier using the touchscreen, a PostgreSQL database that contains all the products and which is used to store sales data, and an application that graphically displays the current sale on a customer display using a separate VGA display. Whenever the cashier books a product, the item is recorded in the database and the customer display is updated, showing the sold products, the total amount, and possibly more data.
The point of sale frontend and the customer display are realized as two separate programs, let's call them xpos and xdisp. Of course there must be some form of interprocess communication between xpos and xdisp, so that the proper data gets displayed. A naive approach for such communication could be to use a socket interface, either local or over the network, to have xpos "talk" to xdisp via some self-defined protocol. While this could work, it is naive insofar, as the two processes are coupled: A common protocol needs to be defined and xpos must know about the xdisp program, it's properties and so on. The code in xpos gets larger and more complicated, since the communication with xdisp needs to be handled.
Decoupling
A better approach is to completely separate the two programs and have them operate independently. If there is an xdisp process running, it will just simply show the current sale, xpos does not have to care about this at all. Now how do we achieve this?
Using PostgreSQL the solution is surprisingly simple: We implement the aforementioned Observer design pattern in a way where the database is the "Model" and the xdisp program is the "Observer". The xpos program plays the role of a "Controller" in the Model-View-Controller paradigm. Whenever the xpos program changes data in the database, e.g. by a cashier adding an item to the current sale, the xdisp program gets notified, queries the data of the current sale, and displays it. For this to work, we need two PostgreSQL mechanisms: Asynchronous notifications and trigger procedures.
Asynchronous Notifications
Asynchronous notifications are a mechanism where one or more PostgreSQL clients register their interest for certain events. To do so, the SQL command "LISTEN
" is being used. Whenever a PostgreSQL client or a stored procedure on the server triggers the "" event, all listening clients are sent an asynchronous notification. To trigger an event the SQL command "NOTIFY " is used. Please note that LISTEN and NOTIFY are PostgreSQL extensions that are not part of the SQL standard.
Trigger Procedures
Trigger procedures are stored procedures, written in any supported language, that are fired when an UPDATE, INSERT, or, DELETE statement is executed on a database table. So they can be used to track changes to database tables. Trigger procedures are quite versatile, they can be fired for each data row that is being changed or only once for the complete statement. Plus they can be fired before or after the change to the database occurs. Furthermore, triggers that are applied for each data row have access to the old or new row values.
Putting it together
For our application, the trigger procedures are rather simple: They merely only execute a NOTIFY command when they are executed. As they are fired on every change to a database table, they can be used as the notification mechanism for "Observers" that have previously issued a LISTEN command. The observing client can then query the current values. All this is completely transparent to the client that updates the database table, it doesn't even know that there are other clients that observe the database tables.
Back to our POS application. The xdisp program will issue the LISTEN command to be notified when the sale data changes, the xpos program will just update the table. The postmaster will fire the trigger procedure which executes the NOTIFY command on every update to the sales table.
Now there is a problem: Asynchronous notifications can only signal the occurance of an event, but they do not carry any further data. Imagine a larger shop with many POS terminals: We would have only one PostgreSQL database server running, but several xpos and xdisp programs, one for each POS terminal. How can an xdisp program know which xpos process fired an event? How can an xdisp process track a particular sale, which is identified by a numerical id in the database?
In the next part I will show a concrete example of asynchronous notifications and trigger procedures in action, and also show how the problem just mentioned can be solved. So please stay tuned...
You have found the 179th edition of Log Buffer, the weekly review of database blogs. Welcome. Enjoy your stay. We begin with . . . SQL Server Merrill Alrich gets going with a fresh juxtaposition–his thoughts on motorcycles a...
Tracked: Feb 19, 21:48