Re: Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback <adambrusselback(at)gmail(dot)com
> wrote:

> Hello all,
> 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:
>
> Trigger based eagerly updated materialized tables for Postgres 9.5
>>
>>
>>
>> High level plan:
>>
>> Have a view definition stored in the database which we can use for
>> reference. Create functions which will read that view definition, and
>> create a materialized table with all the same columns as the reference
>> view, create triggers on all tables the view depends on to keep the
>> materialized table fresh within a transaction. All queries would hit the
>> materialized table, the view is just there so we know what dependencies to
>> track, and have an easy way to update the materialized table.
>>
>>
>>
>> How do we actually do the refresh?
>>
>> 1. A refresh key is defined for the materialized table.
>>
>> 2. Every dependent table must roll up to that refresh key so we
>> know what rows to refresh.
>>
>> 3. That key should be able to be referenced in the views where
>> clause performantly so we can refresh just the rows that match the refresh
>> key using the view.
>>
>> 4. The refresh will be done by deleting any existing rows with the
>> key, and inserting new ones with the key from the view.
>>
>> How do we know what to refresh?
>>
>> 1. A before statement trigger to create a temp table to log all
>> changes.
>>
>> 2. A for each row trigger to log the rows modified by DML.
>>
>> a. This should be done at the refresh key level.
>>
>> i. We
>> need to figure out a way to generate queries to roll up things multiple
>> levels on the dependency chain until we get to the refresh key. Not sure
>> at all how to do that.
>>
>> 3. An after statement trigger to run a refresh on the materialized
>> table, looking at only the rows touched by the DML.
>>
>
> I am however stuck on: How do we know what to refresh? -> Step 2
> 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.
> Thanks,
> -Adam
>

*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
<https://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html>*

*Can't you do that in a cron job?*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.