Hi!
On Fri, Dec 21, 2018 at 11:10 PM George Neuner <gneuner2@comcast.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
