1, design a simple table inside postgresql with trigger function added, which will send pg_notify message when any update/delete/add into a table. peace of trigger code:
{
notification = json_build_object('table', TG_TABLE_NAME,
'action', action_s);
perform pg_notify('Channel1', notification::text);
perform addlog(concat('Channel1', ' ', notification::text) );
}
after run the client java program (mentioned below), i can clearly see the notify trace log (by the addlog function) in a seperated table. while not able to get any notification called inside Java below.
2, Java Application, The Watcher Java File, which get input PGConnection from Main.
import java.sql.*;
import com.impossibl.postgres.api.jdbc.PGConnection;
import com.impossibl.postgres.api.jdbc.PGNotificationListener;
import com.impossibl.postgres.jdbc.PGDataSource;
public class DbWatcher extends Thread {
//public class DbWatcher extends Thread implements PGNotificationListener {
//private Thread t;
private String threadName;
private PGConnection pgconn;
private PGNotificationListener listener;
DbWatcher(String name, PGConnection cnn) throws SQLException
{
this.pgconn = cnn;
threadName = name;
System.out.println("DbWatcher " + threadName);
}
public void run()
{
System.out.println("DbWatcher Running " + threadName );
Statement statement = null;
try
{
listener = new PGNotificationListener() {
@Override
public void notification(int processId, String channelName, String payload) {
//PGNotificationListener.super.notification(processId, channelName, payload);
System.out.println("notification = " + payload);
}
};
pgconn.addNotificationListener(listener);
statement = pgconn.createStatement();
statement.execute("LISTEN Channel1");
statement.close();
} catch (SQLException e)
{
e.printStackTrace();
}
while (true)
{
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
//@Override
//public void notification(int processId, String channelName, String payload) {
// //PGNotificationListener.super.notification(processId, channelName, payload);
// System.out.println("DbWatcher get notification = " + payload);
//}
}
Another Worker Java File, which get the same input PGConnection from Main and insert record into database above, which make the above trigger get called ( that's true).
so anyone knows the problem why the watcher notification cannot get called?