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
basetables.
 
This patch adds a check whether the requested matview definition includes SELECTs using mutable functions. If so, raise
anerror 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@sraoss.co.jp>