Re: Implementing Incremental View Maintenance

On Sun, Aug 1, 2021 at 11:30 PM Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:

> Hi hackers,
>
> On Mon, 19 Jul 2021 09:24:30 +0900
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:
>
> > On Wed, 14 Jul 2021 21:22:37 +0530
> > vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> > > The patch does not apply on Head anymore, could you rebase and post a
> > > patch. I'm changing the status to "Waiting for Author".
> >
> > Ok. I'll update the patch in a few days.
>
> Attached is the latest patch set to add support for Incremental
> Materialized View Maintenance (IVM)
>
> The patches are rebased to the master and also revised with some
> code cleaning.
>
> IVM is a way to make materialized views up-to-date in which only
> incremental changes are computed and applied on views rather than
> recomputing the contents from scratch as REFRESH MATERIALIZED VIEW
> does. IVM can update materialized views more efficiently
> than recomputation when only small part of the view need updates.
>
> The patch set implements a feature so that materialized views could be
> updated automatically and immediately when a base table is modified.
>
> Currently, our IVM implementation supports views which could contain
> tuple duplicates whose definition includes:
>
> - inner and outer joins including self-join
> - DISTINCT
> - some built-in aggregate functions (count, sum, agv, min, and max)
> - a part of subqueries
> -- simple subqueries in FROM clause
> -- EXISTS subqueries in WHERE clause
> - CTEs
>
> We hope the IVM feature would be adopted into pg15. However, the size of
> patch set has grown too large through supporting above features.
> Therefore,
> I think it is better to consider only a part of these features for the
> first
> release. Especially, I would like propose the following features for pg15.
>
> - inner joins including self-join
> - DISTINCT and views with tuple duplicates
> - some built-in aggregate functions (count, sum, agv, min, and max)
>
> By omitting outer-join, sub-queries, and CTE features, the patch size
> becomes
> less than half. I hope this will make a bit easer to review the IVM patch
> set.
>
> Here is a list of separated patches.
>
> - 0001: Add a new syntax:
> CREATE INCREMENTAL MATERIALIZED VIEW
> - 0002: Add a new column relisivm to pg_class
> - 0003: Add new deptype option 'm' in pg_depend
> - 0004: Change trigger.c to allow to prolong life span of tupestores
> containing Transition Tables generated via AFTER trigger
> - 0005: Add IVM supprot for pg_dump
> - 0006: Add IVM support for psql
> - 0007: Add the basic IVM future:
> This supports inner joins, DISTINCT, and tuple duplicates.
> - 0008: Add aggregates (count, sum, avg, min, max) support for IVM
> - 0009: Add regression tests for IVM
> - 0010: Add documentation for IVM
>
> We could split the patch furthermore if this would make reviews much
> easer.
> For example, I think 0007 could be split into the more basic part and the
> part
> for handling tuple duplicates. Moreover, 0008 could be split into "min/max"
> and other aggregates because handling min/max is a bit more complicated
> than
> others.
>
> I also attached IVM_extra.tar.gz that contains patches for sub-quereis,
> outer-join, CTE support, just for your information.
>
> Regards,
> Yugo Nagata
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
>
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
>
Hi,
For v23-0008-Add-aggregates-support-in-IVM.patch :

As a restriction, expressions specified in GROUP BY must appear in
the target list because tuples to be updated in IMMV are identified
by using this group keys.

IMMV -> IMVM (Incremental Materialized View Maintenance, as said above)
Or maybe it means 'incrementally maintainable materialized view'. It would
be better to use the same abbreviation.

this group keys -> this group key

+ errmsg("GROUP BY expression not appeared in select
list is not supported on incrementally maintainable materialized view")));

expression not appeared in select list -> expression not appearing in
select list

+ * For aggregate functions except to count

except to count -> except count

Cheers