PostgreSQL的物化视图

9.4英文文档：http://www.postgresql.org/docs/9.4/interactive/sql-creatematerializedview.html

9.3中文文档：http://www.postgres.cn/docs/9.3/sql-creatematerializedview.html

概述

    物化视图是PG9.3新增的，物化视图既能记住查询SQL，也能填充数据。后期，还能在需要的时候通过refresh materialized view来实现数据刷新。(注意：目前还不支持在原表数据提交后自动刷新的功能)

    refresh materialized view的 concurrently语法是9.4新增的，可以在刷新视图时不锁住对该物化视图的查询工作，但在多行受影响时刷新速度会下降；该参数的原理和优缺点与索引的concurrently类似，以时间来换取查询锁，刷新的速度会变慢。

一、创建物化视图：

语法：

CREATE MATERIALIZED VIEW table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

 是一个PostgreSQL扩展。  

 CREATE MATERIALIZED VIEW用来定义一个查询的物化视图。在命令发出时，查询会被执行并且默认用来填充该物化视图(使用WITH NO DATA则不会填充)。之后可以通过REFRESH MATERIALIZED VIEW来刷新。

 物化视图和表有很多相同的属性，但是物化视图不支持临时物化视图和自动生更OID(既不支持WITH oid语法)。 

   CREATE MATERIALIZED VIEW: 既保存数据,又保存SQL；

   CREATE TABLE AS:只保存数据，不保存sql；

   CREATE VIEW：不保存数据，只保存SQL;

参数：

 table_name：是要创建的物化视图的名称(可以有模式修饰)。

 column_name：物化视图的列名。如果不提供column_name，将从查询结果中去获取。

 storage_parameter：该自居为物化视图指定可选的存储参数。请参照 Storage Parameters 。所有 CREATE TABLE 支持的参数CREATE MATERIALIZED VIEW也都支持，除了OID。请参照 CREATE TABLE。

 tablespace_name:指定本物化视图会被创建到哪个表空间。如果不指定，则会使用default_tablespace。

 query:是一个SELECT, TABLE，或者 VALUES 查询.该查询将在一个受限制的安全操作中执行。对自身创建临时表的函数的调用会失败。

VALUES(1，'lily'); 等价于 select 1, 'lily';
values(1,'lily'),(2,'lucy'); 
等价于 select 1 AS column1,'lily' AS column2 union select 2,'lucy';
 
TABLE name；等价于 SELECT * FROM name

 WITH [NO] DATA: 声明物化视图是否在创建时填充数据。如果不填充的话，该物化视图将会被标记为不可扫描的，直到首次REFRESH MATERIALIZED VIEW执行后才能被查询。

例子：

table=# create table lyy(id int primary key, name varchar);
CREATE TABLE
table=# insert into lyy select generate_series(1,10),'name';
INSERT 0 10
table=# select * from lyy;
 id | name
----+------
  1 | name
  2 | name
.....
(10 rows）
--创建物化视图with data或者缺省时，物化视图会被填充，处于可扫描状态
table=# create materialized view lyy_mv (id, name) as select id,name from lyy;
SELECT 10
table=# select * from lyy_mv;
 id | name
----+------
  1 | name
  2 | name
.....
(10 rows）

二、刷新物化视图

语法：

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

REFRESH MATERIALIZED VIEW 会完全替代物化视图的原有内容，原有内容会被舍弃。如果声明了WITH DATA(或者是缺省)，会执行后端查询来提供新的数据，物化视图的将保留在可扫描状态。如果声明了WITH NO DATA，物化视图将保留在不可扫描的状态。

CONCURRENTLY和WITH NO DATA不能同时使用，否则会报错。

参数：

name：要刷新的物化视图的名字(可以有模式修饰)。

CONCURRENTLY：刷新物化视图而不锁定物化视图上的查询。不指定concurrently选项时，一次影响很多行的刷新，将会使用更少的资源并且完成地更迅速，但是会锁定其他试图从该物化视图读数据的链接。该选项在少量行受影响时，可能速度会更快。

        该选项仅在这种情况下才能使用：在物化视图上有至少一个UNIQUE索引，Unique索引仅使用列名并涵盖所有行。也就是说，必须首先创建至少一个，在物化视图的一个或多个字段上，没有where子句的，UNIQUE索引，才能使用concurrently选项；否则会报错。

        在物化视图未被填充时，不能使用该选项；否则会报错。

        即使一次运行一个带有该选项的REFRESH，也可能会与任何一个物化视图形成竞态。

例子：

--原表数据增加后，不刷新物化视图，物化视图的数据就不会变
table=# insert into lyy values(11,'lyy');
INSERT 0 1
table=# select * from lyy_mv; 
 id | name
----+------
  1 | name
  2 | name
  ......
  (10 rows)
 
  
--使用with data或者缺省刷新物化视图后，新数据会填充，原数据被舍弃
table=# refresh materialized view lyy_mv with data;
REFRESH MATERIALIZED VIEW
table=# select * from lyy_mv;
 id | name
----+------
  1 | name
  2 | name
  ......
 11 |lyy
 （11 rows）
  
  
--使用with no data刷新物化视图，原数据被舍弃，未填充新数据，将处于不可扫描状态。
table=# refresh materialized view lyy_mv with no data;
REFRESH MATERIALIZED VIEW
table=# select * from lyy_mv;
ERROR:  materialized view "lyy_mv" has not been populated
detail:  Use the REFRESH MATERIALIZED VIEW command.
--重现刷新物化视图后，原数据恢复，物化视图又可以进行扫描
table=# refresh materialized view lyy_mv;
REFRESH MATERIALIZED VIEW
table=# select * from lyy_mv;
id | name
----+------
  1 | name
  2 | name
  ......
 11 |lyy
 （11 rows）
 
 
--必须先创建至少一个unique索引(无where子句，至少涉及一个字段)，才能使用concurrently
table=# refresh materialized view concurrently  lyy_mv with data;
ERROR:  cannot refresh materialized view "public.lyy_mv" concurrently
detail:  Create a unique index with no WHERE clause on one or more columns of th
e materialized view.
table=# create unique index lyy_mv_uindex on lyy_mv (id);
CREATE INDEX
table=# refresh materialized view concurrently  lyy_mv with data;
REFRESH MATERIALIZED VIEW
 
 
--concurrently与with no data 不能同时使用.
table=# refresh materialized view concurrently  lyy_mv with no data;
ERROR:  CONCURRENTLY and WITH NO DATA options cannot be used together
 
 
--物化视图未被填充时，不能使用concurrently.
table=# refresh materialized view   lyy_mv with no data;
REFRESH MATERIALIZED VIEW
table=# refresh materialized view concurrently  lyy_mv with no data;
ERROR:  CONCURRENTLY cannot be used when the materialized view is not populated

多事务中，物化视图的普通更新会阻塞对它的查询，currently更新不会阻塞查询。

物化视图的普通更新：

--事务1
table=# begin;
BEGIN
table=# select pg_backend_pid();
 pg_backend_pid
----------------
   3644
table=# refresh materialized view lyy_mv with  data;
REFRESH MATERIALIZED VIEW
 
 
--事务2
table=# begin;
BEGIN
table=# select pg_backend_pid();
 pg_backend_pid
----------------
     1316
table=# select pid,mode,relation,granted from pg_locks where relation='lyy_mv'::regclass;
 pid  |        mode         | relation | granted
------+---------------------+----------+---------
 3644 | AccessShareLock     |    74440 | t
 3644 | ShareLock           |    74440 | t
 3644 | ExclusiveLock       |    74440 | t
 3644 | AccessExclusiveLock |    74440 | t
(4 rows)
 
 
--事务3
table=# begin;
BEGIN
table=#  select pg_backend_pid();
 pg_backend_pid
----------------
           2772
table=# select * from lyy_mv;
--查询处于等待状态
 
 
--事务2
table=#  select pid,mode,relation,granted from pg_locks where relation='lyy_mv'::regclass;
 pid  |        mode         | relation | granted
------+---------------------+----------+---------
 2772 | AccessShareLock     |    74440 | f
 3644 | AccessShareLock     |    74440 | t
 3644 | ShareLock           |    74440 | t
 3644 | ExclusiveLock       |    74440 | t
 3644 | AccessExclusiveLock |    74440 | t
(5 rows）

物化视图的concurrently更新：

--事务1
table=# begin;
BEGIN
table=# select pg_backend_pid();
 pg_backend_pid
----------------
   3644
table=#  refresh materialized view concurrently lyy_mv with  data;
REFRESH MATERIALIZED VIEW
 
 
--事务2
table=# begin;
BEGIN
table=#  select pid,mode,relation,granted from pg_locks where relation='lyy_mv':
:regclass;
 pid  |       mode       | relation | granted
------+------------------+----------+---------
 3644 | AccessShareLock  |    74440 | t
 3644 | RowExclusiveLock |    74440 | t
 3644 | ExclusiveLock    |    74440 | t
(3 rows)
 
 
--事务3
table=# begin;
BEGIN
table=# select pg_backend_pid();
 pg_backend_pid
----------------
     2772
table=# select * from lyy_mv;
 id | name
----+------
  1 | name
  2 | name
......
 11 | lyy
(11 rows)
--查询可以执行，未被阻塞
 
 
--事务2
table=#  select pid,mode,relation,granted from pg_locks where relation='lyy_mv'::regclass;
 pid  |       mode       | relation | granted
------+------------------+----------+---------
 2772 | AccessShareLock  |    74440 | t
 3644 | AccessShareLock  |    74440 | t
 3644 | RowExclusiveLock |    74440 | t
 3644 | ExclusiveLock    |    74440 | t
(4 rows)

物化视图的普通更新要比concurrently更新的效率高很多。

table=# create table yy(id int primary key,name varchar);
CREATE TABLE
table=# create materialized view yy_mv(id,name) as select id, name fro
no data;
SELECT 0
table=# insert into yy(id,name) select generate_series(1,100000),'name
INSERT 0 100000
table=# select count(*) from yy;
 count
--------
 100000
table=# \timing
Time is on.
--普通刷新
table=# refresh materialized view yy_mv with data;
REFRESH MATERIALIZED VIEW
Time: 54.606 ms
table=# select count(*) from yy_mv;
 count
--------
 100000
--concurrently刷新,明显慢了很多.（此处不考虑数据量很小的情况）
--首先得为物化视图创建无where子句的unique索引。
table=# create unique index yy_mv_uindex on yy_mv(id);
CREATE INDEX
table=# refresh materialized view concurrently yy_mv with data;
REFRESH MATERIALIZED VIEW
TIME： 226.042 ms
table=# select count(*) from yy;
 count
--------
 100000

三、移除物化视图

语法：

DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP MATERIALIZED VIEW 删除已存在的物化视图。要执行该命令必须是该物化视图的所有者。

参数：

IF EXISTS ： 如果物化视图不存在不会抛出错误，仅发送一个提示信息。

name: 要移除的物化视图的名称（可以有模式修饰）。

CASCADE：自动删除依赖该物化视图的对象(比如其他的物化视图或者普通视图)。

RESTRICT : 如果有任何对象依赖于该物化视图，则拒绝删除该物化视图。（这是缺省的）。

参考资料：

http://francs3.blog.163.com/blog/static/405767272014421104127225/

http://my.oschina.net/Kenyon/blog/407093