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?

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. — Ralf, Feb 07 '14 at 17:15
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. — jasons2645, Feb 07 '14 at 17:18
Sorry. I see what you mean... Looked at the driver documentation again. — Ralf, Feb 07 '14 at 17:23
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. — jjanes, Feb 07 '14 at 18:29
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? — jasons2645, Feb 07 '14 at 22:25
@jasons2645 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 — jjanes, Feb 10 '14 at 19:04

Neil McGuigan · Accepted Answer · 2022-04-06T04:15:13.113

43

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

edited Apr 06 '22 at 04:15

answered Apr 28 '14 at 22:52

Neil McGuigan

46,580
12
123
152

Also see @Oleg Mikhailov's example code below, which uses pgjdbc-ng. – Doctor Eval Jul 29 '17 at 12:17
1

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. – kensai Aug 19 '17 at 16:30
1

Instead of pointing to a blog post, it would be more useful to include the sample code here. – Mike Stoddart Mar 18 '20 at 11:59
@Neil McGuigan blog url is not functioning please check it out. – Ajay Takur Aug 30 '20 at 17:58
1

If I'm using a connection pool, is there a way to make sure the connection is not closed as the listening is indefinite? – pinkpanther Mar 07 '22 at 12:20
@MikeStoddart because it's a couple hundred of lines – Neil McGuigan Apr 06 '22 at 04:15

Oleg Mikhailov · Answer 2 · 2016-06-15T08:56:39.793

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();

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: http://stackoverflow.com/questions/37916489/listen-notify-pgconnection-goes-down-java — Siddharth Trikha, Jun 23 '16 at 09:17
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. — Doctor Eval, Jul 29 '17 at 12:16
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? — Sergei Tachenov, Apr 07 '20 at 13:12
@SergeiTachenov you can use `NOTIFY` but its value must be a static string whereas `pg_notify()` can use a static string or variable — Neil McGuigan, Apr 06 '22 at 04:13

score -3 · Answer 3 · answered Mar 13 '14 at 13:23

-3

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.

answered Mar 13 '14 at 13:23

jasons2645

629
1
6
10

2

This answer is no longer valid. – Madbreaks Jun 13 '19 at 18:58

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

3 Answers3

Linked