Re: Incrementally refreshed materialized view - Mailing list pgsql-general
| From | Nguyễn Trần Quốc Vinh |
|---|---|
| Subject | Re: Incrementally refreshed materialized view |
| Date | |
| Msg-id | CAAU9oxvRMiHgHe=Y_GXvM6FDqkOQcFPesGcUuXgDtW=XU-2Y9A@mail.gmail.com Whole thread Raw |
| In response to | Re: Incrementally refreshed materialized view (Adam Brusselback <adambrusselback@gmail.com>) |
| Responses |
Re: Incrementally refreshed materialized view
(Nguyễn Trần Quốc Vinh <ntquocvinh@gmail.com>)
|
| List | pgsql-general |
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
ĐT: 0511.6-512-586
DĐ: 0914.78-08-98
------------------------------------------------
Nguyen Tran Quoc Vinh, PhD
Dean
Faculty of Information Technology
Danang University of Education
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98
On Thu, Sep 29, 2016 at 8:37 PM, Adam Brusselback <adambrusselback@gmail.com> wrote:
On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh <ntquocvinh@gmail.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.
pgsql-general by date: