Re: Incrementally refreshed materialized view

Dear Adam.

There are read-me files within each projects. They support only inner-joins
and aggregations (sum, count, min, max, avg). The updating algorithm for
matviews with min/max must be differ from without ones. When there are min
and/or max, we have to use the base table, otherwise, not.

About 15 years ago we implemented for SPJ matvew-queries to generate
triggers in PL/pgSQL. We developed that project for queries with
aggregations about 4 years ago. Unfortunately we lost the last versions. We
have now only the version with some error when there are aggregations. For
SPJ queries it works well. We don't know is it useful or not. If yes, we
can upload for sharing. We didn't share it because we thought that our work
is not good enough for public use.

The projects that generate C-codes was "finished" 2 years ago.

Concern the project that generates triggers (doing incremental update) in
PL/pgSQL, we implemented the algorithm likes your suggestion in this
thread, i.e. at least one key of the base tables are added automatically
into the mat-view queries for further incremental updates.

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 Thu, Sep 29, 2016 at 8:37 PM, Adam Brusselback <adambrusselback(at)gmail(dot)com
> wrote:

> On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh <
> ntquocvinh(at)gmail(dot)com> wrote:
>
>> 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!
>>
>
> Very interesting. Does this support materialized views with recursive
> queries? What about left joins? (not) exists? Aggregates? Window functions?
> In reading up on the implementations in other databases, I was surprised by
> some of the limitations imposed by DB2 / Oracle / Sql Server.
>
> I'm trying to look through the code base to answer my questions, but it's
> large enough that it may be easier to just ask first.
>