Re: Watching for view changes - Mailing list pgsql-general

On Thu, Dec 20, 2018 at 4:17 AM Mitar <mmitar@gmail.com> 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@equinoxinitiative.org
 | 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
>