Trigger java events upon external Postgres database update

Description: I have a java web-application (jsp + tomcat) on linux without any framework. I use postgresql as DBMS.

I have a table (options) and a Singleton Class (Options) that encapsulate it. The Options instance is loaded at application startup and remains there indefinitely.

If a user modifies the options a method (.refreshData()) updates the instance kept in memory.

Now the troubles: there is a remote service who has direct acces to the DB and updates some fields in the options table. I don't have control on this piece of code.

I would like to trigger the refresh method when the external service updates the options table. I also know that the service starts once per day at 3PM but i don't know when it ends.

The LISTEN - NOTIFY feature offered by postgresql (Postgres trigger to update Java cache) seems to me the most elegant way to reach this goal. Following this topic I am trying a simple listener and "adapted" for my needs (Sample of code in Postgress Documentation).

EDITED after @Craig suggestions:

public class OptionsListener extends Thread {
    private int threadMills = 1000;
    private Connection conn;
    private org.postgresql.PGConnection pgconn;
    private Options optionsInstance;
    private static final String DB_URL;
    private static final String DB_USERNAME;
    private static final String DB_PASSWORD;

    static {
        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");

            DB_URL = (String) envContext.lookup("application/DB/url");
            DB_USERNAME = (String) envContext.lookup("application/DB/username");
            DB_PASSWORD = (String) envContext.lookup("application/DB/password");
        } catch (NamingException e) {
            throw new RuntimeException(e);
        }
    }

    OptionsListener(Options instance, int threadMillis) {
        optionsInstance = instance;
        this.threadMills = threadMillis;

        try {
            Class.forName("org.postgresql.Driver");
            conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            pgconn = (PGConnection) DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

            Statement stmt = conn.createStatement();
            stmt.execute("LISTEN otionsUpdate");
            stmt.close();
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    @Override
    public void run() {
        while (true) {
            Log.addItem("Polling ?");
            try {
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT 1");
                rs.close();
                stmt.close();

                PGNotification notifications[] = pgconn.getNotifications();
                if (notifications != null) {
                    Log.addItem("NOTIFY received");
                    optionsInstance.loadDbData();
                }

                Thread.sleep(threadMills); //tempo di attesa in millisecondi
            } catch (Exception e) {
                Log.addItem(getClass().getName() + " " + e.getMessage());
            }
        }
    }
}

In the Options Class I manually start the listener with this method:

public static void startExternalChangesListener(Options instance, int millis) {
    OptionsListener listener = new OptionsListener(instance, millis);
    listener.start();
}

And finally

Options.startExternalChangesListener(options, 5000);

This is the first time I tamper with Threads...

I created an AFTER UPDATE trigger that notifies the channel, and tested it throug PGAdmin3. It works like a charm but java seems not to notice...

Answers


Gotcha, it seems that the issue was in an unnecessary double connection (named pgcon).

I'm using jdbc-postgresql 9.2_p1003 and it works like a charm.

Here is the final code:

OptionsListener(Options instance, int threadMillis) {
    optionsInstance = instance;
    this.threadMills = threadMillis;

    try {
        Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

        Statement stmt = conn.createStatement();
        stmt.execute("SET application_name = 'myapp'; LISTEN optionsupdate");
        stmt.close();
    } catch (SQLException e) {
        Log.addItem(getClass().getName() + " sql error:" + e.getMessage());
    } catch (ClassNotFoundException e) {
        throw new RuntimeException(e.getMessage(), e);
    }
}

@Override
public void run() {
    while (true) {
        try {
            PGNotification notifications[] = ((PGConnection) conn).getNotifications();
            if (notifications != null) {
                optionsInstance.loadDbData();
            }

            Thread.sleep(threadMills); 
        } catch (SQLException sqle) {
            Log.addItem(getClass().getName() + " sql error:" + sqle.getMessage());
        } catch (InterruptedException ie) {
            Log.addItem(getClass().getName() + " thread error: " + ie.getMessage());
        } 
    }
}

If a listener is not picking up an expected notification the question here are the questions you should carefully sort out in order. Note I am including steps you have already done for completeness' sake.

  1. Is the notification actually being raised? Can you verify this with psql? If not, you have some troubleshooting to do there. It sounds like you have done this, so on to step 2.

  2. Is the other session actually listening? A packet snooper may come in helpful here because you can see the notification come in from the db. If it is not listening then you need to make sure the LISTEN command is run at the right time in your application and debug from there.

  3. Is the polling happening in your application correctly? If you have warnings or notices printed out to standard error during the polling process this can be helpful as well.

At this point it is hard to say where the problem is. However if you work through these questions in order (it sounds like you have addressed #1 and can start on #2) then you should be able to find the problem in not too long.


Need Your Help

Compare two colors in image?

java android image colors

I'm trying to compare the given pixel to Color.BLACK. But the problem is it yields false for all the images. (I made a black image and it also returned false!)

ray doesn't reach JSON

webgl three.js

I'm trying to catch a JSON object with a mouse click event. I use ray to identify the object, but for some reason, the objects are not always identified. I suspect that it is related to the fact th...