Re: Advice on using materialized views

Thanks both for your replies.

Wicher wrote:
> On Mon, 06 Dec 2021 18:48:47 +0000
> "Phil Endecott" <spam_from_pgsql_lists@chezphil.org> wrote:
>> and 
>> I need to modify the definition of a view that filters the "new" 
>> values from the raw table each time the materialised view is 
>> refreshed.
>
> You won't necessarily need to rewrite the "recent data" view definitions, I think. What is
> deemed "recent" depends on what's in the materialized views (it'd be anything newer than
> whatever is in there). The good news is that you can simply query for that :-)
> So trivially, in your "the data that is more recent than the stuff from the materialized
> views" non-materialized view you'd use a definition like
> SELECT .... WHERE sometimestamp > (select max(sometimestamp) from the_materialized_view)
> or something along those lines.


I guess I was hoping that someone would suggest a more "magic"
way to do this sort of thing. Actually I'm a bit surprised that
materialised views don't event have a way to either

- Refresh a materialised view whenever a source table is modified;

- Refresh a materialised view whenever it is read, if a source table
has changed since it was last refreshed.

Beyond that, I could imagine smart updates where e.g. if you
modify source table rows with primary key K, then you only need
to refresh materialised view rows derived from K.

I think this could all be done on top of triggers. I wonder, do any
other databases do things like this automagically?


Regards, Phil.