Postgres Listen and Notify Features

Asynchronous messages are a powerful communication mechanism that allows real-time notifications and event-based interactions between different components or processes.

The LISTEN and NOTIFY feature in PostgreSQL provides a robust solution to implement asynchronous communication within the database. By subscribing to specific channels and publishing notifications, we can enable a seamless and timely data exchange between sessions or even integrate with external applications.

This tutorial explores the LISTEN/NOTIFY feature in PostgreSQL. It is a compelling feature and one of the oldest mechanisms of PostgreSQL which faciliatates the developers to listen to what’s going on in the database.

Before we dive into the tutorial, please ensure that you have PostgreSQL installed and have a basic understanding of SQL and database concepts.

Enable the LISTEN and NOTIFY Features

The first step is enabling your PostgreSQL server LISTEN and NOTIFY features. We can do this by installing the “uuid-ossp” extension.

Run the following command:

create extension if not exists "uuid-ossp";

The uuid-ossp refers to the UUID (Universally Unique Identifier) data type and related functions that the PostgreSQL extension provides. The uuid-ossp extension is bundled with PostgreSQL and allows us to generate, manipulate, and store the UUID values.

UUIDs are 128-bit identifiers that are unique across all devices and time which make them suitable for scenarios where creating the globally unique identifiers is necessary.

Working with PostgreSQL Listen and Notify

Let us now explore how we can use the LISTEN and NOTIFY features to subscribe to channels, publish and listen to messages.

Subscribing to Notification

Within a database session, we can listen for notifications using the LISTEN command followed by the channel name that you wish to subscribe to.

The command syntax is as follows:

LISTEN channel_name;

Replace the channel_name with your target channel.

Publishing the Notifications

We can publish the notifications to the subscribers of a specific channel using the NOTIFY command. The command syntax is as follows:

NOTIFY channel_name, 'payload';

The “payload” parameter is optional and can contain additional information related to the notification.

Subscribing to Notifications

After establishing a connection, you can subscribe to notifications by executing an SQL statement using the appropriate database library or driver. Using PSQL, you can use the pg_notify as shown in the following:

SELECT pg_notify('channel_name', 'payload');

Example: Using the Listen/Notify Feature with Triggers

We can combine the database triggers with PostgreSQL’s LISTEN and NOTIFY features to create more sophisticated and dynamic notification systems.

Define a Trigger Function

Let us start by creating a trigger function that defines the logic to generate notifications. The function runs automatically when a new insert event occurs in the database.

CREATE OR REPLACE FUNCTION notify_insert()

RETURNS TRIGGER AS $$

BEGIN

PERFORM pg_notify('notifications, 'A new row has been inserted');

RETURN NEW;

END;

$$ LANGUAGE plpgsql;

The given query creates a function that publishes a message to the notifications channel when a new row is inserted into the database tables.

Create Database Trigger

Once we define a trigger function, we can associate it with a specific table and event using the CREATE TRIGGER statement. For example, the following query triggers an insert operation on the sample_table:

CREATE TRIGGER insert_trigger

AFTER INSERT ON sample_table

FOR EACH ROW

EXECUTE FUNCTION notify_insert();

Listen for Notifications

Open a separate database session and execute the LISTEN command to subscribe to the desired channel.

LISTEN notifications;

Test the Trigger

Insert a new row into the table that is associated with the trigger.

INSERT INTO sample_table (name, age) VALUES ('May', 23);

The trigger automatically executes the trigger function which sends a notification to the specified channel.

Conclusion

We explored how we can publish and subscribe to the database notifications using the LISTEN and NOTIFY features provided in PostgreSQL.