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