首页 » PostgreSQL/GaussDB » Oracle、MySQL、PostGreSQL数据库比较系列（四）： 可更新VIEW
Oracle、MySQL、PostGreSQL数据库比较系列（四）： 可更新VIEW
最近在测试Oracle语法其它数据库的兼容性时，发现postgreq上对于TABLE上创建的简单view操作时还有个Oracle没有的东西:rule, 在openguass 2.1当通过view 做insert操作时会提示下面的错误，但是在postgreSQL 13.2和MoGdb2.1（支持updatable view）上并未报错:
“You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.”
# oracle
先看oracle上的insert操作，可以认为是简单的merge view.
create table testv(id int,name varchar2(20)); create view v_testv as select * from testv; insert into v_testv values(1,100); -- 正常， 查看执行计划 SQL> @x2 PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | TESTV | | | | | ---------------------------------------------------------------------------------- 7 rows selected.
Note:
在oracle上在view上执行insert时，从执行可以看出就是在表testv上的insert. 不需要任何干预。
# PostgreSQL 13.2
Postgresql我之前有安装orafce所以有varchar2 别名数据类型，但是及时不安装orafce也不影响以下测试。
anbob=# create table testv(id int,name varchar2(20));
CREATE TABLE
anbob=# create view v_testv as select * from testv;
CREATE VIEW
anbob=# insert into v_testv values(1,100);
INSERT 0 1
anbob=# select version();
version
-------------------------------------------------------------------------------------------------------------
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39.0.1), 64-bit
(1 row)
anbob=# explain insert into v_testv values(1,100);
QUERY PLAN
----------------------------------------------------
Insert on testv (cost=0.00..0.01 rows=1 width=36)
-> Result (cost=0.00..0.01 rows=1 width=36)
(2 rows)
Note:
注意pg13也是正常执行，据了解在pg很久以前的版本是也没会提示上面的错误。
# OpenGuass 2.1 & 3.0
我们知道opengauss是基于PG 9.2, 上周发布的新版本OG 3.0测试也是相同。
anbob=# create table testv(id int,name varchar2(20));
CREATE TABLE
anbob=# create view v_testv as select * from testv;
CREATE VIEW
anbob=# insert into v_testv values(1,100);
ERROR: cannot insert into view "v_testv"
HINT: You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.
anbob=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------
(openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)
Note:
注意报错提到了2个需要创建个do instead Rule 或 insert trigger, 我们都知道trigger是可以在tables上，view（NOT Mview）上是不支持trigger的, 在oracle view 创建trigger会报错:
create or replace trigger trig_v_update
after update on v_testv for each row
begin
dbms_output.put_line('updated.......');
end;
6 /
create or replace trigger trig_v_update
*
ERROR at line 1:
ORA-25001: cannot create this trigger type on this type of view
# Yugabyte 2.11
[root@oel7db1 yugabyte-2.11.1.0]# bin/ysqlsh -U yugabyte -d yugabyte
ysqlsh (11.2-YB-2.11.1.0-b0)
Type "help" for help.
yugabyte=# create table testv(id int,name varchar2(20));
ERROR: type "varchar2" does not exist
LINE 1: create table testv(id int,name varchar2(20));
^
yugabyte=# create table testv(id int,name varchar(20));
CREATE TABLE
yugabyte=# create view v_testv as select * from testv;
CREATE VIEW
yugabyte=# insert into v_testv values(1,100);
INSERT 0 1
Note:
对标TIDB的yugebyte使用的是pg解析器， 也是可以正常创建。
# opengauss 2.1
刚提到了rule 这个东西，在postgresql官方查了以下从pg 9开始引入， 这个rule系统区别于trigger, 可以在table和view上创建，会让我们执行命令时，转换成其它操作。下面创建个简单的rule
anbob-# create or replace rule r1_view_insert as on insert to v_testv do instead insert into testV values(NEW.*) RETURNING testv.*; anbob=# insert into v_testv values(1,100); INSERT 0 1 anbob=# insert into v_testv values(2,'b'); INSERT 0 1 anbob=# select * from testv; id | name ----+------ 1 | 100 2 | b (2 rows) anbob=# select * from pg_rules; schemaname | tablename | rulename | definition ------------+-------------+----------------+------------------------------------------------------------------------------------------------------------------- ----------------------------------- pg_catalog | pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, n ew.setting, false) AS set_config; pg_catalog | pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; public | v_testv | r1_view_insert | CREATE RULE r1_view_insert AS ON INSERT TO v_testv DO INSTEAD INSERT INTO testv (id, name) VALUES (new.id, new.nam e) RETURNING testv.id, testv.name; (3 rows)
Note
在OpenGauss上创建了rule 就可以做insert了， 这里在记录一下select on table RULE转换为查询其他表的方法：
anbob=# select * from testv;
id | name
----+------
1 | 100
2 | b
(2 rows)
anbob=# create table testv_2 (id int,name varchar2(100));
CREATE TABLE
anbob=# insert into testv_2 values(1,'anbob');
INSERT 0 1
create or replace rule r1_view_select AS
ON SELECT TO testV
DO INSTEAD
anbob-# SELECT * FROM testv_2;
ERROR: SELECT rule's target entry 2 has different size from column "name"
anbob=# drop table testv_2;
DROP TABLE
anbob=# create table testv_2 as select * from testv where 1=2
anbob-# ;
INSERT 0 0
create or replace rule r1_view_select AS
ON SELECT TO testV
DO INSTEAD
SELECT * FROM testv_2;
ERROR: view rule for "testv" must be named "_RETURN"
create or replace rule "_RETURN" AS
ON SELECT TO testV
DO INSTEAD
SELECT * FROM testv_2;
CREATE RULE
anbob=# select * from testv;
id | name
----+------
(0 rows)
anbob=# explain SELECT * FROM testv;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on testv_2 (cost=0.00..18.88 rows=888 width=62)
(1 row)
Note：
可以看到我们创建了select on testv的RULE， 查询时反回了testv_2 表的记录， 对于查询表需要相同的列定义，同的rule必须叫 “_RETURN”. 这真是一种奇葩用法。 希望OpenGuss后续可以把该功能继承，并解决我在1年前测试笔记OG 2.0升级后消失的dual表，在OG3.0为什么还没引入？
— over —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。