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

On Fri, 21 Dec 2018 20:49:23 -0800, Mitar <mmitar@gmail.com> wrote:

>On Thu, Dec 20, 2018 at 6:28 PM George Neuner <gneuner2@comcast.net> wrote:
>
>> The difficulty is that views are not guaranteed to be updateable.
>
>What you mean by that? I mean, just to be clear. I care only about
>read-only views. The changes to views I care about change view because
>underlying tables are updated. I do not care about UPDATE queries
>against views themselves.
>
>So you mean here that it is not guaranteed that you can make an UPDATE
>query against a view? Yes, that I know. But if underlying tables
>change, view is always updated, no? I mean, this is the main purpose
>of a view. :-)

Terminology. <grin>  UPDATE vs update.

I'm certain you know this already, but to be clear:


A normal view really is just a SELECT whose results reflect the
current data in the underlying tables.  That is, a normal view is
*code*, NOT data, and its result is an anonymous *virtual* table that
exists only in the transaction that applied the view.  Thus the
results can't be monitored for changes without employing some kind of
deliberate caching.

A materialized view IS exactly such a deliberate cache of results from
applying a view.  It is a real table that can be monitored for changes
using INSERT, UPDATE and/or DELETE triggers.  


>> As a technical matter, you can put a trigger on a view, but it may never fire.
>
>If fires only for UPDATE queries against views themselves (and only if
>INSTEAD OF trigger does not invalidate the update). But it never fires
>for updates which happen because of changes to the underlying tables.

Right.  A trigger on a (normal) view actually is on the virtual result
table - but that table doesn't exist outside of the transaction that
applied the view.

I'm not certain offhand, but I *believe* that - like a SELECT - view
results will change on the fly during a transaction if the underlying
tables change and the isolation settings allow to see it.

But even if a view will update while open, to use this you'd have to
hold the result set open (with a cursor) while also keeping the
transaction open.  Once the result set is closed, the view results are
gone.


With a materialized view, you must apply the view code again (call
REFRESH) to see changes to the underlying tables - it doesn't happen
automagically.  But when refreshed, triggers on the cache table would
react to changes.


>I would like to know:
>
>a) Do people agree/think that would be a good API for my use case?
>b) How hard would it be to implement/develop something like that? Is
>this something PostgreSQL already knows internally and it is just a
>question of exposing it?

It probably would be possible to take a trigger set on a view and
transitively trigger on the base tables underlying it.  

But to what end?  The results (of a normal view) don't persist beyond
the current transaction, and allowing them to do so would, AFAICS, be
a violation of the SQL standard.  Postgresql is not going to do that
(certainly not for a new feature, only for backward compatibility).

>c) Is there some better way to achieve this?
>  :
>Yes, materialized views are too heavy for me. But having update and
>delete triggers only memory-only temporary views would be perfect.

Unfortunately, materialized views are the only reasonable server side
solution that I can think of.


>Also, materialized views have to be manually refreshed, no? So it is
>not really true that they get updated automatically (and that update
>triggers would run) as soon as underling tables are modified?

Yes.  But you can do that with triggers on the base tables.  Or timers
if "most current" information is not critical.  

Perhaps you can use synchronized normal and materialized views.  Use
the normal view for current information in transactions, and the
materialized view to asynchronously update passive LISTEN clients with
delta information.


>> >I would like to not have to do SQL query parsing and understanding
>> >on the client.

That I understand. <grin>  I'm a compiler and language hobbiest ... 
even just parsing modern SQL can be painful.


>> 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.
>
>Not sure how this helps. 

If you know specifically what columns have changed, only they would
need be communicated to a LISTEN client.  Not necessary to send the
whole row (which may be much larger).

AFTER shows you only the new row.  BEFORE shows you both the old and
new rows so you can determine what changed (and how, if relevant).


>So, I am not sure how triggers on underlying tables can really inform
>how to know what in the view has been updated?

They can't - if any results are computed you'd have to apply the view
again to see the changes.  And then you'd have to a whole new view and
not just updates to the old.

I don't see any way out of this that doesn't involve a materialized
view.  It's the only way I can see to get just updates and not deal
with the whole result set again.


YMMV,
George