PostgreSQL 9.4版本的物化视图更新
PostgreSQL 9.4版本的物化视图更新
kenyon_君羊 发表于3年前
PostgreSQL 9.4版本的物化视图更新
  • 发表于 3年前
  • 阅读 4406
  • 收藏 44
  • 点赞 4
  • 评论 20

移动开发云端新模式探索实践 >>>   

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
4.http://my.oschina.net/Kenyon/blog/161001
  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 494
博文 170
码字总数 121714
评论 (20)
mark35
增量刷新的参数还没有,比较遗憾
kenyon_君羊

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾
期待下一版本更新
一号男嘉宾
大爱PG
过马路的蚂蚁
越来越强大了
YuanyuanL
79
宏哥

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾
这个很难实现。
有理想的猪
79
francs
79
HARRYChinese

引用来自“过马路的蚂蚁”的评论

越来越强大了

请教一下,有没有好用的Windows客户端推荐一个?
HARRYChinese

引用来自“梅公子”的评论

大爱PG

请教一下,有没有好用的Windows客户端推荐一个?
HARRYChinese

引用来自“宏哥”的评论

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾
这个很难实现。

请教一下,pg有没有好用的Windows客户端推荐一个?
宏哥

引用来自“HARRYChinese”的评论

引用来自“宏哥”的评论

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾
这个很难实现。

请教一下,pg有没有好用的Windows客户端推荐一个?
navicat, 有时候还得用pgadmin
dreamhack

引用来自“HARRYChinese”的评论

引用来自“宏哥”的评论

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾
这个很难实现。

请教一下,pg有没有好用的Windows客户端推荐一个?

引用来自“宏哥”的评论

navicat, 有时候还得用pgadmin
+1
一号男嘉宾

引用来自“HARRYChinese”的评论

引用来自“梅公子”的评论

大爱PG

请教一下,有没有好用的Windows客户端推荐一个?
Navicat For PG与PGAdmin配合着用。目前只习惯这俩。
MGL_TECH

引用来自“dreamhack”的评论

引用来自“HARRYChinese”的评论

引用来自“宏哥”的评论

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾
这个很难实现。

请教一下,pg有没有好用的Windows客户端推荐一个?

引用来自“宏哥”的评论

navicat, 有时候还得用pgadmin
+1

嗯,但pg都没有64位的,好不爽
kenyon_君羊

引用来自“阿cat”的评论

引用来自“dreamhack”的评论

引用来自“HARRYChinese”的评论

引用来自“宏哥”的评论

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾
这个很难实现。

请教一下,pg有没有好用的Windows客户端推荐一个?

引用来自“宏哥”的评论

navicat, 有时候还得用pgadmin
+1

嗯,但pg都没有64位的,好不爽
都好用的啊
kenyon_君羊

引用来自“HARRYChinese”的评论

引用来自“梅公子”的评论

大爱PG

请教一下,有没有好用的Windows客户端推荐一个?
免费的pgadmin,或者收费的postgres maestro、manager,navicat
kenyon_君羊

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾

引用来自“宏哥”的评论

这个很难实现。
很多以前觉得难以实现的东西现在都实现了,相信科技的进步
喜之郎

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾

引用来自“kenyon_君羊”的评论

期待下一版本更新
物化视图一般放的是统计结果,每条统计结果都与统计基础数据的每一行相关,怎么个增量法?
kenyon_君羊

引用来自“mark35”的评论

增量刷新的参数还没有,比较遗憾

引用来自“kenyon_君羊”的评论

期待下一版本更新

引用来自“喜之郎”的评论

物化视图一般放的是统计结果,每条统计结果都与统计基础数据的每一行相关,怎么个增量法?
可以参考oracle的增量统计效果
×
kenyon_君羊
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: