How do I get asynchronous / event-driven LISTEN/NOTIFY support in Java using a Postgres database?

Question

From what I can tell, the JDBC drivers for LISTEN/NOTIFY in Java do NOT support true event-driven notifications. You have to poll the database every so often to see if there's a new notification.

What options do I have in Java (possibly something other than JDBC?), if any, to get notifications asynchronously in a true event-driven manner without polling?

Jemine

If you want notifications, you need an entity to emit events. If PostgreSQL does not emit events (on a JMS topic or the like) you cannot have event-driven notifications. – Sapienza

PostgreSQL provides for LISTEN/NOTIFY, which I understand to be an asynchronous notification mechanism. The problem is that Java JDBC doesn't provide support for the asynchronous notifications and requires polling. – Jemine

Sorry. I see what you mean... Looked at the driver documentation again. – Sapienza

The way it can be done in other languages is that you ask the database handle for the underlying file descriptor of the connection socket descriptor (promising never to read or write that descriptor yourself) then you sleep on that descriptor to be become readable. Once it is readable, you can query for notifications on the database handle as usual. I don't know how you translate this into Java-ese, though. – Depressor

jjanes, that's an interesting idea and I'd be happy to give it a try. But I don't see how I would do that in Java. Could you provide any suggestions? – Jemine

@Jemine Sorry, Java sends me into fits of rage whenever I try to do anything non-trivial with it. If the connection is used for nothing other than getting notifications once the LISTEN has been done, then it would probably best to put the connection into a dedicated thread, and have it block until something happens. My best guess would be to expose org/postgresql/core/v3/ProtocolConnectionImpl.java's Peek() up the chain until it is visible to org.postgresql.PGConnection – Depressor

Answer 1

Use the pgjdbc-ng driver.

http://impossibl.github.io/pgjdbc-ng/

It supports async notifications, without polling. I have used it successfully.

See https://database-patterns.blogspot.com/2014/04/postgresql-nofify-websocket-spring-mvc.html

Source code: https://bitbucket.org/neilmcg/postgresql-websocket-example

Oleg has a nice example answer as well

Apologia

Also see @Oleg Mikhailov's example code below, which uses pgjdbc-ng. – Vitebsk

great stuff, I would like to build web stream where I will stream some new records inserted in database, I see I could do it without first pushing them into some message broker, but could use directly db queue, brilliant. – Minim

Instead of pointing to a blog post, it would be more useful to include the sample code here. – Carrel

@Neil McGuigan blog url is not functioning please check it out. – Nickolasnickolaus

If I'm using a connection pool, is there a way to make sure the connection is not closed as the listening is indefinite? – Grasmere

@MikeStoddart because it's a couple hundred of lines – Apologia

Answer 2

Here's an asynchronous pattern using com.impossibl.postgres.api (pgjdbc-ng-0.6-complete.jar) with JDK 1.8:

import com.impossibl.postgres.api.jdbc.PGConnection;
import com.impossibl.postgres.api.jdbc.PGNotificationListener;
import com.impossibl.postgres.jdbc.PGDataSource;    
import java.sql.Statement;

public static void listenToNotifyMessage() {
    PGDataSource dataSource = new PGDataSource();
    dataSource.setHost("localhost");
    dataSource.setPort(5432);
    dataSource.setDatabase("database_name");
    dataSource.setUser("postgres");
    dataSource.setPassword("password");

    PGNotificationListener listener = (int processId, String channelName, String payload) 
        -> System.out.println("notification = " + payload);

    try (PGConnection connection = (PGConnection) dataSource.getConnection()) {
        Statement statement = connection.createStatement();
        statement.execute("LISTEN test");
        statement.close();
        connection.addNotificationListener(listener);
        while (true){ }
    } catch (Exception e) {
        System.err.println(e);
    }
}

Create a trigger function for your database:

CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
    BEGIN
        SELECT pg_notify('test', TG_TABLE_NAME);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

Assign a trigger for every table you want to track:

CREATE TRIGGER table_change 
    AFTER INSERT OR UPDATE OR DELETE ON table_name
    FOR EACH ROW EXECUTE PROCEDURE notify_change();

Uppsala

I am also doing as there in your post. May I know what's the significance of while(true){} ?? I also posted a similar question regarding my earlier issue: #37916989 – Planet

The while(true) {} isn't needed, except that the try loop will terminate -- and close the Connection -- if there isn't something to stop it. The connection needs to remain open for the NOTIFY messages to work. – Vitebsk

What's the point of using SELECT pg_notify() in a PL/PgSQL procedure, when a simple pure SQL procedure of just NOTIFY test would do just fine? – Insanitary

@SergeiTachenov you can use NOTIFY but its value must be a static string whereas pg_notify() can use a static string or variable – Apologia

Answer 3

The official PostgreSQL JDBC driver now has support for LISTEN and NOTIFY, see https://jdbc.postgresql.org/documentation/server-prepare/#listen--notify

Gazelle

Answer 4

It appears as though there's no way around this. You can work around it, as several suggestions have been made along those lines, but ultimately, you're going to be polling.

Jemine

This answer is no longer valid. – Welldone