Trigger java events upon external Postgres database update

Question

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...

Maybe try to negotiate with the external service so that it notifies you via for example your web service method when it updates the DB? — dratewka
– dratewka, Commented May 11, 2013 at 16:51
You listed all the possible solutions. Choose the one you prefer. I would personally make the application stateless and go to the database each time I need an option. This makes the problem disappear, makes the application clusterable, is extremely simple to implement, and doesn't have to deal with staleness. And it's probably fast enough, because querying a table whose data can be kept in memory is extremely fast. — JB Nizet
– JB Nizet, Commented May 11, 2013 at 16:51
@JBNizet I think the second option is the most elegant. I would prefer that solution if not 'expensive' (in fact I am asking help in choosing the best one). The option table is accesse too many times, every page served by the server needs to query it more than once. That's why a static singleton instance wich works like a cache. — Azathoth
– Azathoth, Commented May 11, 2013 at 17:19
@dratewka as I told I cannot rely on the external service and in the future there will be more services or process updating directly the data table. — Azathoth
– Azathoth, Commented May 11, 2013 at 17:20
LISTEN/NOTIFY is certainlly the way to go for this if you need to maintain a local cache. It's not especially hard to implement if you're ok with making a single dedicated (non-pooled) connection from your cache singleton to the database and doing local-side-only polling for events on a timer. — Craig Ringer
– Craig Ringer, Commented May 12, 2013 at 5:05

Azathoth · Accepted Answer · 2014-01-31 13:15:19Z

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

Chris Travers · Accepted Answer · 2013-06-06 06:10:57Z

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.

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.
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.
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.

Collectives™ on Stack Overflow

Trigger java events upon external Postgres database update

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related