fast refresh materialized view
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.
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. Thank you.
On Friday, November 13, 2015 4:02 PM, "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.
There has been work toward that end, but for now the best you can
do is REFRESH MATERIALIZED VIEW CONCURRENTLY. This still
materializes the full view in a temp table, but essentially "diffs"
that against the existing data to apply an incremental change set.
The can be done concurrently with reads against the materialized
view and with reads and writes against the source tables.
We are probably at least two years from having what you describe.
--
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
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
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.paquier@gmail.com>
wrote:
Show quoted text
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, distributionor
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 andpermanently
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 otherperson.
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
Attachments:
PgSQLMVTriggersGenerator v4._a_application/octet-stream; name="PgSQLMVTriggersGenerator v4._a_"Download+129-128
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@gmail.com>
wrote:
Show quoted text
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-98On 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 privilegedinformation. If
you are not an intended recipient or an authorized agent of an intended
recipient, you are hereby notified that any dissemination, distributionor
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 andpermanently
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 otherperson.
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
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@gmail.com>
wrote:
Show quoted text
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@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-98On 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 privilegedinformation. 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 otherperson.
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 <Jim.Nasby@bluetreble.com> wrote:
Show quoted text
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
On Tue, Feb 9, 2016 at 12:32 AM, Nguyễn Trần Quốc Vinh
<ntquocvinh@gmail.com> 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 <michael.paquier@gmail.com>
wrote:
Show quoted text
On Tue, Feb 9, 2016 at 12:32 AM, Nguyễn Trần Quốc Vinh
<ntquocvinh@gmail.com> 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
Похоже на то, что вы понимаете по-русски !
Сообщество примет вашу работу только под лицензией BSD. Если что непонятно,
пишите мне.
С Уважением,
Олег
On Thu, Feb 11, 2016 at 7:56 PM, Nguyễn Trần Quốc Vinh <ntquocvinh@gmail.com
Show quoted text
wrote:
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-98On Tue, Feb 9, 2016 at 7:51 PM, Michael Paquier <michael.paquier@gmail.com
wrote:
On Tue, Feb 9, 2016 at 12:32 AM, Nguyễn Trần Quốc Vinh
<ntquocvinh@gmail.com> 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
On Fri, Feb 12, 2016 at 1:56 AM, Nguyễn Trần Quốc Vinh
<ntquocvinh@gmail.com> wrote:
We would like to contribute to the PostgreSQL community. What can you
recommend us?
If you are planning to send patches for integration into core
Postgres, this code needs to be available under a license that is
compatible with the PostgreSQL license. If you plan to maintain that
as an independent project that would never be integrated with Postgres
directly, the license does not really matter, but it would prevent
other people to do the integration legwork into core if having your
feature makes sense.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Feb 12, 2016 at 4:29 AM, Oleg Bartunov <obartunov@gmail.com> wrote:
Похоже на то, что вы понимаете по-русски !
I don't. My bad.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general