Re: [GENERAL] Incrementally refreshed materialized view

We came to C and discontinued the version generating code in PL/pgSQL because of - Our testing showed that triggers in C give better performance than the ones in PL/pgSQL. Our opinion may be wrong. - If we can generate triggers and other updating codes in C, we may integrate it into PostgreSQL

Re: [GENERAL] Incrementally refreshed materialized view

Dear Adam. There are read-me files within each projects. They support only inner-joins and aggregations (sum, count, min, max, avg). The updating algorithm for matviews with min/max must be differ from without ones. When there are min and/or max, we have to use the base table, otherwise, not.

Re: [GENERAL] Incrementally refreshed materialized view

On Thu, Sep 29, 2016 at 8:10 AM, Nguyễn Trần Quốc Vinh wrote: > Dear, > > As it was recommended, we pushed our projects into github: > https://github.com/ntqvinh/PgMvIncrementalUpdate. > > 1) Synchronous incremental update > - For-each-row triggers are generated for all

Re: [GENERAL] Incrementally refreshed materialized view

Dear, As it was recommended, we pushed our projects into github: https://github.com/ntqvinh/PgMvIncrementalUpdate. 1) Synchronous incremental update - For-each-row triggers are generated for all changing events on all underlying tables. 2) Asynchronous (deferred) incremental update - Triggers

Re: [GENERAL] Incrementally refreshed materialized view

We also tried to achieve incremental refresh of materialized view and our solution doesn't solve all of the use cases. Players: 1) WAL 2) Logical decoding 3) replication slots 4) custom background worker Two kinds of approaches : 1. Deferred refresh (oracle type of creating log table for each

Re: [GENERAL] Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 3:16 PM, Adam Brusselback wrote: > Well I feel like I've learned a ton already reading through the links you > provided earlier and that example above. Yeah, I know that example can really help show what will happen "under the covers", and make

Re: [GENERAL] Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 3:21 PM, Kevin Grittner wrote: > On Mon, Sep 26, 2016 at 2:04 PM, Rakesh Kumar > wrote: > > > Does PG have a concept of MV log, from where it can detect the > > delta changes and apply incremental changes quickly. > > That

Re: [GENERAL] Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 2:04 PM, Rakesh Kumar wrote: > Does PG have a concept of MV log, from where it can detect the > delta changes and apply incremental changes quickly. That is what I am trying to work toward with the patch I cited in an earlier post. Once some

Re: [GENERAL] Incrementally refreshed materialized view

> Of course 9.5 is the current release so the answer is Yes, since 9.5 https://www.postgresql.org/docs/current/static/sql-creatematerializedview.html What am I missing. I don't see any support for incremental refresh. Just in case we are not speaking the same thing: When a MV is created for

Re: [GENERAL] Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 1:22 PM, Melvin Davidson wrote: > On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback > wrote: >> >> I am working on a plan to implement incrementally refreshed >> materialized "views" with the existing functionality in >>

Re: [GENERAL] Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 2:35 PM, Rob Sargent wrote: > Of course 9.5 is the current release so the answer is Yes, since 9.5 > > It seems like there is some confusion about what we're talking about. I am talking about incremental updates to a sort of "fake" materialized view

Re: [GENERAL] Incrementally refreshed materialized view

I require eagerly refreshed materialized views for my use case, which is something Postgres does not currently support. I need my updates to a table the view refers to visible within the same transaction, and often it is a single change to one row which will only effect a single row in the view.

Re: [GENERAL] Incrementally refreshed materialized view

Of course 9.5 is the current release so the answer is Yes, since 9.5 On 09/26/2016 12:29 PM, Rakesh Kumar wrote: *Does PG support INCREMENTAL MV ? Looks like not (until 9.5)*

Re: [GENERAL] Incrementally refreshed materialized view

Does PG support INCREMENTAL MV ? Looks like not (until 9.5)

Re: [GENERAL] Incrementally refreshed materialized view

On Mon, Sep 26, 2016 at 1:56 PM, Adam Brusselback 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

[GENERAL] Incrementally refreshed materialized view

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