Re: Implementing Incremental View Maintenance

Hi,

Attached is the latest patch (v15) to add support for Incremental Materialized
View Maintenance (IVM). It is possible to apply to current latest master branch.

Differences from the previous patch (v14) include:

* Fix to not use generate_series when views are queried

In the previous implementation, multiplicity of each tuple was stored
in ivm_count column in views. When SELECT was issued for views with
duplicate, the view was replaced with a subquery in which each tuple
was joined with generate_series function in order to output tuples
of the number of ivm_count.

This was problematic for following reasons:

- The overhead was huge. When almost of tuples in a view were selected,
it took much longer time than the original query. This lost the meaning
of materialized views.

- Optimizer could not estimate row numbers correctly because this had to
know ivm_count values stored in tuples.

- System columns of materialized views like cmin, xmin, xmax could not
be used because a view was replaced with a subquery.

To resolve this, the new implementation doen't store multiplicities
for views with tuple duplicates, and doesn't use generate_series
when SELECT query is issued for such views.

Note that we still have to use ivm_count for supporting DISTINCT and
aggregates.

* Add query checks for IVM restrictions

Query checks for following restrictions are added:

- DISTINCT ON
- TABLESAMPLE parameter
- inheritance parent table
- window function
- some aggregate options(such as FILTER, DISTINCT, ORDER and GROUPING SETS)
- targetlist containing IVM column
- simple subquery is only supported
- FOR UPDATE/SHARE
- empty target list
- UNION/INTERSECT/EXCEPT
- GROUPING SETS clauses

* Improve error messages

Add error code ERRCODE_FEATURE_NOT_SUPPORTED to each IVM error message.
Also, the message format was unified.

* Support subqueries containig joins in FROM clause

Previously, when multi tables are updated simultaneously, incremental
view maintenance with subqueries including JOIN didn't work correctly
due to a bug.

Best Regards,
Takuma Hoshiai

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>