postgresql的9.4版本出來有一段時間了，也更新了很多內(nèi)容，其中之一是比較感興趣的物化視圖的更新，對比原先的物化視圖語法，新增了一個CONCURRENTLY參數(shù)。?

?一、新語法： --創(chuàng)建語法,未有更新 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 ] 二、數(shù)據(jù)準備： [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 | d4fc1c7440a4d1672028586c2bb765142 | 5c1590519fa47f02db2895146a5f62a43 | 1710ac4199746e9bfa188f1655d1f8574 | 6cae64191c2bc309a4884301e77b26ad5 | 813987a5c3af2d75bd0de6e288083b106 | c52baa42cda22c89719bfb59dde1f78b7 | 491003337ea4e887c5ac24d174c691c68 | 455cdf32b170fcf2b450c0b974fbf3109 | 43adb30aeb0a21ab35fdf97064ad1d2110 | 97dc1adc5484244a077e87ef36ecfe09 (10 rows)--創(chuàng)建簡單的物化視圖 postgres=# create materialized view mv_tbl_kenyon as select * from tbl_kenyon ; SELECT 1000000 postgres=# \d+List of relationsSchema | 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 mspostgres=# 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; --還能發(fā)現(xiàn)在開啟的另外的session里面，是不會阻塞查詢的,反之不加concurrently會阻塞 postgres=# select * from mv_tbl_kenyon limit 10;id | remark ----+----------------------------------1 | d4fc1c7440a4d1672028586c2bb765142 | 5c1590519fa47f02db2895146a5f62a43 | 1710ac4199746e9bfa188f1655d1f8574 | 6cae64191c2bc309a4884301e77b26ad5 | 813987a5c3af2d75bd0de6e288083b106 | c52baa42cda22c89719bfb59dde1f78b7 | 491003337ea4e887c5ac24d174c691c68 | 455cdf32b170fcf2b450c0b974fbf3109 | 43adb30aeb0a21ab35fdf97064ad1d2110 | 97dc1adc5484244a077e87ef36ecfe09 (10 rows) 四、源碼
?相關(guān)唯一索引的源碼，在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"); 五、總結(jié)：?
1.新版的物化視圖新增了concurrently參數(shù)，可以使在刷新視圖時不會鎖住該物化視圖的查詢工作 ,會對9.4之后，9.3之前的物化視圖的查詢，其會阻塞有關(guān)物化視圖的查詢
2.該參數(shù)的原理和優(yōu)缺點與索引的concurrently類似，以時間來換取查詢鎖，刷新的速度會變得很慢?
3.增量刷新的參數(shù)還沒有，比較遺憾?

總結(jié)

以上是生活随笔為你收集整理的PostgreSQL 9.4版本的物化视图更新的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。