Re: counting algorithm for incremental matview maintenance

Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Thursday, May 16, 2013 7:02 PM Kevin Grittner wrote:

>> Let's say there is a table and matview like this:
>
>> create table foo (fooid int primary key, val int not null);
>> create materialized view bar as select distinct val from foo;
>
>> Let's say there are millions of rows in both, and that we have
>> flagged the view for incremental maintenance.  (Syntax omitted
>> to avoid distracting bikeshedding on that when the point is the
>> algorithm.)
>
>> Now, someone runs this:
>
>> update foo set val = val + 1 where fooid between 1 and 10;
>
>> What will happen is this:
>
>> Since foo will be flagged as a relation which is referenced by
>> an incrementally maintained matview, a delta relation will be
>> materialized for this update, which will contain the net change
>> to the underlying table in the count_t system column.
>
> How and when will it clear old rows of delta relation especially
> when there are more than one matview is defined on table

There will not be a permanent delta relation -- that is something
that will exist in a tuple store or other transient structure for
the duration it is needed.  Once the incremental maintenance code
iterates through all affected matviews, the delta can be destroyed.

> and how will it maintain from where to start second time refresh
> from this delta relation to matview.

I'm not clear on what you're asking here.  If it's about how
incremental maintenance will be coordinated with any explicit
REFRESH, it seems to me that at the point a REFRESH starts, any
changes which are from commits too late to be included in the
snapshot used for the REFRESH would need to block or be queued
until completion of the REFRESH (depending on whether the view is
using synchronous or asynchronous maintenance), and any unapplied
matview deltas from before that could be discarded.

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