Re: fast refresh materialized view

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
>