Re: Implementing Incremental View Maintenance

Hi,

Attached is the latest patch (v11) to add support for Incremental Materialized View Maintenance (IVM).

Differences from the previous patch (v10) include:
- Prohibit creating matviews including mutable functions

Matviews including mutable functions (for example now(),random(), ... etc) could result in inconsistent data with the base tables.
This patch adds a check whether the requested matview definition includes SELECTs using mutable functions. If so, raise an error while creating the matview.

This issue is reported by nuko-san.
https://www.postgresql.org/message-id/CAF3Gu1Z950HqQJzwanbeg7PmUXLc+7uZMstfnLeZM9iqDWeW9Q@mail.gmail.com

Currently other IVM's support status is:

> 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.
>
> There are two approaches with regard to timing of view maintenance:
> immediate and deferred. In immediate maintenance, views are updated in
> the same transaction where its base table is modified. In deferred
> maintenance, views are updated after the transaction is committed,
> for example, when the view is accessed, as a response to user command
> like REFRESH, or periodically in background, and so on.
>
> This patch implements a kind of immediate maintenance, in which
> materialized views are updated immediately in AFTER triggers when a
> base table is modified.
>
> This supports views using:
> - inner and outer joins including self-join
> - some built-in aggregate functions (count, sum, agv, min, max)
> - a part of subqueries
> -- simple subqueries in FROM clause
> -- EXISTS subqueries in WHERE clause
> - DISTINCT and views with tuple duplicates
>
> ===
> Here are major changes we made after the previous submitted patch:
>
> * Aggregate functions are checked if they can be used in IVM
> using their OID. Per comments from Alvaro Herrera.
>
> For this purpose, Gen_fmgrtab.pl was modified so that OIDs of
> aggregate functions are output to fmgroids.h.
>
> * Some bug fixes including:
>
> - Mistake of tab-completion of psql pointed out by nuko-san
> - A bug relating rename of matview pointed out by nuko-san
> - spelling errors
> - etc.
>
> * Add documentations for IVM
>
> * Patch is splited into eleven parts to make review easier
> as suggested by Amit Langote:
>
> - 0001: Add a new syntax:
> CREATE INCREMENTAL MATERIALIZED VIEW
> - 0002: Add a new column relisivm to pg_class
> - 0003: Change trigger.c to allow to prolong life span of tupestores
> containing Transition Tables generated via AFTER trigger
> - 0004: Add the basic IVM future using counting algorithm:
> This supports inner joins, DISTINCT, and tuple duplicates.
> - 0005: Change GEN_fmgrtab.pl to output aggregate function's OIDs
> - 0006: Add aggregates support for IVM
> - 0007: Add subqueries support for IVM
> - 0008: Add outer joins support for IVM
> - 0009: Add IVM support to psql command
> - 0010: Add regression tests for IVM
> - 0011: Add documentations for IVM
>
> ===
> Todo:
>
> Currently, REFRESH and pg_dump/pg_restore is not supported, but
> we are working on them.
>
> Also, TRUNCATE is not supported. When TRUNCATE command is executed
> on a base table, nothing occurs on materialized views. We are
> now considering another better options, like:
>
> - Raise an error or warning when a base table is TRUNCATEed.
> - Make the view non-scannable (like WITH NO DATA)
> - Update the view in some ways. It would be easy for inner joins
> or aggregate views, but there is some difficult with outer joins.

Best Regards,

--
Takuma Hoshiai <hoshiai(at)sraoss(dot)co(dot)jp>