Re: [GENERAL] fast refresh materialized view

[Michael Paquier]

On Fri, Feb 12, 2016 at 4:29 AM, Oleg Bartunov  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

Re: [GENERAL] fast refresh materialized view

[Michael Paquier]

On Fri, Feb 12, 2016 at 1:56 AM, Nguyễn Trần Quốc Vinh
 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

Re: [GENERAL] fast refresh materialized view

[Oleg Bartunov]

Похоже на то, что вы понимаете по-русски !

Сообщество примет вашу работу только под лицензией BSD. Если что непонятно,
пишите мне.

С Уважением,
Олег

On Thu, Feb 11, 2016 at 7:56 PM, Nguyễn Trần Quốc Vinh  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://www.ued.vn
> ; http://www.ued.udn.vn
> LLKH: http://scv.ued.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
> SCV: http://scv.ued.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  > wrote:
>
>> On Tue, Feb 9, 2016 at 12:32 AM, Nguyễn Trần Quốc Vinh
>>  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
>>
>
>

Re: [GENERAL] fast refresh materialized view

[Nguyễn Trần Quốc Vinh]

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://www.ued.vn
; http://www.ued.udn.vn
LLKH: http://scv.ued.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
SCV: http://scv.ued.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 
wrote:

> On Tue, Feb 9, 2016 at 12:32 AM, Nguyễn Trần Quốc Vinh
>  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
>

Re: [GENERAL] fast refresh materialized view

[Michael Paquier]

On Tue, Feb 9, 2016 at 12:32 AM, Nguyễn Trần Quốc Vinh
 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

Re: [GENERAL] fast refresh materialized view

[Nguyễn Trần Quốc Vinh]

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://www.ued.vn
; http://www.ued.udn.vn
LLKH: http://scv.ued.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
SCV: http://scv.ued.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  wrote:

> 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
>

Re: [GENERAL] fast refresh materialized view

[Jim Nasby]

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

Re: [GENERAL] fast refresh materialized view

[Nguyễn Trần Quốc Vinh]

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.



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://www.ued.vn
; http://www.ued.udn.vn
LLKH: http://scv.ued.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
SCV: http://scv.ued.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 
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 <
> ntquocv...@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
>> SCV: http://scv.ued.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.paqu...@gmail.com> wrote:
>>
>>> On Sat, Nov 14, 2015 at 7:01 AM, Pradhan, Sabin 
>>> 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
>>> informati

Re: [GENERAL] fast refresh materialized view

[Nguyễn Trần Quốc Vinh]

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 
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
> SCV: http://scv.ued.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.paqu...@gmail.com> wrote:
>
>> On Sat, Nov 14, 2015 at 7:01 AM, Pradhan, Sabin 
>> 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
>>
>
>

Re: [GENERAL] fast refresh materialized view

[Michael Paquier]

On Sat, Nov 14, 2015 at 7:01 AM, Pradhan, Sabin  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

Re: [GENERAL] fast refresh materialized view

[Kevin Grittner]

On Friday, November 13, 2015 4:02 PM, "Pradhan, Sabin" 
 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

[GENERAL] fast refresh materialized view

[Pradhan, Sabin]

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.