Re: Incrementally refreshed materialized view

Dear,

As it was recommended, we pushed our projects into github:
https://github.com/ntqvinh/PgMvIncrementalUpdate.

1) Synchronous incremental update
- For-each-row triggers are generated for all changing events on all
underlying tables.

2) Asynchronous (deferred) incremental update
- Triggers are generated for all changing events on all underlying tables
to collect all changed rows
- Other codes are generated for each matview-query to update the matview.

We hope that our projects may be helpful for someone!

TS. Nguyễn Trần Quốc Vinh
-----------------------------------------------
Chủ nhiệm khoa Tin học
Trường ĐH Sư phạm - ĐH Đà Nẵng
Website: http://it.ued.vn <http://it.ued.udn.vn>; http://www.ued.vn
<http://www.ued.udn.vn>; http://www.ued.udn.vn
LLKH: http://scv.ued.vn/~ntquocvinh <http://scv.ued.udn.vn/~ntquocvinh>
ĐT: 0511.6-512-586
DĐ: 0914.78-08-98
------------------------------------------------
Nguyen Tran Quoc Vinh, PhD
Dean
Faculty of Information Technology
Danang University of Education
Website: http://it.ued.udn.vn; http://www.ued.vn <http://www.ued.udn.vn/>;
http://www.ued.udn.vn
SCV: http://scv.ued.vn/~ntquocvinh <http://scv.ued.udn.vn/~ntquocvinh>
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98

On Tue, Sep 27, 2016 at 12:23 PM, hariprasath nallasamy <
hariprasathnallasamy(at)gmail(dot)com> wrote:

> We also tried to achieve incremental refresh of materialized view and our
> solution doesn't solve all of the use cases.
>
> Players:
> 1) WAL
> 2) Logical decoding
> 3) replication slots
> 4) custom background worker
>
> Two kinds of approaches :
> 1. Deferred refresh (oracle type of creating log table for each base
> tables with its PK and agg's columns old and new values)
> a) Log table for each base table has to be created and this log
> table will keep track of delta changes.
> b) UDF is called to refresh the view incrementally - this will
> run original materialized view query with the tracked delta PK's in their
> where clause. so only rows that are modified/inserted will be touched.
> c) Log table will keep track of changed rows from the data given by
> replication slot which uses logical decoding to decode from WAL.
> d) Shared memory is used to maintain the relationship between the
> view and its base table. In case of restart they are pushed to maintenance
> table.
>
> 2. RealTime refresh (update the view whenever we get any change-sets
> related to that base tables)
> a) Delta data from the replication slot will be applied to view by
> checking the relationship between our delta data and the view definiton.
> Here also shared memory and maintenance table are used.
> b) Work completed only for materialized views having single table.
>
> Main disadvantage :
> 1) Data inconsistency when master failure and also slave doesn't have
> replication slot as of now. But 2ndquard guys try to create slots in slave
> using some concepts of failover slots. But that doesn't come along with PG
> :(.
> 2) Sum, count and avg are implemented for aggregates(single table) and for
> other aggs full refresh comes to play a role.
> 3) Right join implementation requires more queries to run on the top of
> MV's.
>
> So we are on a long way to go and dono whether this is the right path.
>
> Only deferred refresh was pushed to github.
> https://github.com/harry-2016/MV_IncrementalRefresh
>
> I wrote a post regarding that in medium.
> https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-
> incremental-refresh-44d1ca742599
>
>
>