PostgreSQL 9.4版本的物化视图更新

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 | d4fc1c7440a4d1672028586c2bb765142 | 5c1590519fa47f02db2895146a5f62a43 | 1710ac4199746e9bfa188f1655d1f8574 | 6cae64191c2bc309a4884301e77b26ad5 | 813987a5c3af2d75bd0de6e288083b106 | c52baa42cda22c89719bfb59dde1f78b7 | 491003337ea4e887c5ac24d174c691c68 | 455cdf32b170fcf2b450c0b974fbf3109 | 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=# iming 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 | d4fc1c7440a4d1672028586c2bb765142 | 5c1590519fa47f02db2895146a5f62a43 | 1710ac4199746e9bfa188f1655d1f8574 | 6cae64191c2bc309a4884301e77b26ad5 | 813987a5c3af2d75bd0de6e288083b106 | c52baa42cda22c89719bfb59dde1f78b7 | 491003337ea4e887c5ac24d174c691c68 | 455cdf32b170fcf2b450c0b974fbf3109 | 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.增量刷新的参数还没有，比较遗憾------------------------------------华丽丽的分割线------------------------------------CentOS 6.3环境下yum安装PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htmPostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmUbuntu上的phppgAdmin安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htmCentOS平台下安装PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htmPostgreSQL配置Streaming Replication集群 http://www.linuxidc.com/Linux/2014-05/101724.htm如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin http://www.linuxidc.com/Linux/2014-12/110108.htm------------------------------------华丽丽的分割线------------------------------------PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里本文永久更新链接地址