On Thu, 27 Dec 2018 21:57:26 +0900 Yugo Nagata <nag...@sraoss.co.jp> wrote:
> Hi, > > I would like to implement Incremental View Maintenance (IVM) on PostgreSQL. I am now working on an initial patch for implementing IVM on PostgreSQL. This enables materialized views to be updated incrementally after one of their base tables is modified. At the first patch, I want to start from very simple features. Firstly, this will handle simple definition views which includes only selection, projection, and join. Standard aggregations (count, sum, avg, min, max) are not planned to be implemented in the first patch, but these are commonly used in materialized views, so I'll implement them later on. Views which include sub-query, outer-join, CTE, and window functions are also out of scope of the first patch. Also, views including self-join or views including other views in their definition is not considered well, either. I need more investigation on these type of views although I found some papers explaining how to handle sub-quries and outer-joins. Next, this will handle materialized views with no duplicates in their tuples. I am thinking of implementing an algorithm to handle duplicates called "counting-algorithm" afterward, but I'll start from this no-duplicates assumption in the first patch for simplicity. In the first patch, I will implement only "immediate maintenance", that is, materialized views are updated immediately in a transaction where a base table is modified. On other hand, in "deferred maintenance", materialized views are updated after the transaction, for example, by the user command like REFRESH. Although I plan to implement both eventually, I'll start from "immediate" because this seems to need smaller code than "deferred". For implementing "deferred", it is need to implement a mechanism to maintain logs for recording changes and an algorithm to compute the delta to be applied to materialized views are necessary. I plan to implement the immediate maintenance using AFTER triggers created automatically on a materialized view's base tables. In AFTER trigger using transition table features, changes occurs on base tables is recorded ephemeral relations. We can compute the delta to be applied to materialized views by using these ephemeral relations and the view definition query, then update the view by applying this delta. -- Yugo Nagata <nag...@sraoss.co.jp>