Thread: [GENERAL] Incremental refresh - Materialized view

On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:
> I need to implement incremental refresh of materialized view.
>
> Please let me know how to do the incremental refresh of materialized 
> view in postgresql 9.5.9 version.
>
> Is there anything similar to materialized view log in postgresql.


you refresh a materialized view with REFRESH MATERIALIZED VIEW name;    
There's no 'incremental' methods, as views can be quite complex.

I do not know what you mean by 'materialized view log', is this a 
feature of some other database server ?



-- 
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

Krithika Venkatesh wrote:
> I need to implement incremental refresh of materialized view.
> 
> Please let me know how to do the incremental refresh of materialized view in postgresql 9.5.9 version. 
> 
> Is there anything similar to materialized view log in postgresql.

There is no such feature in PostgreSQL (yet), so you'll have to
do it yourself.

The "materialized view" would then be a regular table (with read only
access), and each underlying table would have a trigger that records
changes with a timestamp to a log table.

You can then write a function that brings the "materialized view"
up to date.

Yours,
Laurenz Albe


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

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