In my team's most recent app, we've been utilizing a few of PostgreSQL's useful built-in features. Last week we dipped our toes into the Full Text Search feature, where we discussed how to make special Text Search queries on special Text Search vectors to achieve a loose text matching more akin to a proper search engine rather than simple text matching/filtering. Check out that previous article here.

This week, we will look at more of PostgreSQL's features: the LISTEN and NOTIFY, and a basic implementation thereof. This post aims to teach you the very basics of understanding how these features work, how they communicate, and hopefully inspire you to create your own use case.

How do the PostgreSQL LISTEN and NOTIFY features work?

LISTEN

Calling LISTEN from the PostgreSQL shell will register the current session as an event listener on the specific channel argument given after the LISTEN keyword. A command of LISTEN insertion_event will register the current session to listen on the "insertion_event" channel. Whenever a NOTIFY is invoked on this channel, we will receive it on the listener's session. Multiple sessions (i.e. listeners) can listen to a single channel, and all of them will receive a notification upon an event being sent over that channel.

LISTEN insertion_event;

NOTIFY

Calling NOTIFY from the shell will send a notification to the channel given, with an optional "payload" argument that would be supplied after the channel. Notifications are visible to all listeners on the channel.

NOTIFY insertion_event;

Upon executing the above command, on the listener's channel (which can indeed be the same channel that notifies, as per the below example) we will receive either a notification result if no payload is supplied, or we will receive the notification with the payload if one is.

LISTEN insertion_event;
NOTIFY insertion_event;
Asynchronous notification "insertion_event" received from server process with PID 8448.

With a payload:

LISTEN insertion_event;
NOTIFY insertion_event, 'Hello world';
Asynchronous notification "insertion_event" with payload "Hello World" received from server process with PID 8448.

pg_notify

Instead of the NOTIFY command, PostgreSQL supplies us with the pg_notify() function that takes two text parameters, the first being the channel name, and the second the payload, and is a much easier syntax to implement than the NOTIFY command if your app requires you to work with non-constant channel names and payloads. We will see an example of this in the code below.

Creating a function in PostgreSQL

  CREATE OR REPLACE FUNCTION new_reservation() RETURNS trigger AS $new_reservation$
      BEGIN
      PERFORM pg_notify('insertion_event', row_to_json(NEW)::text );
        RETURN NEW;
      END;
  $new_reservation$ LANGUAGE plpgsql;

In this code above, we are creating a PostgreSQL function called new_reservation() that performs a pg_notify invocation on channel "insertion_event", and then takes that new row (a reference is supplied to us natively with the NEW keyword), converts it to JSON (row_to_json()), casts the result to text (::text), and sends it as the notification payload.

  CREATE TRIGGER reservation_trigger AFTER INSERT ON "Reservations"
    FOR EACH ROW EXECUTE FUNCTION new_reservation();

In this code above, we create a trigger called reservation_trigger that will initiate an invocation of our recently created new_reservation() function after the event of a row being inserted onto the table "Reservations".

So now any time a new Reservation is created, any session that I have listening to the "insertion_event" channel will receive that notification. I'm sure you can see how this could become very useful. Perhaps I would want to trigger any particular behavior if an item is added to the Reservation table, something like a notification being passed to the end user so they can be made aware of a new message, or in this case a reservation on an item that has been listed on the app (this could be implemented with a Websocket connection, maybe something like Socket.IO? You could learn more about implemented Socket.IO from my previous post here.

PostgreSQL has proven itself to be very handy with all of it's built-in features. My team and I have been very satisfied with the functionality that we've been able to put together utilizing these features. I hope this tutorial has helped you understand how these features work at a basic level, and also inspired you to come up with your own ideas on how to use them! See you next time.