PostgreSQL 9.4版本的物化视图更新

浏览数：24 / 时间：2015年06月12日

postgresql的9.4版本出来有一段时间了，也更新了很多内容，其中之一是比较感兴趣的物化视图的更新，对比原先的物化视图语法，新增了一个CONCURRENTLY参数。

一、新语法：

--创建语法,未有更新
CREATE MATERIALIZED VIEW table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

--刷新语法
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

二、数据准备：

[postgres@ ~]$ psql
psql (9.4.1)
Type "help" for help.

postgres=# create table tbl_kenyon(id int,remark text);
CREATE TABLE
postgres=# insert into tbl_kenyon select generate_series(1,1000000),md5(random()::text);
INSERT 0 1000000
postgres=# select * from tbl_kenyon limit 10;
 id |              remark              
----+----------------------------------
  1 | d4fc1c7440a4d1672028586c2bb76514
  2 | 5c1590519fa47f02db2895146a5f62a4
  3 | 1710ac4199746e9bfa188f1655d1f857
  4 | 6cae64191c2bc309a4884301e77b26ad
  5 | 813987a5c3af2d75bd0de6e288083b10
  6 | c52baa42cda22c89719bfb59dde1f78b
  7 | 491003337ea4e887c5ac24d174c691c6
  8 | 455cdf32b170fcf2b450c0b974fbf310
  9 | 43adb30aeb0a21ab35fdf97064ad1d21
 10 | 97dc1adc5484244a077e87ef36ecfe09
(10 rows)

--创建简单的物化视图
postgres=# create materialized view mv_tbl_kenyon as select * from tbl_kenyon ;
SELECT 1000000
postgres=# \d+
                              List of relations
 Schema |     Name      |       Type        |  Owner   | Size  | Description 
--------+---------------+-------------------+----------+-------+-------------
 public | mv_tbl_kenyon | materialized view | postgres | 65 MB | 
 public | tbl_kenyon    | table             | postgres | 65 MB | 
(2 rows)

三、测试用例:

--测试不带concurrently
postgres=# insert into tbl_kenyon values(1000001,md5(random()::text));
INSERT 0 1
postgres=# select max(id) from mv_tbl_kenyon ;
   max   
---------
 1000000
(1 row)

postgres=# \timing 
Timing is on.
postgres=# refresh materialized view mv_tbl_kenyon ;
REFRESH MATERIALIZED VIEW
Time: 2056.460 ms

--测试带concurrently，需要建一个唯一索引
postgres=# insert into tbl_kenyon values(1000002,md5(random()::text));
INSERT 0 1
Time: 9.434 ms

postgres=# refresh materialized view concurrently mv_tbl_kenyon;
ERROR:  cannot refresh materialized view "public.mv_tbl_kenyon" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.
Time: 22109.877 ms
postgres=# create unique index idx_ken on mv_tbl_kenyon(id);
CREATE INDEX
Time: 707.721 ms
postgres=# select max(id) from mv_tbl_kenyon ;
   max   
---------
 1000001
(1 row)

Time: 1.110 ms
postgres=# begin;
BEGIN
postgres=# refresh materialized view concurrently mv_tbl_kenyon;
REFRESH MATERIALIZED VIEW
Time: 24674.739 ms

--如果在refresh的时候，前面加个begin;
--还能发现在开启的另外的session里面，是不会阻塞查询的,反之不加concurrently会阻塞
postgres=# select * from mv_tbl_kenyon limit 10;
 id |              remark              
----+----------------------------------
  1 | d4fc1c7440a4d1672028586c2bb76514
  2 | 5c1590519fa47f02db2895146a5f62a4
  3 | 1710ac4199746e9bfa188f1655d1f857
  4 | 6cae64191c2bc309a4884301e77b26ad
  5 | 813987a5c3af2d75bd0de6e288083b10
  6 | c52baa42cda22c89719bfb59dde1f78b
  7 | 491003337ea4e887c5ac24d174c691c6
  8 | 455cdf32b170fcf2b450c0b974fbf310
  9 | 43adb30aeb0a21ab35fdf97064ad1d21
 10 | 97dc1adc5484244a077e87ef36ecfe09
(10 rows)

四、源码
相关唯一索引的源码，在matview.c里面可以查看：

--先初始化唯一索引是false
foundUniqueIndex = false;

--如果找到唯一索引赋值为true
if (foundUniqueIndex)
     appendStringInfoString(&querybuf, " AND ");

     colname = quote_identifier(NameStr((tupdesc->attrs[attnum - 1])->attname));
     appendStringInfo(&querybuf, "newdata.%s ", colname);
     type = attnumTypeId(matviewRel, attnum);
     op = lookup_type_cache(type, TYPECACHE_EQ_OPR)->eq_opr;
     mv_GenerateOper(&querybuf, op);
     appendStringInfo(&querybuf, " mv.%s", colname);

      foundUniqueIndex = true;

--如果找不到唯一索引报error
if (!foundUniqueIndex)
                ereport(ERROR,(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
                errmsg("cannot refresh materialized view \"%s\" concurrently",matviewname),
                errhint("Create a unique index with no WHERE clause on one or more columns of the materialized view.")));
        appendStringInfoString(&querybuf, " AND newdata OPERATOR(pg_catalog.*=) mv) "
                                "WHERE newdata IS NULL OR mv IS NULL " "ORDER BY tid");

五、总结：
1.新版的物化视图新增了concurrently参数，可以使在刷新视图时不会锁住该物化视图的查询工作
2.该参数的原理和优缺点与索引的concurrently类似，以时间来换取查询锁，刷新的速度会变得很慢
3.增量刷新的参数还没有，比较遗憾

六、参考：
1.http://www.postgresql.org/docs/9.4/static/sql-creatematerializedview.html 2.http://francs3.blog.163.com/blog/static/405767272014421104127225/
3.src/backend/commands/matview.c

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

PostgreSQL 9.4版本的物化视图更新