 Java, Spring and Web Development tutorials  1. Introduction
In this article, we’re going to take a look at the LISTEN and NOTIFY commands within PostgreSQL. We’ll see what they are, how they can be used, and how we can utilize them from within our applications.
2. What Are LISTEN and NOTIFY?
PostgreSQL has support for asynchronous communication between the server and connected clients using the LISTEN and NOTIFY commands. These PostgreSQL-specific extensions enable us to use the database as a simple messaging system, allowing us to generate events out of the database that clients can react to. This can be useful for many purposes, such as real-time dashboards, cache invalidation, data auditing, and more.
2.1. Listening for Notifications
We use the LISTEN command to register interest in receiving events. This takes the name of the channel that we want to listen to:
postgres=# LISTEN my_channel;
LISTEN
Once we’ve done this, our connection can receive asynchronous notifications of events happening on this channel.
Every connection that registers interest in these notifications receives them, so the system effectively broadcasts the messages rather than delivering them to a single recipient. This means that we can use this mechanism to easily tell every single client about events that are happening within our database.
Note that if we’re using psql then we don’t receive notifications automatically. Instead, we need to execute our LISTEN command again, and we’ll get shown all of the notifications that have been raised since the last time:
postgres=# LISTEN my_channel;
LISTEN
.....
postgres=# LISTEN my_channel;
LISTEN
Asynchronous notification "my_channel" with payload "Hello, World!" received from server process with PID 66.
Here, we can see that some connection has raised an event with the payload “Hello, world!”, and our listening connection has been notified about it.
While there’s no maximum number of listeners we can register, each listener must keep its database connection open in order to receive notifications, so the maximum connection limit effectively acts as a limit. In addition, every listener will use some amount of resources, so having too many can potentially cause performance issues.
2.2. Raising Notifications
Now that we know how to listen for events, we also need to be able to raise them. We can raise events using the NOTIFY command. This takes both the channel name and the message to send:
postgres=# NOTIFY my_channel, 'Hello, World!';
NOTIFY
When this command is executed, all connections that have previously executed the appropriate LISTEN command can receive this event, as we saw earlier.
Our payload is optional, but if we either don’t provide one or provide NULL, then the system will act as if the empty string was provided. It also has a maximum size of 8,000 bytes. If we try to send more than that, we’ll get an error and no listeners will be notified.
Notifications take part in transactions. This means that if we raise a notification during an active transaction, the system won’t send it until the transaction commits. It also means that if the transaction rolls back, the system won’t send the notification at all.
2.3. Dynamic Messages
The NOTIFY command requires that the message sent is exactly specified. We’re not able to do anything to generate the message dynamically, including simple string concatenation:
postgres=# NOTIFY my_channel, 'Hello, ' || 'World';
ERROR: syntax error at or near "||"
LINE 1: NOTIFY my_channel, 'Hello, ' || 'World!';
However, we can instead use the pg_notify function to generate our notifications. This can take any message formed in any way:
postgres=# SELECT pg_notify('my_channel', 'Hello, ' || 'World!');
pg_notify
-----------
(1 row)
In this case, the channel name has to be provided as a string, and the payload as a separate string. We can construct these strings in any way we need, including by using the results of SQL statements.
2.4. Raising Events From Triggers
While we can raise events ourselves by executing the appropriate statements, we can also get the database to raise them for us automatically. For example, we can register trigger functions that execute at appropriate times, and these functions can also generate these notifications:
CREATE OR REPLACE FUNCTION notify_table_change() RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify('table_change', TG_TABLE_NAME);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER table_change
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW EXECUTE PROCEDURE notify_table_change();
Once this is done, if anything happens to create, update, or delete rows in the table_name table, this trigger will automatically send a notification on the table_change channel with the name of the table that changed.
3. Raising Notifications With JDBC
We can raise notifications from JDBC exactly the same as we’ve already seen.
Firstly, we need to connect to the database. We can do this using the official drivers for now. Let’s add them to our build:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.6</version>
</dependency>
We can then create a connection as normal:
Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "postgres", "mysecretpassword");
Once we’re connected, we can raise notifications using either the NOTIFY command or the pg_notify() function:
try (Statement statement = connection.createStatement()) {
statement.execute("NOTIFY my_channel, 'Hello, NOTIFY!'");
}
As we saw before, if we want to do anything more than just a bare string, such as using bind parameters, then we need to use pg_notify instead:
try (PreparedStatement statement = connection.prepareStatement("SELECT pg_notify(?, ?)")) {
statement.setString(1, "my_channel");
statement.setString(2, "Hello, pg_notify!");
statement.execute();
}
Both approaches work the same way, and the system delivers the notifications exactly as expected:
postgres=# postgres=# LISTEN my_channel;
LISTEN
Asynchronous notification "my_channel" with payload "Hello, NOTIFY!" received from server process with PID 390.
Asynchronous notification "my_channel" with payload "Hello, pg_notify!" received from server process with PID 390.
Here, we can see that our listening session successfully received both notifications raised by the Java code.
4. Listening With the Official JDBC Drivers
While raising notifications with JDBC is straightforward, listening for them is more involved. Receiving asynchronous messages from the database isn’t an official part of the JDBC specification, so we need to resort to driver-specific functionality.
The first thing we need to do is execute our LISTEN statement:
try (Statement statement = connection.createStatement()) {
statement.execute("LISTEN my_channel");
}
However, in order to receive the notifications themselves, we need to use the getNotifications() method on the raw PGConnection object. This means that we first need to ensure we’ve got the correct type of connection:
PGConnection pgConnection = connection.unwrap(org.postgresql.PGConnection.class);
We then call getNotifications() to get any notifications that have been received. We need to do this in a loop, polling the database at a suitable frequency:
while (!Thread.currentThread().isInterrupted()) {
PGNotification[] notifications = pgConnection.getNotifications(1000);
if (notifications != null) {
// React to notifications
}
}
When we receive notifications, we can react to them however we like. However, we won’t receive any more until the next time getNotifications() is called, so we need to remember not to stop this loop if we need to react efficiently to any more.
We have three different ways that getNotifications() can be called. The simplest is with no parameters, in which case it will return immediately with whatever notifications are outstanding. However, this is not the recommended solution. Instead, there is a version that takes a timeout, in milliseconds, that the thread will block:
PGNotification[] notifications = pgConnection.getNotifications(100);
In this case, the call will return after either this timeout passes or any notifications are available, whichever happens first.
If we call this version with a timeout value of 0, then this will block forever. This effectively means that we will only return as soon as any notifications are available. If we’re running this method on a dedicated thread, then this can help make things easier to manage since we’ll no longer need to do any idle waiting.
5. Listening with PGJDBC-NG
If we want to receive notifications without needing to poll the database, we can achieve this with some alternative drivers. The PGJDBC-NG drivers are compatible with PostgreSQL while offering some more advanced features, including the ability to register callbacks for notifications.
Before we can use them, we need to add them to our build:
<dependency>
<groupId>com.impossibl.pgjdbc-ng</groupId>
<artifactId>pgjdbc-ng</artifactId>
<version>0.8.9</version>
</dependency>
We can then create a connection as normal, only this time we use a URL of type jdbc:pgsql instead of jdbc:postgresql. For example:
Connection connection = DriverManager.getConnection("jdbc:pgsql://localhost:5432/postgres", "postgres", "mysecretpassword");
We still need to execute our LISTEN command on the connection, exactly the same as before. However, this time, we’re able to register a listener to receive callbacks whenever a notification happens. In order to do this, we need to implement the PGNotificationListener interface:
class Listener implements PGNotificationListener {
@Override
public void notification(int processId, String channelName, String payload) {
LOG.info("Received notification: Channel='{}', Payload='{}', PID={}",
channelName, payload, processId);
}
}
We can then register an instance of this with our connection:
PGConnection pgConnection = connection.unwrap(com.impossibl.postgres.api.jdbc.PGConnection.class);
pgConnection.addNotificationListener(new Listener());
At this point, as long as the connection is active, we’ll automatically receive notifications as soon as they get raised without needing to poll the database:
10:34:03.104 [PG-JDBC I/O (1)] INFO com.baeldung.listennotify.JdbcLiveTest -- Received notification: Channel='my_channel', Payload='Hello, NOTIFY!', PID=844
10:34:03.106 [PG-JDBC I/O (1)] INFO com.baeldung.listennotify.JdbcLiveTest -- Received notification: Channel='my_channel', Payload='Hello, pg_notify!', PID=844
Not only is this easier for us to manage, but it’s more efficient for our application since we’re no longer having to poll the database waiting for something to happen.
6. Conclusion
In this article, we’ve taken a brief look at the LISTEN and NOTIFY commands within PostgreSQL and how to make use of them from JDBC connections. The next time you need to be able to raise events from your database, why not give it a go.
As usual, all of the examples from this article are available over on GitHub. The post Event-Driven LISTEN/NOTIFY Support in Java using PostgreSQL first appeared on Baeldung.
Content mobilized by FeedBlitz RSS Services, the premium FeedBurner alternative. | 
