Triggered Notifications Using PostGIS, Redux

A while back, I wrote a post about using Python to generate triggered notifications in PostGIS. The approach described in that post is somewhat fraught with peril and I’ve learned a little bit more since then, so I have a different approach, using PostgreSQL NOTIFY and LISTEN, which I’ll describe here.

The steps I followed are:

Create a spatial table:

Create a trigger function. This is where the formatting gets done. It’s best to keep the payload small, so this function creates a GeoJSON object that returns the geometry, record ID, table name, and an indication of the action. In order to keep the example simple, this one is hard-coded to handle inserts. Notice that the channel is named ‘actions’ in the trigger function. This is important for consuming the payload later.

Add an actual trigger to the table. As mentioned above, this example only handles inserts, but updates could be handled as well.

Next, build some sort of consumer to listen for the notifications. In this case, I’ll use a very simple Node app that uses ‘pg-pubsub‘ to handle the interactions.

Notice that the ‘addChannel’ call listens to the ‘actions’ channel defined in the trigger function above. If the message payload is a JSON string, then pg-pubsub automatically processes it into a JavaScript object. The code above simply dumps the data to the console (both the object and its stringified serialization). This is ideally where something more intelligent, such as adding the object to a message queue, may occur.

At this point, just run the node application and it will wait for messages. Issue a simple insert in psql, like so:

The resulting console output looks like this:

This approach is safer for the database, but all things have trade-offs, so it’s best to research performance implications and such. It’s also a fairly accessible middle ground before jumping off the cliff into logical decoding. The basic tenets of this approach are to keep message payloads small and not to try to do too much processing in the trigger itself. The goal is really to get a notification out of the database so that an external application can do more heavy lifting.