Re: Incremental refresh - Materialized view

On 11/6/2017 11:34 PM, Krithika Venkatesh wrote:
> Materialized view log is one of the feature in oracle. It creates a
> log in which the changes made to the table are recorded. This log is
> required for an asynchronous materialized view that is refreshed
> incrementally.
>
> I read in the below link about incrementally refreshing the
> materialized view in postgresql:
>
> https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599
>
> Can someone let me how to do incremental refresh using Write Ahead Log

I note that bloggers sample code on github no longer exists.m   I
suspect it was half baked, and ran into intractable problems.

to do what you want, you would need to implement logical decoding [1] of
the WAL stream,  you would need to 'understand' the views completely so
you can tell if a given tuple update affects one of your views or not
(relatively simple for a view which is just `select fields from table
where simplecondition`, not so easy for a view which is a N way join
with complex filtering and/or aggregation, or whatever), then accumulate
these updates somewhere so your incremental refresh could replay them
and update the table underlying a given materialized view.

I'm sure i'm not thinking of major aspects complicating this.

[1]
https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html

--
john r pierce, recycling bits in santa cruz