PostgreSQL 9.4版本的物化视图更新

概述postgresql的9.4版本出来有一段时间了，也更新了很多内容，其中之一是比较感兴趣的物化视图的更新，对比原先的物化视图语法，新增了一个CONCURRENTLY参数。   一、新语法： --创建语法,未有更新CREATE MATERIALIZED VIEW table_name [ (column_name [, ...] ) ] [ WITH ( storage_param 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@ ~]$ psqlpsql (9.4.1)Type "help" for help.postgres=# create table tbl_kenyon(ID int,remark text);CREATE tablepostgres=# insert into tbl_kenyon select generate_serIEs(1,1000000),md5(random()::text);INSERT 0 1000000postgres=# 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 1000000postgres=# \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)

三、测试用例:

--测试不带concurrentlypostgres=# insert into tbl_kenyon values(1000001,md5(random()::text));INSERT 0 1postgres=# 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 VIEWTime: 2056.460 ms--测试带concurrently，需要建一个唯一索引postgres=# insert into tbl_kenyon values(1000002,md5(random()::text));INSERT 0 1Time: 9.434 mspostgres=# refresh materialized vIEw concurrently mv_tbl_kenyon;ERROR:  cannot refresh materialized vIEw "public.mv_tbl_kenyon" concurrentlyHINT:  Create a unique index with no WHERE clause on one or more columns of the materialized vIEw.Time: 22109.877 mspostgres=# create unique index IDx_ken on mv_tbl_kenyon(ID);CREATE INDEXTime: 707.721 mspostgres=# select max(ID) from mv_tbl_kenyon ;   max   --------- 1000001(1 row)Time: 1.110 mspostgres=# begin;BEGINpostgres=# refresh materialized vIEw concurrently mv_tbl_kenyon;REFRESH MATERIAliZED VIEWTime: 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里面可以查看：

--先初始化唯一索引是falsefoundUniqueIndex = false;--如果找到唯一索引赋值为trueif (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;--如果找不到唯一索引报errorif (!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 4.http://my.oschina.net/Kenyon/blog/161001 总结

以上是内存溢出为你收集整理的PostgreSQL 9.4版本的物化视图更新全部内容，希望文章能够帮你解决PostgreSQL 9.4版本的物化视图更新所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。