Re: Incremental refresh - Materialized view

On Tue, Nov 7, 2017 at 7:08 AM, Rakesh Kumar <rakeshkumar464(at)outlook(dot)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(at)gmail(dot)com>
> *To:* John R Pierce <pierce(at)hogranch(dot)com>
> *Cc:* pgsql-general(at)postgresql(dot)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(at)hogranch(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mail pref/pgsql-general
> <http://www.postgresql.org/mailpref/pgsql-general>
>
>
>
>
>
>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>

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.