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

On Thu, 20 Dec 2018 12:12:14 -0800, Mitar <mmitar@gmail.com> wrote:


>On Thu, Dec 20, 2018 at 8:11 AM Mike Rylander <mrylander@gmail.com> wrote:
>> 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 mean that for every SQL query I am making to a database, I would
>manually determine which tables are involved and then setup triggers
>with NOTIFY that the SELECT might have changed?

You can just put an update trigger on every table.  You aren't forced
to listen for notifications.


>I am trying to see if this could be something I could abstract out
>that it would be done automatically for any query. I have looked into
>using EXPLAIN to get a list of tables involved in a query, but the
>issue is that it look non-trivial to determine what has really changed
>as a consequence of those tables changing. I would have to cache
>myself what was the result of a query and then myself do a diff? Are
>there any ways to do this inside PostgreSQL without having to cache
>results on the client and do it there?

You don't need to cache anything on the client.  An update trigger on
a table can notify a listening client when data is changed.  

The difficulty is that views are not guaranteed to be updateable.  As
a technical matter, you can put a trigger on a view, but it may never
fire.

AFAIK, update and delete triggers do work on materialized views,
because they really are physical tables.  Depending on your use case,
materialized views may or may not be of help to you.



>> 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.
>
>But this would mean that I would have to know how changes on involved
>tables influence query results. 

The trigger function itself is agnostic WRT the format of the table -
the old and new row data are provided generically as records, and you
can to convert the record data, e.g., to JSON or XML, without knowing
its format.

AFAIHS, you really only need to know the table format to inspect or
modify the row data.


>I would like to not have to do SQL query parsing and understanding
>on the client. So ideally, I would get information directly from 
>PostgreSQL. For me, an API where I could do AFTER UPDATE trigger on
>FOR EACH ROW on a view would be perfect. In that trigger I could get
>information which rows of the view changed and then use NOTIFY to 
>inform the client. 

You might want to use BEFORE UPDATE so you get both the old and new
row data.


YMMV,
George