Re: Implementing Incremental View Maintenance

2019年12月3日(火) 14:42 Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>:

> On Mon, 2 Dec 2019 13:48:40 -0300
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
>
> > On 2019-Dec-02, Yugo Nagata wrote:
> >
> > > On Mon, 02 Dec 2019 10:36:36 +0900 (JST)
> > > Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)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(at)sraoss(dot)co(dot)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(at)sraoss(dot)co(dot)jp>
>
>
>