2

I have a Java web app (WAR deployed to Tomcat) that keeps a cache (Map<Long,Widget>) in memory. I have a Postgres database that contains a widgets table:

widget_id | widget_name | widget_value
(INT)       (VARCHAR 50)  (INT)

To O/R map between Widget POJOs and widgets table records, I am using MyBatis. I would like to implement a solution whereby the Java cache (the Map) is updated in real-time whenever a value in the widgets table changes. I could have a polling component that checks the table every, say, 30 seconds, but polling just doesn't feel like the right solution here. So here's what I'm proposing:

  1. Write a Postgres trigger that calls a stored procedure (run_cache_updater())
  2. The procedure in turns runs a shell script (run_cache_updater.sh)
  3. The script base-64 encodes the changed widgets record and then cURLs the encoded record to an HTTP URL
  4. The Java WAR has a servlet listening on the cURLed URL and handles any HttpServletRequests sent to it. It base-64 decodes the record and somehow transforms it into a Widget POJO.
  5. The cache (Map<Long,Widget>) is updated with the correct key/value.

This solution feels awkward, and so I am first wondering how any Java/Postgres gurus out there would handle such a situation. Is polling the better/simpler choice here (am I just being stubborn?) Is there another/better/more standard solution I am overlooking?

If not, and this solution is the standard way of pushing changed records from Postgres to the application layer, then I'm choking on how to write the trigger, stored procedure, and shell script so that the entire widgets record gets passed into the cURL statement. Thanks in advance for any help here.

Improve this question

1 Answer 1

Reset to default
4

I can't speak to MyBatis, but I can tell you that PostgreSQL has a publish/subscribe system baked in, which would let you do this with much less hackery.

First, set up a trigger on widgets that runs on every insert, update, and delete operation. Have it extract the primary key and NOTIFYwidgets_changed, id. (Well, from PL/pgSQL, you'd probably want PERFORM pg_notify(...).) PostgreSQL will broadcast your notification if and when that transaction commits, making both the notification and the corresponding data changes visible to other connections.

In the client, you'd want to run a thread dedicated to keeping this map up-to-date. It would connect to PostgreSQL, LISTENwidgets_changed to start queueing notifications, SELECT * FROM widgets to populate the map, and wait for notifications to arrive. (Checking for notifications apparently involves polling the JDBC driver, which sucks, but not as bad as you might think. See PgNotificationPoller for a concrete implementation.) Once you see a notification, look up the indicated record and update your map. Note that it's important to LISTEN before the initial SELECT *, since records could be changed between SELECT * and LISTEN.

This approach doesn't require PostgreSQL to know anything about your application. All it has to do is send notifications; your application does the rest. There's no shell scripts, no HTTP, and no callbacks, letting you reconfigure/redeploy your application without also having to reconfigure the database. It's just a database, and it can be backed up, restored, replicated, etc. with no extra complications. Similarly, your application has no extra complexities: all it needs is a connection to PostgreSQL, which you already have.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Wow - awesome answer @willglynn (+1) - just curious: I assume I can run a LISTEN via JDBC? Also is PgNotificationPoller just what Postgres is running under the hood, or is this something I would need to add to my project to make this NOTIFY/LISTEN model work? Thanks again!
LISTEN is a command that causes PostgreSQL to asynchronously push notifications to your connection, delivered in special NOTIFY-related messages alongside regular protocol operations. However, there are idiosyncrasies with the way the JDBC driver handles these notifications (due to SSL limitations), so apparently you need to issue a garbage query ("") to make the driver see that notifications are available. This is worse than libpq, which needs no round-trips, but still beats polling the table.
To more directly answer your question, PgNotificationPoller and its friend PgNotificationHelper are two things I found that demonstrate the JDBC getNotifications() functionality. You can use these or do it yourself as outlined in the driver documentation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.