使用LISTEN和NOTIFY（或NodeJS）实时监控Postgresql查询更改的任何方法？

Question

我有一个定制的Postgresql查询，可以检索指定经纬度半径内的所有行，如下所示：

SELECT *, 
earth_distance(ll_to_earth($1,$2), ll_to_earth(lat, lng)) as distance_metres
FROM RoomsWithUsers
WHERE earth_box(ll_to_earth($1,$2), $3) @> ll_to_earth(lat, lng)
ORDER by distance_metres;

在我的 Node 服务器中，我希望能够在此查询中的行数更改时得到通知。我已经研究过使用 Node 库，例如 pg-live-query，但我更愿意使用与现有 Postgres LISTEN/NOTIFY 兼容的 pg-pubsub，以避免不必要的开销。然而，据我所能理解，PostgreSQL 的 TRIGGERs 仅适用于 UPDATE/INSERT/DELETE 操作，而不是任何特定的查询本身。是否有办法达到我的目标？

- BeardMagician

---

{btsdaf} - Juan Carlos Oropeza

WHERE条件中的条件是不可变的（对于相同的参数始终产生相同的结果）吗？ - klin

{btsdaf} - Joel Coehoorn

@JuanCarlosOropeza 问题是我想根据客户的位置创建多个定制查询（即提供自定义的纬度、经度和半径），并且我不希望每个客户在RoomsWithUser视图更新时都收到通知，只有观察特定查询的客户才会收到通知。 - BeardMagician

{btsdaf} - BeardMagician

Answer 1

您需要设置正确的触发器，以调用在同一通道上使用LISTEN的所有客户端的NOTIFY。

如何在触发器中实现您的NOTIFY逻辑很难给出具体建议，因为它取决于以下因素：

- 消息面向的客户端有多少？ - 被评估的查询有多么复杂/大？ - 触发器能否知道查询的逻辑以对其进行评估？

基于您的答案，您可以考虑不同的方法，包括但不限于以下选项及其组合：

- 在无法评估结果时执行查询/视图，并缓存结果 - 如果可以评估查询的结果，请提供智能通知 - 使用有效负载将更新详细信息传递给侦听器 - 如果查询过于耗费资源，则安排查询/视图重新运行以进行延迟执行 - 将整个通知作为单独的作业执行

某些情况可能变得相当复杂。例如，您可能有一个主客户端可以进行更改，而多个从属客户端需要收到通知。在这种情况下，主节点执行查询，检查结果是否已更改，然后调用PostgreSQL服务器中的函数来触发所有从属客户端的通知。

因此，具体要求取决于具体任务的特定要求，所以我们无法给出任何具体路线。但是以上通用指南应该对您有所帮助。

- vitaly-t

Answer 2

异步 & LISTEN/NOTIFY 是正确的方式！

您可以在 UPDATE/INSERT 上添加触发器，并在触发器中执行您的查询，将行数保存在简单表中，如果值发生更改，则调用 NOTIFY。如果您需要在查询中使用多个参数组合，则可以从程序内部创建/销毁触发器。

- delfer

---

{btsdaf} - Blag

{btsdaf} - klin

{btsdaf} - BeardMagician

{btsdaf} - BeardMagician

你弄清楚这个问题了吗，@BeardMagician？ - MrR