Check this post out on the Arctype blog!

We’re going to take a look how I used a Postgres feature, pg_notify, to power a work schedule for a manufacturing company. This particular product went through a dozen or so stages of manufacture, and each time a product advanced to its next stage the worker would record that progress from their workstation. The app we will build in this post displayed this schedule and allowed everyone to see the day’s progress.

The Observer Pattern in SQL

If you’re only used to using the standards of SQL in Postgres, the NOTIFY and LISTEN commands might not be familiar. But with these two commands you can implement something akin to the Observer pattern, but in your SQL engine!

The Observer pattern allows one class of object to “listen” for incoming events and another class to send events to those listeners. This pattern is commonly used for instances where data is being updated or changed, and several possibly unrelated objects need to react to those changes.

Observer Examples

Listening to state changes from a Redux store from inside of a React component is a common example of this pattern. Many React components listen to a single part of the Redux store. Android’s LiveData is another great example of this pattern, where observers can be created to watch for changes and immediately update the state and UI of an app.

Observing in PostgreSQL

NOTIFY and LISTEN work together to allow you to implement this design within your Postgres database. Generally, NOTIFY will be called inside of a SQL query, oftentimes within a trigger. Triggers and event-based models go together well.

LISTEN is called from your Postgres client. When an event is triggered by NOTIFY the client will be notified. The event contains a payload so the client can tell what event was triggered (this can also contain metadata or actual data from the database). How your client receives this notification and how you are able to process it from there varies from client to client. In our example, the client will use WebSockets to update each connected schedule client after it receives the signal from pg_notify.

Building the Work Schedule App

Schema Design

Let’s begin on the Postgres side. We are modeling items that are being manufactured. Items in production will be represented by the production_item table. Each production_item has an associated product_id and a current stage of production.

We could store the current production stage as a column in the production_item table, but that would only allow us to see what stage the item is in currently. Instead, we’ll use a production_item_wip (work-in-progress) table where each row will contain a timestamp as the item progresses through the stages of production. Let’s also create a table that stores all the possible stages of production, production_stage. production_stage will have an idx integer column to store the order in which the stages occur. The query below creates the production_item_wip table, as an example.

create table production_item_wip (  
  id serial primary key,  
  insert_time timestamp default NOW(),  
  production_item_id int references production_item(id),  
  production_stage_id int references production_stage(id),  
  employee_id int references employee(id)  
);

PROTIP: You may notice I’ve included insert_time on every table. We will not need to use this column on every table for this particular example right now, but I’ve found that it often proves useful in the future. I spend a significant amount of time building queries and extracting useful statistics, and countless times I’ve been unable to use data because it lacked an insert_time. I would err on the side of adding it when designing database schema in general, if you’re unsure whether or not you should.

Postgres NOTIFY Syntax

Using NOTIFY to send an event is dead simple! Here is a trigger procedure that sends a notification to the order_progress_event channel.

create
or replace function fn_production_stage_modified() returns trigger as $psql$
begin
  perform pg_notify(
    'order_progress_event',
    'Time to refresh those screens!'
  );return new;
end;$psql$ language plpgsql;

pg_notify lends itself well to being used within a trigger when used to deliver real-time data streaming. However, you could just as easily call pg_notify from a regular SQL query: select pg_notify('order_progress_event', 'Hello world!');

Inside of a PL/pgSQL procedure, you cannot SELECT a function, like pg_notify, that returns void. Doing so will cause a Postgres error. That’s why in the first example we use perform, while in the second we can simply use select.

With that procedure created, let’s add the actual trigger so that whenever an item moves along in the production process, and thus another row is inserted for production_item_wip, the procedure above is called.

create trigger production_stage before
insert
  on production_item_wip for each row execute procedure fn_production_stage_modified();

That’s it! In this example the payload is the same each time. You could send actual data rather than just an alert, but in this example I prefer to send a basic notification so the client application can receive it and then in turn, select exactly what it needs in a separate query.

Encoding data within a notification, whether it’s a push notification or one from something like pg_notify, requires you to abstract away the source of the notification, assuming the data is normally delivered via an HTTP API. Using notifications as a “hint” for your software to reach out and get fresh data from an HTTP API simplifies the process and helps you reduce the number of different data sources you need to maintain.

Postgres LISTEN Syntax

Listening to a channel is even simpler: LISTEN order_progress_event;

That really is all.

When this event is called, we’ll want to select the latest production data for the day. Here’s a view that will show how many products have progressed through each production stage today:

create view view_daily_production_stats as
select
  count(1) as stage_count,
  ps.name as stage_namefrom production_item_wip piw
  join production_stage ps on ps.id = piw.production_stage_idwhere date(piw.insert_time) = date(now())
group by
  ps.id

Now that your client is listening, how can you react to events it receives? This varies by client, since the featuresets of programming languages that serve async events vary heavily. We are using JavaScript’s pg client in this example. JavaScript is commonly used for asynchronous web programming.

var clients = [];
function eventCallback(event) {
  query('select * from view_daily_production_stats', (data) => {
    clients.map(c => {
      c.send(data);
    });
  });
}
client.connect(function(err, client) {
  var query = client.query("LISTEN order_progress_event");
  client.on("notification", eventCallback);
});
;

Whenever a new event is received by the PostgreSQL client, the function eventCallback will be called with the payload from NOTIFY. The callback then queries the view we wrote earlier to select the most recent production stage data, and loops through to send the new data to all of the listening clients (Raspberry Pis). The clients receive the data and render HTML.

Putting it all Together

pg_notify is simple, built-in to PostgreSQL feature that has tons of different potential use cases. If you need a simple, real-time notification of just a few specific events, consider checking it out!