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_
<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 <ntquocvinh(at)gmail(dot)com>
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 <
> 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
>>>
>>
>>
>