From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Incremental refresh - Materialized view |
Date: | 2017-11-07 18:07:40 |
Message-ID: | 1068317d-dc30-d9bc-1f2f-c8ea841f99dc@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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] 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
--
john r pierce, recycling bits in santa cruz
From | Date | Subject | |
---|---|---|---|
Next Message | chris kim | 2017-11-07 20:04:18 | standby stop replicating, then picked back up |
Previous Message | pinker | 2017-11-07 17:24:15 | Re: Block duplications in a shared buffers |