Postgres触发更新Java缓存 [英] Postgres trigger to update Java cache

问题描述

我有一个Java Web应用程序（WAR部署到Tomcat），它在内存中保存一个缓存（ Map< Long，Widget> ）。我有一个Postgres数据库包含小部件表：

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)

Widget POJOs和小部件表记录，我使用MyBatis。我想实现一个解决方案，即当 widgets 表中的值发生更改时，Java缓存（Map）会实时更新。我可以有一个轮询组件，每隔30秒检查表，但轮询只是不觉得像这里正确的解决方案。所以这里是我的建议：

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. 编写一个Postgres触发器调用存储过程（ run_cache_updater / code>）


2. 该过程依次运行一个shell脚本（ run_cache_updater.sh ）


3. 脚本base-64编码更改的 widgets 记录，然后将编码记录cURL到HTTP URL


4. Java WAR有一个servlet监听cURLed URL，并处理发送给它的任何 HttpServletRequests 。它base-64解码记录，并以某种方式将其转换为 Widget POJO。


5. 缓存（ Map<



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.

此解决方案 感觉尴尬，所以我第一想知道任何Java / Postgres大师在那里会如何处理这种情况。是否有更好/更简单的选择在这里（我只是固执？）有没有另一个/更好/更标准的解决方案，我忽视？

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?

如果没有，是将改变的记录从Postgres推送到应用程序层的标准方法，然后我窒息如何编写触发器，存储过程和shell脚本，以便整个小部件记录传递到cURL语句中。感谢您提供任何帮助。

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.

推荐答案

我不能说MyBatis，但我可以告诉你，PostgreSQL有一个

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.

首先，在小部件上设置一个触发器在每次插入，更新和删除操作上运行。提取主键和 NOTIFY widgets_changed，id 。 （好吧，从PL / pgSQL，你可能会想要 PERFORM pg_notify（...）。）PostgreSQL将广播你的通知，如果和什么时候提交，通知和其他连接可见的相应数据更改。

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.

在客户端中，您希望运行一个专门用于保持此地图更新的线程。它将连接到PostgreSQL， LISTEN widgets_changed 开始排队通知， SELECT * FROM widgets 填入地图，然后等待通知到达。 （检查通知显然是涉及轮询JDBC驱动程序，它吸引，但不是您可能认为有误，请参阅 PgNotificationPoller 了解具体实施情况。）一旦看到通知，请查看并更新您的地图。请注意，在初始 SELECT * 之前 LISTEN 很重要，因为记录可以在 SELECT * 和 LISTEN 。

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.

这种方法不需要PostgreSQL知道任何东西关于您的应用程序。它所要做的就是发送通知;你的应用程序休息。没有shell脚本，没有HTTP，没有回调，允许您重新配置/重新部署应用程序，而不必重新配置数据库。它只是一个数据库，它可以备份，恢复，复制等，没有额外的复杂性。同样，您的应用程序没有额外的复杂性：它所需要的只是一个连接到PostgreSQL，你已经有。

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.

这篇关于Postgres触发更新Java缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！