Re: Watching for view changes

From: Mitar <mmitar(at)gmail(dot)com>
To: George Neuner <gneuner2(at)comcast(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Watching for view changes
Date: 2018-12-22 07:41:16
Message-ID: CAKLmikN1U7TLi1px==U4ipVM71UEckBtNakGXbKSTYTYKD2Bbw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

On Fri, Dec 21, 2018 at 11:10 PM George Neuner <gneuner2(at)comcast(dot)net> wrote:
> 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.

Caching is needed if you want to compute a difference between previous
version and new. But if you want to just know new value, then I could
imagine that (a simple implementation would) on every change to any
underlying table check if this change matches selectors of the query
and if such apply its operations/projections and produce the new
value.

So yes, you need caching if you want to decrease CPU use, but you
could also see it as new values being computed again and again through
query. Would such caching you are mentioning really improve
performance, I do not know, so it might be premature optimization?

If we do not go down the cache path, then it seems there is no other
way to have this "apply this query again on those updated rows from
table". In a way I see query as a transformation (in relational
algebra) of original tables to results and I would need to be able to
apply that transformation again on new rows. And if some additional
data is necessary (some rows for other non-changed tables) it would
just recompute that again, instead of using cache.

If we do go down the cache path, then I agree, materialized views
seems nice, but I would prefer temporary materialized views: they
should be cleaned up at the end of the session. Moreover, they should
be ideally just in memory, not really on disk. Materialized views are
currently stored to disk, no?

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

So you are saying I could use triggers on the cache table to know what
really changed instead of having to compute diff myself? Interesting.

Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark 2018-12-22 09:14:05 Re: Query planner / Analyse statistics bad estimate rows=1 with maximum statistics 10000 on PostgreSQL 10.2
Previous Message George Neuner 2018-12-22 07:10:02 Re: Watching for view changes