Re: Implementing Incremental View Maintenance

On Mon, 2 Dec 2019 13:48:40 -0300
Alvaro Herrera <alvherre@2ndquadrant.com> wrote:

> On 2019-Dec-02, Yugo Nagata wrote:
>
> > On Mon, 02 Dec 2019 10:36:36 +0900 (JST)
> > Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
> >
> > > >> One thing pending in this development line is how to catalogue aggregate
> > > >> functions that can be used in incrementally-maintainable views.
> > > >> I saw a brief mention somewhere that the devels knew it needed to be
> > > >> done, but I don't see in the thread that they got around to doing it.
> > > >> Did you guys have any thoughts on how it can be represented in catalogs?
> > > >> It seems sine-qua-non ...
>
> > > > In the first option, we support only built-in aggregates which we know able
> > > > to handle correctly. Supported aggregates can be identified using their OIDs.
> > > > User-defined aggregates are not supported. I think this is the simplest and
> > > > easiest way.
> > >
> > > I think this is enough for the first cut of IVM. So +1.
> >
> > If there is no objection, I will add the check of aggregate functions
> > by this way. Thanks.
>
> The way I imagine things is that there's (one or more) new column in
> pg_aggregate that links to the operator(s) (or function(s)?) that
> support incremental update of the MV for that aggregate function.  Is
> that what you're proposing?

The way I am proposing above is using OID to check if a aggregate can be
used in IVM. This allows only a part of built-in aggreagete functions.

This way you mentioned was proposed as one of options as following.

On Fri, 29 Nov 2019 17:33:28 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:
> Third, we can add a new attribute to pg_aggregate which shows if each
> aggregate can be used in IVM. We don't need to use names or OIDs list of
> supported aggregates although we need modification of the system catalogue.
>
> Regarding pg_aggregate, now we have aggcombinefn attribute for supporting
> partial aggregation. Maybe we could use combine functions to calculate new
> aggregate values in IVM when tuples are inserted into a table. However, in
> the context of IVM, we also need other function used when tuples are deleted
> from a table, so we can not use partial aggregation for IVM in the current
> implementation. This might be another option to implement "inverse combine
> function"(?) for IVM, but I am not sure it worth.

If we add "inverse combine function" in pg_aggregate that takes two results
of aggregating over tuples in a view and tuples in a delta, and produces a
result of aggregating over tuples in the view after tuples in the delta are
deleted from this, it would allow to calculate new aggregate values in IVM
using aggcombinefn together when the aggregate function provides both
functions.

Another idea is to use support functions for moving-aggregate mode which are
already provided in pg_aggregate. However, in this case, we have to apply
tuples in the delta to the view one by one instead of applying after
aggregating tuples in the delta.

In both case, we can not use these support functions in SQL via SPI because
the type of some aggregates is internal. We have to alter the current
apply_delta implementation if we adopt a way using these support functions.
Instead, we also can add support functions for IVM independent to partial
aggregate or moving-aggregate. Maybe this is also one of options.


Regards,
Yugo Nagata

--
Yugo Nagata <nagata@sraoss.co.jp>