On Mon, Sep 26, 2016 at 1:22 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote: > On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback <adambrusselback@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
