Listening to data changes in PostgreSQL and C#
To poll or not to poll?
Nowadays, fast, responsive UI that reacts to data changes is pretty much a given (well, not always, yes, but often enough to talk about it!). If it is an application with just one user it is not that hard to implement, but what if there are multiple users in a system that affect each other?
The answer would be… not to poll if you can, because more often than not there is another, better way. Many databases, both NoSQL and RDBMS offer functionality to push events to the connected clients.
So, what are we doing in this post?
RavenDB has awesome feature called Data Subscriptions, which allow its client-api to receive near real-time notifications and changed data.
I was curious to see how such feature works in other databases. The following is the result of my tinkering with PostgreSQL, I tried (and succeeded!) making PostgreSQL C# driver to receive events of any change of data in the database.
Setting the server-side to send notifications about data changes
In order to set up change event listening, I will be using NOTIFY)/LISTEN commands.
Setting up the database
I installed the latest version PostgreSQL server at the time of writing, version 12. (But you don’t have to use version 12 - from what I’ve read, the following SQL code should work for any PostgreSQL that supports JSON natively)
As a test dataset I have used a Northwind database from this repository, but any dataset can be used, really.
A function that will be used in change triggers
Any table we want to watch for changes in its data would have a trigger that would “forward” the change to a function that would use NOTIFY statement to listening clients.
The following is such function:
CREATE FUNCTION public."NotifyOnDataChange"()
Now, we will set up triggers to wire change events with NotifyOnDataChange function.
CREATE TRIGGER "OnDataChange"
That is nice, but what if we want notifications from all of the tables in the database?
The following function will iterate over all tables and create triggers in each of them
CREATE FUNCTION public."CreateOnDataChangeForAllTables"()
That’s it! The server-side is ready to send notifications for data changes.
Setting-up PostgreSQL client to receive events
For a client test app, I used .Net Core 3.0 project with Npgsql data provider.
The following is code for listening for data changes.
There we have it. A simple way to listen for changes in PostgreSQL.
Note, if the NpgsqlConnection client is not connected and changes happen, data change event will obviously be missed. Considering that RavenDB’s Data Subscriptions allow handling “missed” events as well, I consider this a flaw of PostgreSQL notifications feature.