> 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>