南大通用 GBase 8c 视图关联下的 DDL 操作全解析

1、背景说明

GBase 8c 多模多态企业级分布式数据库具备高性能、高可用、弹性伸缩、高安全性等特性。

如果日常使用中表被视图引用的话，特定场景下，部分DDL操作是不能直接执行的，比如修改被视图引用的字段的类型，删除表等，而新增字段是可以操作，主要原因是视图引用了表的字段，修改的话视图也需要变化。下面演示一下这部分内容，被视图引用的表进行DDL操作会有什么表现。然后再看看怎么操作才能修改表字段等。

2、创建实验环境

2.1 创建表和视图

（1）创建2张测试表

CREATE TABLE t1 (id int,name varchar(20));
CREATE TABLE t2 (id int,name varchar(20));

（2）创建三张视图，所有视图都是使用了t1的字段，没有使用t2的字段。

CREATE OR REPLACE VIEW v1 as select * from t1;
CREATE OR REPLACE VIEW v2 as select a.* from t1 a inner join t2 b on a.id = b.id;
CREATE OR REPLACE VIEW v3 as select a.* from v1 a inner join v2 b on a.id = b.id inner join t1 c on a.id = c.id;

2.2 删除表

drop table t1;
drop table t2;

执行返回如下信息：

从执行结果提示来看，DROP TABLE是没有执行成功的，因为有视图依赖。可以通过DROP ...CASCADE来一起将依赖的视图删除，但是一般情况下我们不想将视图删除。

2.3 修改字段

ALTER TABLE T1 MODIFY NAME VARCHAR(30);
ALTER TABLE T2 MODIFY NAME VARCHAR(30);

从执行结果的提示来看，t1、t2表都修改成功了，t2很好理解，因为没有视图使用到t2的字段，虽然视图使用了t2表，但只是用来关联，视图的字段并没有使用t2表的字段，所以t2表的字段类型能修改成功。t1表能修改成功是因为级联也修改了v2视图的定义，解决了被视图引用的表定义不能修改的情况。

插入数据测试数据，并查询视图结果如下：

insert into t1 values(1,'12345678901234567890');
insert into t2 values(2,'09876543210987654321');
insert into t1 values(11,'123456789012345678901234567890');
insert into t2 values(22,'098765432109876543210987654321');
insert into t2 values(1,'098765432109876543210987654321');
select * from t1;
select * from t2;select * from v1;
select * from v2;

2.4 新增字段

ALTER TABLE  T1 ADD COMMENT VARCHAR(30);
ALTER TABLE  T2 ADD COMMENT VARCHAR(30);

新增字段没有任何限制，因为视图建立的时候，没办法引用还没有的字段。我们审视视图的定义CREATE VIEW v1 AS SELECT * FROM t1; 那此时v1会有新增的字段信息吗？答案是否定的，视图需要重新刷新才会有新增的字段。

select * from v2;
CREATE OR REPLACE VIEW v2 as select a.* from t1 a inner join t2 b on a.id = b.id;
select * from v2;

2.5 查找t2表关联的视图

通过with recursive xx as循环语句获取所有相关视图。

with recursive rec_view as(
   select c.nspname as schemaname,b.relname,rel_oid,b.relkind,d.oid as ori_oid,d.relname ori_name
,0as level  --level防止死循环
from(
   select unnest(regexp_matches(ev_action::text,':relid (\d+)','g'))::oid  rel_oid,ev_class --rel_oid 被依赖对象 ，ev_class 视图名称
   from pg_rewrite 
   union 
   select unnest(regexp_matches(ev_action::text,':resorigtbl (\d+)','g'))::oid,ev_class
   from pg_rewrite 
) deptbl                   --pg_write获取依赖关系
   inner join pg_class b       --被依赖对象获取表名等信息
   on deptbl.rel_oid = b.oid
   inner join pg_namespace c
   on b.relnamespace = c.oid
   inner join pg_class d     --视图获取视图名等信息，且用于排除pg_write获取的自身对象，即rel_oid <> ev_class
   on deptbl.ev_class = d.oid
   and deptbl.rel_oid <> d.oid
   where  b.relname ='t2'--指定表名t2
union all
   select c.nspname,b.relname,deptbl.rel_oid,b.relkind,d.oid as ori_oid,d.relname ori_name,level+1
from(
   select unnest(regexp_matches(ev_action::text,':relid (\d+)','g'))::oid  rel_oid,ev_class
   from pg_rewrite 
 union 
   select unnest(regexp_matches(ev_action::text,':resorigtbl (\d+)','g'))::oid,ev_class
   from pg_rewrite 
) deptbl 
   inner join pg_class b
   on deptbl.rel_oid = b.oid
   inner join pg_namespace c
   on b.relnamespace = c.oid
   inner join pg_class d
   on deptbl.ev_class = d.oid
   and deptbl.rel_oid <> d.oid
   inner join rec_view e          --循环语句关联条件
   on deptbl.rel_oid = e.ori_oid
   where level <=10--level防止死循环
)
select * from rec_view;

从结果看，t2表相关视图是v2,v3两个视图，假如需要备份v2,v3到文本中，可以使用gs_dump的方式。

gs_dump postgres -s -t v2 -t v3 -c -f view.ddl -p 5432