Re: counting algorithm for incremental matview maintenance

2013/5/17 Claudio Freire <klaussfreire(at)gmail(dot)com>:

> On Fri, May 17, 2013 at 4:25 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>
>>> (3) The count algorithm must be implemented in a way that understands
>>> MVCC internals: Reading the base tables must be done using a technique
>>> that reads all rows (i.e., also the ones not visible to the current
>>> transaction), and the xmin/xmax/etc of the updated rows in the matview
>>> must be set in a smart way, so as to yield visibility results that
>>> correspond to the visibility of the “originating” rows in the base
>>> tables. Calculation of the matview deltas (especially the part where
>>> the base tables are inspected for joining rows) must in this case
>>> probably be done in a serialized manner.
>>
>> I will look at this option some more, but on the face of it, I'm
>> not quite following how it would work; and it sounds invasive and
>> fragile.
>
> I don't believe it would be that problematic if deltas:
>
> 1 - contains all changes, matching join conditions or not, that could
> potentially alter the matview. This includes the example's alterations
> since the columns being altered were part of the matview's definition,
> but it would not cover updates to columns that were not part of the
> definition (ie: not involved in the join or the selected rows).
> 2 - each update is marked with its xid
>
> Then, "serialization" can be achieved by only applying committed xids,
> discarding rolled back xids, and evaluating join satisfaction only
> during the updates, and not during delta logging.

I think that your idea of postponing some of the processing to later
commits might indeed make it possible to implement this without
needing the “MVCC-expressiveness enhancement” that I mentioned in my
previous post (by postponing the application to the matview until at
least *someone* must be able to see that particular change). Right now
I have some difficulties working out the MVCC-details of what to do
when joining a base table delta row with the contents of the other
base tables (to create the matview delta), but I’m probably just tired
:-).

In any case, your proposal seems to require the following additional
“infrastructural changes” relative to Kevin’s proposal:

(1) The base table deltas must be shared by all “in flight”
transactions that are in the process of putting stuff in them and
using them to apply changes to the matviews.
(2) Putting stuff in base table deltas must be transactionally safe
(unless all matviews that cares about that specific base table delta
are unlogged).
(3) “Right-after-each-statement matview maintenance” is not possible
(without additional effort).

Nicolas

--
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?