Re: Incrementally refreshed materialized view

We came to C and discontinued the version generating code in
PL/pgSQL because of
- Our testing showed that triggers in C give better performance than the
ones in PL/pgSQL. Our opinion may be wrong.
- If we can generate triggers and other updating codes in C, we may
integrate it into PostgreSQL source codes. We may be wrong too.

:)

We plan to do by the same way for queries with outer-joins next year. With
recursive queries we have no plan because we don't see any effective update
algorithm. The worst is that we have no fund to do with matviews and
PostgreSQL. All that just for fun! We have too many things to do each day.

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:56 PM, Nguyễn Trần Quốc Vinh <ntquocvinh(at)gmail(dot)com
> wrote:

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