β

PostgreSQL9.3Beta1:视图新增可更新功能( Updatable Views )

PostgreSQL DBA 30 阅读
 
PostgreSQL 9.3 版本支持视图更新操作,也就是说可以在 views 上执行 UPDATE/INSERT/DELETE
操作,但这种视图必须是简单的而且还有其它限制条件,例如视图创建中只允许引用单张表,等等,接
下来会介绍这些限制条件,先通过一个简单的实验验证下:

一 简单测试: 可更新视图
--1.1 创建测试表
francs=> create table test_view1 (id int4 primary key ,name character varying(64),creat_time timestamp without time zone);
CREATE TABLE

francs=> insert into test_view1 select generate_series(1,100000),'a_' || generate_series(1,100000),clock_timestamp();
INSERT 0 100000

francs=> select * from test_view1 limit 3;
id | name |         creat_time
----+------+----------------------------
1 | a_1  | 2013-05-18 15:25:25.815398
2 | a_2  | 2013-05-18 15:25:25.816195
3 | a_3  | 2013-05-18 15:25:25.816219
(3 rows)

--1.2 创建视图
francs=> create view view1_test as select id,name from test_view1;
CREATE VIEW

--1.3 查看表,视图大小
francs=> select pg_relation_size('test_view1');
pg_relation_size
------------------
4825088
(1 row)

francs=> select pg_relation_size('view1_test');
pg_relation_size
------------------
0
(1 row)
备注:视图占用 0 字节,说明本身不存数据,这与物化视图不同。
--1.4 更新视图
francs=> select * from view1_test where id=1;
id | name
----+------
1 | a_1
(1 row)

francs=> update view1_test set name='a_111' where id=1;
UPDATE 1

francs=> select * from view1_test where id=1;
id | name
----+-------
1 | a_111
(1 row)
备注:视图 view1_test 果然可以更新,接下来看表中的数据是否被更新。

--1.5 验证表数据
francs=> select * from test_view1 where id=1;
id | name  |         creat_time
----+-------+----------------------------
1 | a_111 | 2013-05-18 15:25:25.815398
(1 row)
备注:更新视图后,表中对应的数据被更新了,原理:当视图被更新时,PostgreSQL 会将视图上的
INSERT/UPDATE/DELETE 语句传送到视图引用的基表。回到本文开始的问题,只有简单的视图
才支持可更新操作,并且有很多限制,如下:
--1.6 可更新视图的限制
1 The view must have exactly one entry in its FROM list, which must be a table or another updatable view.

2 The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.

3 The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.

4 All columns in the view's select list must be simple references to columns of the underlying relation. They cannot
be expressions, literals or functions. System columns cannot be referenced, either.

5 No column of the underlying relation can appear more than once in the view's select list.

6 The view must not have the security_barrier property.
备注:以上来自手册,不翻译了。

做这个实验时想到一个问题,假如赋给一个用户对这张视图的 select, update 权限,而不赋予这个
用户对这张视图所引用的表的 select ,update 权限,那么这个用户是否能更新视图呢?接着实验。
二 测试二:权限测试
--2.1 创建测试用户并赋权
postgres=# create role user1 LOGIN encrypted password 'user1' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ;
CREATE ROLE

postgres=# \c francs francs
You are now connected to database "francs" as user "francs".
francs=> grant connect on database francs to user1 ;
GRANT

francs=> grant select,update on view1_test to user1;
GRANT

francs=> grant usage on schema francs to user1;
GRANT

--2.2 测试 user1  权限
francs=> \c francs user1;
You are now connected to database "francs" as user "user1".

francs=> select * from francs.test_view1 limit 1;
ERROR:  permission denied for relation test_view1

francs=> select * from francs.view1_test limit 1;
id | name
----+------
2 | a_2
(1 row)
备注:user1 能查询视图,但不能查询基表。

francs=>  update francs.test_view1 set name='update' where id=3;
ERROR:  permission denied for relation test_view1

francs=> update francs.view1_test set name='update' where id=2;
UPDATE 1

francs=> select * from francs.view1_test where id=2;
id |  name
----+--------
2 | update
(1 row)

francs=> select * from francs.view1_test where id=2;
id |  name
----+--------
2 | update
(1 row)
备注:user1 能更新视图,但不能直接更新基表。
三 总结
上面简单的演示了可更新视图,非常重要的特性,在使用过程中可能会碰到更多问题,以后补充。
四 参考
http://www.postgresql.org/docs/9.3/static/sql-createview.html
http://www.depesz.com/2012/12/11/waiting-for-9-3-support-automatically-updatable-views/

 
作者:PostgreSQL DBA
Francs 的博客

发表评论