How can I get LISTEN / NOTIFY support with asynchronous / event support in Java using the Postgres database?

Question

From what I can tell, the JDBC drivers for LISTEN / NOTIFY in Java do NOT support true event driven notifications. You should periodically test the database to see if there is a new notification.

What options do I have in Java (maybe something other than JDBC?), If any, to receive notifications asynchronously using a true event without polling?

Answer 1

Use the pgjdbc-ng driver.

http://impossibl.imtqy.com/pgjdbc-ng/

It supports asynchronous notifications without polling. I have used it successfully.

See http://blog.databasepatterns.com/2014/04/postgresql-nofify-websocket-spring-mvc.html

Oleg has a good example of an answer

Answer 2

Here's an asynchronous template 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 each table that you want to track:

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

Answer 3

There seems to be no way around this. You can get around this, as several proposals have been made in these areas, but in the end you are going to conduct a survey.

Answer 4

If you're fine with polling a database, here's how to do it:

 PGConnection con = ... try (Statement s = con.createStatement()) { s.executeUpdate("listen x"); s.executeUpdate("notify x, 'abc'"); } for (PGNotification n : con.getNotifications()) { System.out.println(String.format("%s, %s, %s", n.getPID(), n.getName(), n.getParameter())); } 

The above will give something like:

 11796, x, abc 11796, x, xyz 

You need to run at least one operator to receive new notifications from the connection.

Answer 5

This is a big limitation of Postgres notification / listening, and basically makes it useless for Java applications. I assume the database is being updated from another application.

Depending on how often the database is updated, there are 2 options:

• If the database is updated frequently, simply poll the query this way select * from table where update_timestamp > ? . This column must be indexed.

• If the database does not update, often make an application that updates messages about the publication of database updates in the message broker (for example, zero mq).