Re: Implementing Incremental View Maintenance

Dear All,

The tool analyzes the input query and then generates triggers (trigger
functions and pl/pgsql scripts as well) on all manipulating events
(insert/updates/delete) for all underlying base tables. The triggers do
incremental updates to the table that contains the query result (MV). You
can build the tool, then see the provided example and try the tool. It is
for synchronous maintenance. It was hard tested but you can use it with
your own risk.

For Asynchronous maintenance, we generate 1) triggers on all manipulating
events on base tables to collect all the data changes and save to the
'special' tables; then 2) the tool to do incremental updates of MVs.

Best regards,

Vinh

TS. Nguyễn Trần Quốc Vinh
-----------------------------------------------
Chủ nhiệm khoa Tin học
Trường ĐH Sư phạm - ĐH Đà Nẵng
------------------------------------------------
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 Mon, Jan 7, 2019 at 9:00 AM Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp> wrote:

> > Hi all, just wanted to say I am very happy to see progress made on this,
> > my codebase has multiple "materialized tables" which are maintained with
> > statement triggers (transition tables) and custom functions. They are
> ugly
> > and a pain to maintain, but they work because I have no other
> > solution...for now at least.
> >
> > I am concerned that the eager approach only addresses a subset of the MV
> use
> >> case space, though. For example, if we presume that an MV is present
> >> because
> >> the underlying direct query would be non-performant, then we have to at
> >> least question whether applying the delta-update would also be
> detrimental
> >> to some use cases.
> >>
> >
> > I will say that in my case, as long as my reads of the materialized view
> > are always consistent with the underlying data, that's what's
> important. I
> > don't mind if it's eager, or lazy (as long as lazy still means it will
> > refresh prior to reading).
>
> Assuming that we want to implement IVM incrementally (that means, for
> example, we implement DELETE for IVM in PostgreSQL XX, then INSERT for
> IVM for PostgreSQL XX+1... etc.), I think it's hard to do it with an
> eager approach if want to MV is always consistent with base tables.
>
> On the other hand, a lazy approach allows to implement IVM
> incrementally because we could always let full MV build from scratch
> if operations on MV include queries we do not support.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
>