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.
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
