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.
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.
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.
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.
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.
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.