Re: Implementing Incremental View Maintenance

Hi,

Attached is the latest patch (v13) to add support for Incremental
View Maintenance (IVM). Differences from the previous patch (v12)
include:

* Allow to maintain IMMVs containing user defined types

Previously, IMMVs (Incrementally Maintainable Materialized Views)
containing user defined types could not be maintained and an error
was raised because such columns were compared using pg_calatog.=
during tuple matching. To fix this, use the column type's default
equality operator instead of forcing to use the built-in operator.

Pointed out by nuko-san.
https://www.postgresql.org/message-id/CAF3Gu1YL7HWF0Veor3t8sQD%2BJnvozHe6WdUw0YsMqJGFezVhpg%40mail.gmail.com

* Improve an error message for unspoorted aggregate functions

Currentlly only built-in aggregate functions are supported, so
aggregates on user-defined types causes an error at view definition
time. However, the message was unappropreate like:

ERROR: aggregate function max is not supported

even though built-in max is supported. Therefore, this is improved
to include its argument types as following:

ERROR: aggregate function min(xxx) is not supported
HINT: IVM supports only built-in aggregate functions.

Pointed out by nuko-san.
https://www.postgresql.org/message-id/CAF3Gu1bP0eiv%3DCqV%3D%2BxATdcmLypjjudLz_wdJgnRNULpiX9GrA%40mail.gmail.com

* Doc: fix description of support subquery

IVM supports regular EXISTS clause not only correlated subqueries.

Regards,
Yugo Nagata

On Fri, 20 Dec 2019 14:02:32 +0900
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:

> 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

Regareds,
Yugo Nagata

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