Incremental refresh - Materialized view

On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:

I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized
view in postgresql 9.5.9 version.

Is there anything similar to materialized view log in postgresql.

you refresh a materialized view with REFRESH MATERIALIZED VIEW name;   
There's no 'incremental' methods, as views can be quite complex.

I do not know what you mean by 'materialized view log', is this a
feature of some other database server ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Materialized view log is one of the feature in oracle. It creates a log in
which the changes made to the table are recorded. This log is required for
an asynchronous materialized view that is refreshed incrementally.

I read in the below link about incrementally refreshing the materialized
view in postgresql:

https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599

Can someone let me how to do incremental refresh using Write Ahead Log

Thanks,
Krithika

On 07-Nov-2017 12:37 PM, "John R Pierce" <pierce@hogranch.com> wrote:

On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:

I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized view
in postgresql 9.5.9 version.

Is there anything similar to materialized view log in postgresql.

you refresh a materialized view with REFRESH MATERIALIZED VIEW name;
There's no 'incremental' methods, as views can be quite complex.

I do not know what you mean by 'materialized view log', is this a feature
of some other database server ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Krithika Venkatesh wrote:

I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized view in postgresql 9.5.9 version.

Is there anything similar to materialized view log in postgresql.

There is no such feature in PostgreSQL (yet), so you'll have to
do it yourself.

The "materialized view" would then be a regular table (with read only
access), and each underlying table would have a trigger that records
changes with a timestamp to a log table.

You can then write a function that brings the "materialized view"
up to date.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

You have already been informed. PG, as yet, does not allow incremental refresh of a MV. It allows online refresh of a MV, but that it does by doing a full table scan of the base table and rebuilding the MV.

________________________________
From: Krithika Venkatesh <krithikavenkatesh31@gmail.com>
To: John R Pierce <pierce@hogranch.com>
Cc: pgsql-general@postgresql.org
Sent: Tuesday, November 7, 2017 2:35 AM
Subject: Re: [GENERAL] Incremental refresh - Materialized view

Materialized view log is one of the feature in oracle. It creates a log in which the changes made to the table are recorded. This log is required for an asynchronous materialized view that is refreshed incrementally.

I read in the below link about incrementally refreshing the materialized view in postgresql:

https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599

Can someone let me how to do incremental refresh using Write Ahead Log

Thanks,
Krithika

On 07-Nov-2017 12:37 PM, "John R Pierce" <pierce@hogranch.com<mailto:pierce@hogranch.com>> wrote:
On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:
I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized view in postgresql 9.5.9 version.

Is there anything similar to materialized view log in postgresql.

you refresh a materialized view with REFRESH MATERIALIZED VIEW name; There's no 'incremental' methods, as views can be quite complex.

I do not know what you mean by 'materialized view log', is this a feature of some other database server ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mail pref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general&gt;

On Tue, Nov 7, 2017 at 7:08 AM, Rakesh Kumar <rakeshkumar464@outlook.com>
wrote:

You have already been informed. PG, as yet, does not allow incremental
refresh of a MV. It allows online refresh of a MV, but that it does by
doing a full table scan of the base table and rebuilding the MV.

------------------------------
*From:* Krithika Venkatesh <krithikavenkatesh31@gmail.com>
*To:* John R Pierce <pierce@hogranch.com>
*Cc:* pgsql-general@postgresql.org
*Sent:* Tuesday, November 7, 2017 2:35 AM
*Subject:* Re: [GENERAL] Incremental refresh - Materialized view

Materialized view log is one of the feature in oracle. It creates a log in
which the changes made to the table are recorded. This log is required for
an asynchronous materialized view that is refreshed incrementally.

I read in the below link about incrementally refreshing the materialized
view in postgresql:

https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-
incremental-refresh-44d1ca742599

Can someone let me how to do incremental refresh using Write Ahead Log

Thanks,
Krithika

On 07-Nov-2017 12:37 PM, "John R Pierce" <pierce@hogranch.com> wrote:

On 11/6/2017 10:38 PM, Krithika Venkatesh wrote:

I need to implement incremental refresh of materialized view.

Please let me know how to do the incremental refresh of materialized view
in postgresql 9.5.9 version.

Is there anything similar to materialized view log in postgresql.

you refresh a materialized view with REFRESH MATERIALIZED VIEW name;
There's no 'incremental' methods, as views can be quite complex.

I do not know what you mean by 'materialized view log', is this a feature
of some other database server ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mail pref/pgsql-general
<http://www.postgresql.org/mailpref/pgsql-general&gt;

Please let me know how to do the incremental refresh of materialized view

in postgresql 9.5.9 version.

As previously stated, there is currently no such thing as an incremental
refresh of a materialized view.
I believe what you are looking for is:

*REFRESH MATERIALIZED VIEW CONCURRENTLY mat_view_name;*

REFRESH MATERIALIZED VIEW
<https://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html&gt;

Specifying CONCURRENTLY with prevent locking of the underlying table(s),
but will extend the
time it takes to complete the refresh.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 11/6/2017 11:34 PM, Krithika Venkatesh wrote:

Materialized view log is one of the feature in oracle. It creates a
log in which the changes made to the table are recorded. This log is
required for an asynchronous materialized view that is refreshed
incrementally.

I read in the below link about incrementally refreshing the
materialized view in postgresql:

https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599

Can someone let me how to do incremental refresh using Write Ahead Log

I note that bloggers sample code on github no longer exists.m   I
suspect it was half baked, and ran into intractable problems.

to do what you want, you would need to implement logical decoding [1]https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html of
the WAL stream,  you would need to 'understand' the views completely so
you can tell if a given tuple update affects one of your views or not
(relatively simple for a view which is just `select fields from table
where simplecondition`, not so easy for a view which is a N way join
with complex filtering and/or aggregation, or whatever), then accumulate
these updates somewhere so your incremental refresh could replay them
and update the table underlying a given materialized view.

I'm sure i'm not thinking of major aspects complicating this.

[1]: https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html
https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dear all,

We have some result on incremental update for MVs. We generate triggers in
C to do the incremental maintenance. We posted the code to github about 1
year ago, but unfortunately i posted a not-right version of ctrigger.h
header. The mistake was exposed to me when a person could not compile the
generated triggers and reported to me. And now i re-posted with the right
ctrigger.h file.

You can find the codes of the generator here:
https://github.com/ntqvinh/PgMvIncrementalUpdate/commits/master. You can
find how did we do here:
https://link.springer.com/article/10.1134/S0361768816050066. The paper is
about generating of codes in pl/pgsql. Anyway i see it is useful for
reading the codes. I don't know if i can share the paper or not so that i
don't publish anywhere else. The text about how to generate triggers in C
was published with open-access but unfortunately, it is in Vietnamese.

We are happy if the codes are useful for someone.

Thank you and best regards,

NTQ Vinh

--
TS. Nguyễn Trần Quốc Vinh
-----------------------------------------------
Chủ nhiệm khoa Tin học
Trường ĐH Sư phạm - ĐH Đà Nẵng
------------------------------------------------
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/&gt;;
http://www.ued.udn.vn
SCV: http://scv.ued.vn/~ntquocvinh <http://scv.ued.udn.vn/~ntquocvinh&gt;
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98