Re: Implementing Incremental View Maintenance

> * Aggregate support
>
> The current patch supports several built-in aggregates, that is, count, sum,
> avg, min, and max. Other built-in aggregates or user-defined aggregates are
> not supported.
>
> Aggregates in a materialized view definition is checked if this is supported
> using OIDs of aggregate function. For this end, Gen_fmgrtab.pl is changed to
> output aggregate function's OIDs to fmgroids.h
> (by 0006-Change-Gen_fmgrtab.pl-to-output-aggregate-function-s.patch).
> The logic for each aggregate function to update aggregate values in
> materialized views is enbedded in a trigger function.
>
> There was another option in the past discussion. That is, we could add one
> or more new attribute to pg_aggregate which provides information about if
> each aggregate function supports IVM and its logic[2]. If we have a mechanism
> to support IVM in pg_aggregate, we may use more general aggregate functions
> including user-defined aggregate in materialized views for IVM.
>
> For example, the current pg_aggregate has aggcombinefn attribute for
> supporting partial aggregation. Maybe we could use combine functions to
> calculate new aggregate values in materialized views when tuples are
> inserted into a base table. However, in the context of IVM, we also need
> other function used when tuples are deleted from a base table, so we can not
> use partial aggregation for IVM in the current implementation.
>
> Maybe, we could support the deletion case by adding a new support function,
> say, "inverse combine function". The "inverse combine function" would take
> aggregate value in a materialized view and aggregate value calculated from a
> delta of view, and produces the new aggregate value which equals the result
> after tuples in a base table are deleted.
>
> However, we don't have concrete plan for the new design of pg_aggregate.
> In addition, even if make a new support function in pg_aggregate for IVM,
> we can't use this in the current IVM code because our code uses SQL via SPI
> in order to update a materialized view and we can't call "internal" type
> function directly in SQL.
>
> For these reasons, in the current patch, we decided to left supporting
> general aggregates to the next version for simplicity, so the current patch
> supports only some built-in aggregates and checks if they can be used in IVM
> by their OIDs.

Current patch for IVM is already large. I think implementing above
will make the patch size even larger, which makes reviewer's work
difficult. So I personally think we should commit the patch as it is,
then enhance IVM to support user defined and other aggregates in later
version of PostgreSQL.

However, if supporting user defined and other aggregates is quite
important for certain users, then we should rethink about this. It
will be nice if we could know how high such demand is.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp