On Thu, Dec 20, 2018 at 4:17 AM Mitar <mmitar@xxxxxxxxx> wrote: > > Hi! > > I am trying to see how I could use NOTIFY/LISTEN to watch changes of a > complicated SELECT query which spans multiple tables. Ideally, I would > like to leave to PostgreSQL to determine when some data (and which > data) in the result of the SELECT query has changed. So I am thinking > that creating a temporary view using that query could be a way, only > if I would find a way to watch such view for changes somehow. > If, as I assume, the FROM clause of the SELECT is essentially static, just with varying WHERE conditions, you could just use an AFTER INSERT/UPDATE/DELETE trigger on each of the tables involved to send a NOTIFY whenever data that is exposed by the SELECT is changed. You can deliver a payload, such as the table name, primary key value and, with a little work, even a list of fields that were modified as a JSON blob, and let the application do whatever needs to be done to react the the changes -- issue other queries, etc. Of course that depends on your application knowing when it's appropriate to NOTIFY, or being able to handle spurious NOTIFYs. HTH, -- Mike Rylander | Executive Director | Equinox Open Library Initiative | phone: 1-877-OPEN-ILS (673-6457) | email: miker@xxxxxxxxxxxxxxxxxxxxx | web: http://equinoxinitiative.org > But it seems this is not really possible. I looked into two mechanisms: > > - Logical replication. Instead of NOTIFY/LISTEN I could simply create > a publication over a view and then subscribe to it. But it seems > logical replication can be done only over base tables and not views. > [1] > - Using "after" trigger on the view to get notification when the view > gets changed. I could even use transition relations to have > information what changed. But sadly it seems that this is possible > only if there is also INSTEAD OF trigger on the view. But I would like > to get notification when the view has changed because underlying > tables have changed, and not because of an UPDATE query on the view > itself. Moreover, I do not really need writable views. [2] > > So I wonder if I am missing anything. Is there some other best > practice how to get notifications when result of a query changes in > real-time? And information what changed? > > How hard it would be to implement such triggers on a view for whenever > a view changes? Is there a process to make a feature request? > > (Also, I have not really managed to get statement level "after" > triggers to be run on a view for at all. Because if I rewrite a query > with INSTEAD OF then triggers on those tables are triggered, not > really view's. So not sure what is even expected use there.) > > [1] https://www.postgresql.org/docs/devel/logical-replication-restrictions.html > [2] https://www.postgresql.org/docs/devel/trigger-definition.html > > > Mitar > > -- > http://mitar.tnode.com/ > https://twitter.com/mitar_m >