Re: Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 1:22 PM, Melvin Davidson <melvin6925(at)gmail(dot)com> wrote:
> On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback <adambrusselback(at)gmail(dot)com> wrote:
>>
>> I am working on a plan to implement incrementally refreshed
>> materialized "views" with the existing functionality in
>> Postgres.
>>
>> Below is the plan for doing that:

>> [design from scratch, incomplete]

>> I am however stuck on: How do we know what to refresh?

>> Pretty much, I need to figure out how to follow the joins in the
>> view back to whatever key was defined as the "refresh key" for
>> each dependent table. I know about the
>> information_schema.view_column_usage, but I don't think that'll
>> get me everything I need.
>>
>> I'd really appreciate any help with this, as i'd love a better
>> way to get eagerly refreshed materialized views in Postgres
>> rather than doing everything manually as I have to now.
>>
>> If I can provide any more info please let me know.

> I am a bit curious. Why are you reinventing the wheel?
> What is wrong with:
>
> REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
> [ WITH [ NO ] DATA ]
>
> https://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html
>
> Can't you do that in a cron job?

Well, that is not *incremental* maintenance -- the entire query is
executed, with the resulting relation either replacing the previous
contents of the matview or "diffed" against the previous contents
(so that the difference can be applied with transactional
semantics), depending on whether CONCURRENTLY was specified.

The OP is still reinventing the wheel though. A summary of
available techniques as of the mid-90s can be found here:

http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.40.2254&rep=rep1&type=pdf

With some detail for what to me look like the two most promising
techniques here:

http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.31.3208&rep=rep1&type=pdf

The first step in using either of those techniques (counting or
DRed) is to capture a delta relation to feed into the relational
algebra used by these techniques. As a first step in that
direction I have been floating a patch to implement the
SQL-standard "transition tables" feature for AFTER triggers.

https://commitfest.postgresql.org/10/778/

If you want to help that effort, reading the thread and reviewing
the patch would be good.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company