Re: fast refresh materialized view - Mailing list pgsql-general
| From | Nguyễn Trần Quốc Vinh |
|---|---|
| Subject | Re: fast refresh materialized view |
| Date | |
| Msg-id | CAAU9oxsB5gy8LZ12kqaa3r1iv19C7faMNeFixDAC1FhRFYThyg@mail.gmail.com Whole thread Raw |
| In response to | Re: fast refresh materialized view (Michael Paquier <michael.paquier@gmail.com>) |
| Responses |
Re: fast refresh materialized view
(Jim Nasby <Jim.Nasby@BlueTreble.com>)
|
| List | pgsql-general |
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
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98
On Sun, Nov 15, 2015 at 6:36 PM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Sat, Nov 14, 2015 at 7:01 AM, Pradhan, Sabin <Sabin.Pradhan@finra.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Attachment
pgsql-general by date: