Any way to monitor Postgresql query changes in realtime using LISTEN & NOTIFY (or NodeJS)?

Question

So I have a custom Postgresql query that retrieves all rows within a specified longitude latitude radius, like so:

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;

And in my node server, I want to be able to be notified every time the number of rows in this query changes. I have looked into using a Node library such as pg-live-query but I would much rather pg-pubsub that works with existing Postgres LISTEN/NOTIFY in order to avoid unnecessary overhead. However, as far as I am able to understand, PostgreSQL TRIGGERs only work on UPDATE/INSERT/DELETE operations and not on any specific queries themselves. Is there any way to accomplish what I'm doing?

Answer 1

You need to set up the right triggers that will call NOTIFY for all the clients that use LISTENon the same channel.

It is difficult to advise how exactly you implement your NOTIFY logic inside the triggers, because it depends on the following:

• How many clients the message is intended for?
• How heavy/large is the query that's being evaluated?
• Can the triggers know the logic of the query to evaluate it?

Based on the answers you might consider different approaches, which includes but not limited to the following options and their combinations:

• execute the query/view when the outcome cannot be evaluated, and cache the result
• provide smart notification, if the query's outcome can be evaluated
• use the payload to pass in the update details to the listeners
• schedule query/view re-runs for late execution, if it is heavy
• do entire notification as a separate job

---

Certain scenarios can grow quite complex. For example, you may have a master client that can do the change, and multiple slaves that need to be notified. In this case the master executes the query, checks if the result has changed, and then calls a function in the PostgreSQL server to trigger notifications across all slaves.

So again, lots and lots of variations are possible, depending on specific requirements of the task at hand. In your case you do not provide enough details to offer any specific path, but the general guidelines above should help you.

Answer 2

Async & LISTEN/NOTIFY is the right way!

You can add trigger(s) on UPDATE/INSERT and execute your query in the body of trigger, save number of rows in simple table and if value changed call NOTIFY. If you need multiple params combinations in query you can create/destroy triggers from inside your program.