We have some result on incremental update for MVs. We generate triggers on C to do the incremental maintenance. We posted the code to github about 1 year ago, but unfortunately i posted a not-right ctrigger.h header. The mistake was exposed to me when a person could not compile the generated triggers and reported to me. And now i re-posted with the right ctrigger.h file.
You can find the codes of the generator here: https://github.com/ntqvinh/PgMvIncrementalUpdate/commits/master. You can find how did we do here: https://link.springer.com/article/10.1134/S0361768816050066. The paper is about generating of codes in pl/pgsql. Anyway i see it is useful for reading the codes. I don't know if i can share the paper or not so that i don't publish anywhere else. The text about how to generate triggers in C was published with open-access but unfortunately, it is in Vietnamese.
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).
