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
Đ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:56 PM, Nguyễn Trần Quốc Vinh <ntquocvinh@xxxxxxxxx> 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ọcTrường ĐH Sư phạm - ĐH Đà NẵngĐT: 0511.6-512-586DĐ: 0914.78-08-98------------------------------------------------ Nguyen Tran Quoc Vinh, PhDDeanFaculty of Information TechnologyDanang University of EducationPhone: (+84) 511.6-512-586Mobile: (+84) 914.78-08-98On Thu, Sep 29, 2016 at 8:37 PM, Adam Brusselback <adambrusselback@xxxxxxxxx> wrote:On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh <ntquocvinh@xxxxxxxxx> 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.
