Re: [GENERAL] 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 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 < > adambrusselb...@gmail.com> wrote: > >> On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh < >> ntquocv...@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. >> > >
Re: [GENERAL] 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 wrote: > On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh < > ntquocv...@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. >
Re: [GENERAL] Incrementally refreshed materialized view
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! 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 Tue, Sep 27, 2016 at 12:23 PM, hariprasath nallasamy < hariprasathnallas...@gmail.com> wrote: > We also tried to achieve incremental refresh of materialized view and our > solution doesn't solve all of the use cases. > > Players: > 1) WAL > 2) Logical decoding > 3) replication slots > 4) custom background worker > > Two kinds of approaches : > 1. Deferred refresh (oracle type of creating log table for each base > tables with its PK and agg's columns old and new values) > a) Log table for each base table has to be created and this log > table will keep track of delta changes. > b) UDF is called to refresh the view incrementally - this will > run original materialized view query with the tracked delta PK's in their > where clause. so only rows that are modified/inserted will be touched. > c) Log table will keep track of changed rows from the data given by > replication slot which uses logical decoding to decode from WAL. > d) Shared memory is used to maintain the relationship between the > view and its base table. In case of restart they are pushed to maintenance > table. > > 2. RealTime refresh (update the view whenever we get any change-sets > related to that base tables) > a) Delta data from the replication slot will be applied to view by > checking the relationship between our delta data and the view definiton. > Here also shared memory and maintenance table are used. > b) Work completed only for materialized views having single table. > > Main disadvantage : > 1) Data inconsistency when master failure and also slave doesn't have > replication slot as of now. But 2ndquard guys try to create slots in slave > using some concepts of failover slots. But that doesn't come along with PG > :(. > 2) Sum, count and avg are implemented for aggregates(single table) and for > other aggs full refresh comes to play a role. > 3) Right join implementation requires more queries to run on the top of > MV's. > > So we are on a long way to go and dono whether this is the right path. > > Only deferred refresh was pushed to github. > https://github.com/harry-2016/MV_IncrementalRefresh > > I wrote a post regarding that in medium. > https://medium.com/@hariprasathnallsamy/postgresql-materialized-view- > incremental-refresh-44d1ca742599 > > >
Re: [GENERAL] Materialized view auto refresh
Dear Harry. I'm sorry. Please check it again at http://it.ued.udn.vn/myprojects/pgTriggerGen/: http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator_v2_src.rar . Best regards, On Tue, Sep 6, 2016 at 2:42 PM, hari.prasath wrote: > Dear Nguyen Tran Quoc Vinh > Source link is broken. Please check this http://it.ued.udn.vn/ > myprojects/pgTriggerGen/PgSQLMVTriggersGenerator_src.rar > > cheers > *- *Harry > > > On Mon, 05 Sep 2016 11:50:49 +0530 *Nguyễn Trần Quốc Vinh > >*wrote > > Dear Harry. > > You can try this while you are waiting the better solution from Prof. > Kevin Grittner. We did not improve because we do not know if we are in the > right way... > > https://www.postgresql.org/message-id/CAAU9oxsB5gy8LZ12kqaa3r1iv19C7 > famnefixdac1fhrfyt...@mail.gmail.com > > You can downloat the source and the binary from here http://it.ued.udn.vn/ > myprojects/pgTriggerGen/. > > Best regards, > > 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.udn.vn; http://www.ued.udn.vn; http:// > www.ued.udn.vn > LLKH: 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.udn.vn/; http: > //www.ued.udn.vn > SCV: http://scv.ued.udn.vn/~ntquocvinh > Phone: (+84) 511.6-512-586 > Mobile: (+84) 914.78-08-98 > > On Tue, Aug 9, 2016 at 8:49 PM, Kevin Grittner wrote: > > On Tue, Aug 9, 2016 at 4:50 AM, hari.prasath > wrote: > > > Is there any tentative schedule for real-time or incremental(only > > applying delta changes) refresh of materialized views.?. > > There is work in progress, but no hard schedule. Unfortunately, it > has often been set aside to address more immediate issues for > particular end users; but I expect to get back to it Real Soon Now. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > >
Re: [GENERAL] Materialized view auto refresh
Dear Harry. You can try this while you are waiting the better solution from Prof. Kevin Grittner. We did not improve because we do not know if we are in the right way... https://www.postgresql.org/message-id/caau9oxsb5gy8lz12kqaa3r1iv19c7famnefixdac1fhrfyt...@mail.gmail.com You can downloat the source and the binary from here http://it.ued.udn.vn/myprojects/pgTriggerGen/. Best regards, 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 Tue, Aug 9, 2016 at 8:49 PM, Kevin Grittner wrote: > On Tue, Aug 9, 2016 at 4:50 AM, hari.prasath > wrote: > > > Is there any tentative schedule for real-time or incremental(only > > applying delta changes) refresh of materialized views.?. > > There is work in progress, but no hard schedule. Unfortunately, it > has often been set aside to address more immediate issues for > particular end users; but I expect to get back to it Real Soon Now. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] fast refresh materialized view
We would like to contribute to the PostgreSQL community. What can you recommend us? Thank you. 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 Tue, Feb 9, 2016 at 7:51 PM, Michael Paquier wrote: > On Tue, Feb 9, 2016 at 12:32 AM, Nguyễn Trần Quốc Vinh > wrote: > > > > Thank you very much. We did n't think about that. We would like to choose > > APACHE LICENSE. We apologize for late reply. > > And that would be incompatible with the PostgreSQL license I guess, > per concerns with patents and similar stuff. > -- > Michael >
Re: [GENERAL] fast refresh materialized view
Thank you very much. We did n't think about that. We would like to choose APACHE LICENSE. We apologize for late reply. We would like to get discussion of the experts. Thanks and best regards, 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 Fri, Dec 4, 2015 at 7:09 AM, Jim Nasby wrote: > On 12/3/15 4:17 AM, Nguyễn Trần Quốc Vinh wrote: > >> We have build a tool that generates all triggers in C-language for all >> data-changing events on all tables underlying upon query. The generated >> triggers do synchronous incremental updates for MV. >> > > Awesome! The hope is to eventually support this internally, and maybe your > work will help that. What license is it released under? > -- > Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX > Experts in Analytics, Data Architecture and PostgreSQL > Data in Trouble? Get it in Treble! http://BlueTreble.com >
Re: [GENERAL] fast refresh materialized view
Dear Sir. I'm sorry. The attachment was too big. You can find the source code and the binary at *http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator_v4._a_ <http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator_v4._a_>* . Best regards. 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, Dec 3, 2015 at 5:39 PM, Nguyễn Trần Quốc Vinh wrote: > Dear Sir. > > I'm sorry. The attachment was too big. You can find the source code and > the binary at > http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator > v4._a_. > > Best regards. > > > On Thu, Dec 3, 2015 at 5:17 PM, Nguyễn Trần Quốc Vinh < > ntquocv...@gmail.com> wrote: > >> Dear Sirs. >> >> We have build a tool that generates all triggers in C-language for all >> data-changing events on all tables underlying upon query. The generated >> triggers do synchronous incremental updates for MV. Although the feature of >> synchronous incremental update integrated into the PostgreSQL source code >> may be more optimal plan. But the solution with triggers may have its >> benefit because of its relative independence from versions of the DBMS. >> >> The tool is written in C in Windows environment. I'm trying to adapt to >> the Linux environment, but i can not finish in the short time. >> >> The matview can be created by any queries with restrictions: >> - no recursive; >> - no having; >> - no sub-queries; >> - inner join; >> - aggregate functions: COUNT, CUM, AVG, MIN, MAX. >> >> The current version of program can work only with 32bit PostgreSQL, but >> the generated triggers can be built for both 32bit and 64bit versions, >> depending on the version of libs are included during compiling. The program >> is not implemented all of our algorithm. We have to do also some >> optimization. >> >> You can find the run.bat in the release\example. >> o11dw-OK4-lowercase.backup file is the backup of the database transformed >> from oracle 11g sample database. The query accompanied with the example is >> designed for that database. It requests the local PostgreSQL instance >> running at port 5432. >> >> We use Visual Studio 2013 for building the generator. You can find the >> project within the .rar attached too. It is configured for PostgreSQL 9.3 >> 32bit. You can change the configuration as you want related to the >> platform, include folder and library folder. Please, don't forget to >> install Visual Leak Detector and set the project configuration for it too. >> >> The incremental update algorithm that is implemented within the generated >> triggers is based on the published academic papers with my improvement and >> adapting to be implemented with PostgreSQL triggers. I will send you at >> least the general steps of the algorithm in the near future. My English is >> not very good, so i need many time to figure out the content with high >> complexity. I'm looking forward for understanding from you. >> >> I hope our solution will bring benefits to you. We are willing to all the >> recommendations and the cooperation to improve the tool. >> >> Thank you and best regards, >> >> >> 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 Sun, Nov 15, 2015 at 6:36 PM, Michael Paquier < >> michael.paqu...@gmail.com> wrote: >> >>> On Sat, Nov 14, 2015 at 7:01 AM, Pradhan, Sabin >>> wrote: >>> > Does postgres has fast refresh materialized view that supports >>> incremental >>
Re: [GENERAL] fast refresh materialized view
Dear Sir. I'm sorry. The attachment was too big. You can find the source code and the binary at http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator v4._a_. Best regards. On Thu, Dec 3, 2015 at 5:17 PM, Nguyễn Trần Quốc Vinh wrote: > Dear Sirs. > > We have build a tool that generates all triggers in C-language for all > data-changing events on all tables underlying upon query. The generated > triggers do synchronous incremental updates for MV. Although the feature of > synchronous incremental update integrated into the PostgreSQL source code > may be more optimal plan. But the solution with triggers may have its > benefit because of its relative independence from versions of the DBMS. > > The tool is written in C in Windows environment. I'm trying to adapt to > the Linux environment, but i can not finish in the short time. > > The matview can be created by any queries with restrictions: > - no recursive; > - no having; > - no sub-queries; > - inner join; > - aggregate functions: COUNT, CUM, AVG, MIN, MAX. > > The current version of program can work only with 32bit PostgreSQL, but > the generated triggers can be built for both 32bit and 64bit versions, > depending on the version of libs are included during compiling. The program > is not implemented all of our algorithm. We have to do also some > optimization. > > You can find the run.bat in the release\example. > o11dw-OK4-lowercase.backup file is the backup of the database transformed > from oracle 11g sample database. The query accompanied with the example is > designed for that database. It requests the local PostgreSQL instance > running at port 5432. > > We use Visual Studio 2013 for building the generator. You can find the > project within the .rar attached too. It is configured for PostgreSQL 9.3 > 32bit. You can change the configuration as you want related to the > platform, include folder and library folder. Please, don't forget to > install Visual Leak Detector and set the project configuration for it too. > > The incremental update algorithm that is implemented within the generated > triggers is based on the published academic papers with my improvement and > adapting to be implemented with PostgreSQL triggers. I will send you at > least the general steps of the algorithm in the near future. My English is > not very good, so i need many time to figure out the content with high > complexity. I'm looking forward for understanding from you. > > I hope our solution will bring benefits to you. We are willing to all the > recommendations and the cooperation to improve the tool. > > Thank you and best regards, > > > 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 Sun, Nov 15, 2015 at 6:36 PM, Michael Paquier < > michael.paqu...@gmail.com> wrote: > >> On Sat, Nov 14, 2015 at 7:01 AM, Pradhan, Sabin >> wrote: >> > Does postgres has fast refresh materialized view that supports >> incremental >> > refresh. In oracle , this is achieve by materialized view log. Not >> sure >> > how to implement it in postgres. >> >> No. Postgres offers just the possibility to refresh materialized views >> while taking a lock on it that allows reads to continue running on it >> WITH REFRESH MATERIALIZED VIEW CONCURRENTLY. A necessary condition is >> that a UNIQUE index needs to be created on it. >> >> > Confidentiality Notice:: This email, including attachments, may include >> > non-public, proprietary, confidential or legally privileged >> information. If >> > you are not an intended recipient or an authorized agent of an intended >> > recipient, you are hereby notified that any dissemination, distribution >> or >> > copying of the information contained in or transmitted with this e-mail >> is >> > unauthorized and strictly prohibited. If you have received this email in >> > error, please notify the sender by replying to this message and >> permanently >> > delete this e-mail, its attachments, and any copies of it immediately. >> You >> > should not retain, copy or use this e-mail or any attachment for any >> > purpose, nor disclose all or any part of the contents to any other >> person. >> >> This is a public mailing list. >> -- >> Michael >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
[GENERAL] Incremental update for matview - automatic trigger-in-c generator
Dear sir/madam. We build a program that can automatically generate triggers in C-language for synchronous incremental matview update. It supports queries with limitations: - inner join - group by with sum, count, avg, min, max - the join has to be of the form: t1 1-n t2 1-n t3 1-n t4 The binary and source can be find at http://it.ued.vn/myprojects/pgTriggerGen/. It is the first version. We now are build another version with some improvements. Anyone can use it if they find it useful. All comments, recommendations, improvements are welcome. Corresponding address: ntquocv...@ued.vn. Thank you for attentions.
