Re: 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 <ntquocvinh(at)gmail(dot)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(dot)paquier(at)gmail(dot)com> wrote:
>
>> On Sat, Nov 14, 2015 at 7:01 AM, Pradhan, Sabin <Sabin(dot)Pradhan(at)finra(dot)org>
>> 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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>