0

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?

Steven
  • 1

0 Answers0