Thanks both for your replies.
Wicher wrote:
On Mon, 06 Dec 2021 18:48:47 +0000
"Phil Endecott" <spam_from_pgsql_lists@xxxxxxxxxxxx> 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.
