> Note that this is the last patch in the series of IVM patches: now we
> would like focus on blushing up the patches, rather than adding new
> SQL support to IVM, so that the patch is merged into PostgreSQL 13
> (hopefully). We are very welcome reviews, comments on the patch.
>
> BTW, the SGML docs in the patch is very poor at this point. I am going
> to add more descriptions to the doc.
As promised, I have created the doc (CREATE MATERIALIZED VIEW manual)
patch.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml
index 964c9abbf7..92f5668771 100644
--- a/doc/src/sgml/ref/create_materialized_view.sgml
+++ b/doc/src/sgml/ref/create_materialized_view.sgml
@@ -62,36 +62,167 @@ CREATE [ INCREMENTAL ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_na
of the materialized view are immediately updated when base tables of the
materialized view are updated. In general, this allows faster update of
the materialized view at a price of slower update of the base tables
- because the triggers will be invoked.
+ because the triggers will be invoked. We call this form of materialized
+ view as "Incremantal materialized View Maintenance" (IVM).
</para>
<para>
There are restrictions of query definitions allowed to use this
- option. Followings are allowed query definitions:
+ option. Followings are supported query definitions for IVM:
<itemizedlist>
+
<listitem>
<para>
Inner joins (including self-joins).
</para>
</listitem>
+
<listitem>
<para>
- Some of aggregations (count, sum, avg, min, max) without HAVING clause.
+ Outer joins with following restrictions:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ Outer join view's targetlist must contain attributes used in the
+ join conditions.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT a.i FROM mv_base_a a LEFT
+JOIN mv_base_b b ON a.i=b.i;
+ERROR: targetlist must contain vars in the join condition for IVM with outer join
+ </programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Outer join view's targetlist cannot contain non strict functions.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT a.i, b.i, (k > 10 OR k = -1)
+FROM mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i;
+ERROR: targetlist cannot contain non strict functions for IVM with outer join
+ </programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Outer join supports only simple equijoin.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a
+a LEFT JOIN mv_base_b b ON a.i>b.i;
+ERROR: Only simple equijoin is supported for IVM with outer join
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b,k,j) AS SELECT a.i, b.i, k j FROM
+mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i AND k=j;
+ERROR: Only simple equijoin is supported for IVM with outer join
+ </programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Outer join view's WHERE clause cannot contain non null-rejecting
+ predicates.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a
+a LEFT JOIN mv_base_b b ON a.i=b.i WHERE k IS NULL;
+ERROR: WHERE cannot contain non null-rejecting predicates for IVM with outer join
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a
+a LEFT JOIN mv_base_b b ON a.i=b.i WHERE (k > 10 OR k = -1);
+ERROR: WHERE cannot contain non null-rejecting predicates for IVM with outer join
+ </programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Aggregate is not supported with outer join.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b,v) AS SELECT a.i, b.i, sum(k) FROM
+mv_base_a a LEFT JOIN mv_base_b b ON a.i=b.i GROUP BY a.i, b.i;
+ERROR: aggregate is not supported with IVM together with outer join
+</programlisting>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Subquery is not supported with outer join.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a
+a LEFT JOIN (SELECT * FROM mv_base_b) b ON a.i=b.i;
+ERROR: subquery is not supported with IVM together with outer join
+CREATE INCREMENTAL MATERIALIZED VIEW mv(a,b) AS SELECT a.i, b.i FROM mv_base_a
+a LEFT JOIN mv_base_b b ON a.i=b.i WHERE EXISTS (SELECT 1 FROM mv_base_b b2
+WHERE a.j = b.k);
+ERROR: subquery is not supported by IVM together with outer join
+ </programlisting>
+ </para>
+ </listitem>
+ </itemizedlist>
</para>
- </listitem>
- </itemizedlist>
- Prohibited queries with this option include followings:
- <itemizedlist>
+ </listitem>
+
<listitem>
<para>
- Outer joins.
+ Subqueries. However following forms are not supported.
</para>
- </listitem>
+
+ <para>
+ WHERE IN .. (subquery) is not supported:
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm03 AS SELECT i,j FROM
+mv_base_a WHERE i IN (SELECT i FROM mv_base_b WHERE k < 103 );
+ </programlisting>
+ </para>
+ <para>
+ subqueries in target list is not supported:
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm05 AS SELECT i,j, (SELECT k
+FROM mv_base_b b WHERE a.i = b.i) FROM mv_base_a a;
+ </programlisting>
+ </para>
+ <para>
+ Nested EXISTS subqueries is not supported:
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm11 AS SELECT a.i,a.j FROM
+mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE EXISTS(SELECT
+1 FROM mv_base_b c WHERE b.i = c.i));
+ </programlisting>
+ </para>
+ <para>
+ EXISTS subquery with aggregate function is not supported:
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm_exists AS SELECT COUNT(*)
+FROM mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i =
+b.i) OR a.i > 5;
+ </programlisting>
+ </para>
+ <para>
+ EXISTS subquery with condition other than AND is not supported:
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm10 AS SELECT a.i,a.j FROM
+mv_base_a a WHERE EXISTS(SELECT 1 FROM mv_base_b b WHERE a.i = b.i) OR
+a.i > 5;
+ </programlisting>
+ </para>
+ </listitem>
+
<listitem>
<para>
- Subqueries.
+ Some of aggregations (count, sum, avg, min, max) without HAVING
+ clause. However, aggregate functions in subquery is not supported:
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm09 AS SELECT a.i,a.j FROM mv_base_a
+a, (SELECT i, COUNT(*) FROM mv_base_b GROUP BY i) b WHERE a.i = b.i;
+ </programlisting>
</para>
</listitem>
+ </itemizedlist>
+
+ Unsupported queries with this option include followings:
+
+ <itemizedlist>
<listitem>
<para>
Aggregations other than count, sum, avg, min and max.
@@ -111,24 +242,50 @@ CREATE [ INCREMENTAL ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_na
Other restrictions include:
<itemizedlist>
+
<listitem>
<para>
- Incremental materialized views must be based on simple base
- tables. Views or materialized views are not allowed to create
- incremental materialized views.
+ IVMs must be based on simple base tables. Views or materialized views
+ are not allowed to create IVM on them.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ <command>pg_dump</command> and <command>pg_restore</command> do not
+ support IVMs. IVMs are dumped as ordinary materialized views.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>REFRESH MATERIALIZED VIEW</command> does not support IVMs.
+ </para>
+ </listitem>
+
<listitem>
<para>
When TRUNCATE command is executed on a base table, nothing occurs and
this is not applied to the materialized view.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ IVM including system columns is not supported.
+ <programlisting>
+CREATE INCREMENTAL MATERIALIZED VIEW mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610';
+ERROR: system column is not supported with IVM
+ </programlisting>
+ </para>
+ </listitem>
+
<listitem>
<para>
- Incremental materialized views are not supported by logical replication.
+ IVMs not supported by logical replication.
</para>
</listitem>
+
</itemizedlist>
</para>
