From: | Mitar <mmitar(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Watching for view changes |
Date: | 2018-12-20 09:17:14 |
Message-ID: | CAKLmikP+PPB49z8rEEvRjFOD0D2DV72KdqYN7s9fjh9sM_32ZA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Deepti Sharma S | 2018-12-20 10:56:04 | PostgreSQL version compatibility with RHEL7.6 |
Previous Message | Tom Lane | 2018-12-20 04:45:20 | Re: Does idle sessions will consume more cpu and ram? If yes,how to control them |