首页 » PostgreSQL/GaussDB » Oracle、MySQL、PostGreSQL数据库比较系列（四）： 可更新VIEW

Oracle、MySQL、PostGreSQL数据库比较系列（四）： 可更新VIEW

  • 2022/04/04
  • PostgreSQL/GaussDB
  • 181 views
  • 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 —

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：