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:

https://gist.github.com/geobabbler/6d4fd1127da72201b1aab40db7b279b2

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.

https://gist.github.com/geobabbler/341793e4eb88e9f112c2605226791a40

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

https://gist.github.com/geobabbler/83628a2364d3ac90958e70890642cfd6

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.

https://gist.github.com/geobabbler/45068f225b0787b9b45e4b2d9374d3ee

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:

https://gist.github.com/geobabbler/95fcb4501fa9c987d2f8b9b7d03d52d8

The resulting console output looks like this:

https://gist.github.com/geobabbler/e705a7d342551a6da2aafc489f01be28

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.