文档章节

PostgreSQL 9.4版本的物化视图更新

kenyon_君羊
 kenyon_君羊
发布于 2015/04/28 10:17
字数 707
阅读 4514
收藏 44
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

© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 497
博文 170
码字总数 121714
作品 0
杭州
其他
加载中

评论(20)

kenyon_君羊
kenyon_君羊

引用来自“mark35”的评论

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

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

期待下一版本更新

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

物化视图一般放的是统计结果,每条统计结果都与统计基础数据的每一行相关,怎么个增量法?
可以参考oracle的增量统计效果
喜之郎
喜之郎

引用来自“mark35”的评论

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

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

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

引用来自“mark35”的评论

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

引用来自“宏哥”的评论

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

引用来自“HARRYChinese”的评论

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

大爱PG

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

引用来自“阿cat”的评论

引用来自“dreamhack”的评论

引用来自“HARRYChinese”的评论

引用来自“宏哥”的评论

引用来自“mark35”的评论

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

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

引用来自“宏哥”的评论

navicat, 有时候还得用pgadmin
+1

嗯,但pg都没有64位的,好不爽
都好用的啊
MGL_TECH
MGL_TECH

引用来自“dreamhack”的评论

引用来自“HARRYChinese”的评论

引用来自“宏哥”的评论

引用来自“mark35”的评论

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

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

引用来自“宏哥”的评论

navicat, 有时候还得用pgadmin
+1

嗯,但pg都没有64位的,好不爽
一号男嘉宾
一号男嘉宾

引用来自“HARRYChinese”的评论

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

大爱PG

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

引用来自“HARRYChinese”的评论

引用来自“宏哥”的评论

引用来自“mark35”的评论

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

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

引用来自“宏哥”的评论

navicat, 有时候还得用pgadmin
+1
宏哥
宏哥

引用来自“HARRYChinese”的评论

引用来自“宏哥”的评论

引用来自“mark35”的评论

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

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

引用来自“宏哥”的评论

引用来自“mark35”的评论

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

请教一下,pg有没有好用的Windows客户端推荐一个?
postgresql9.5 物化视图测试

copyright http://cupegraf.com/ 视图是指数据库只存储定义该视图的查询语句(内容是查询时产生),而物化视图是一个其查询语句查询后的内容并存储的视图(内容是创建物化视图刷新视图时产生,...

廖君
2015/07/30
0
0
PostgreSQL和MySQL

翻译来源:https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/ PostgreSQL和MySQL 之间有着根本的区别。在评估两个系统之间的差异和折衷之后,必须做出明智的决定。 我们已经提...

悟道之客
05/04
0
0
PostgreSQL 快速靠谱的跨大版本升级

标签 PostgreSQL , pg_upgrade , 跨版本升级 , 原地升级 , 快速 背景 已使用pg_upgrade顺利将一个8TB的生产数据库(包含表, 索引, 类型, 函数, 外部对象等对象大概10万个)从9.3升级到9.4, 升级...

德哥
04/24
0
0
这些特性,Postgre SQL秒杀其他数据库

你可能会问自己 “为什么选择PostgreSQL ?” 开源数据库我们有好几种选择(本文参考 MySQL, MariaDB 和 Firebird ), 那么 PostgreSQL 具有哪些其它开源数据库不具备的特性呢? PostgreSQL 宣...

三墩IT人
2016/02/04
0
0
postgresql 9.3 升级至 9.4

前言: postgresql的升级方式有两种 一种是使用 pgdumpall 备份后 导入至新版本即可。 另一种就是本文使用pgupgrade方式升级 是从文件级别将 老的数据库文件 复制至新的版本中 注意: 升级前请...

Firxiao
2015/05/21
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

OSChina 周日乱弹 —— 种族不同,禁止交往

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @小小编辑:推荐歌曲《苏菲小姐》- 鱼果 《苏菲小姐》- 鱼果 手机党少年们想听歌,请使劲儿戳(这里) @貓夏:下大雨 正是睡觉的好时候 临睡前...

小小编辑
今天
226
6
Python 搭建简单服务器

Python动态服务器网页(需要使用WSGI接口),基本实现步骤如下: 1.等待客户端的链接,服务器会收到一个http协议的请求数据报 2.利用正则表达式对这个请求数据报进行解析(请求方式、提取出文...

代码打碟手
今天
1
0
Confluence 6 删除垃圾内容

属性(profile)垃圾 属性垃圾的定义为,一个垃圾用户在 Confluence 创建了用户,但是这个用户在自己的属性页面中添加了垃圾 URL。 如果你有很多垃圾用户在你的系统中创建了属性,你可以使用...

honeymose
今天
1
0
qduoj~前端~二次开发~打包docker镜像并上传到阿里云容器镜像仓库

上一篇文章https://my.oschina.net/finchxu/blog/1930017记录了怎么在本地修改前端,现在我要把我的修改添加到部署到本地的前端的docker容器中,然后打包这个容器成为一个本地镜像,然后把这...

虚拟世界的懒猫
今天
1
0
UML中 的各种符号含义

Class Notation A class notation consists of three parts: Class Name The name of the class appears in the first partition. Class Attributes Attributes are shown in the second par......

hutaishi
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部