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