PostgreSQL 9.4版本的物化視圖更新

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 | 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.增量重新整理的參數還沒有，比較遺憾

------------------------------------華麗麗的分割線------------------------------------

CentOS 6.3環境下yum安裝PostgreSQL 9.3

PostgreSQL緩衝詳述

Windows平台編譯 PostgreSQL

Ubuntu下LAPP（Linux+Apache+PostgreSQL+PHP)環境的配置與安裝

Ubuntu上的phppgAdmin安裝及配置

CentOS平台下安裝PostgreSQL9.3

PostgreSQL配置Streaming Replication叢集

如何在CentOS 7/6.5/6.4 下安裝PostgreSQL 9.3 與 phpPgAdmin 

------------------------------------華麗麗的分割線------------------------------------

PostgreSQL 的詳細介紹：請點這裡
PostgreSQL 的：請點這裡

本文永久更新連結地址：