Re: [GENERAL] 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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general