GaussDB(DWS) 应用实战:对被视图引用的表进行 DDL 操作

发布于: 2020 年 09 月 11 日

摘要:GaussDB(DWS)是从Postgres演进过来的,像Postgres一样,如果表被视图引用的话,特定场景下,部分DDL操作是不能直接执行的。



背景说明



GaussDB(DWS)是从Postgres演进过来的,像Postgres一样,如果表被视图引用的话,特定场景下,部分DDL操作是不能直接执行的,比如修改被视图引用的字段的类型,删除表等,而新增字段是可以操作,主要原因是视图引用了表的字段,修改的话视图也需要变化。下面稍微演示一下这部分内容,被视图引用的表进行DDL操作会有什么表现。然后再看看怎么操作才能修改表字段等。



生成实验内容



建2个测试表,3个测试视图,建的SQL语句如下,注意所有视图都是使用了t1的字段,没有使用t2的字段。



CREATE TABLE t1 (id int,name varchar(20));
CREATE TABLE t2 (id int,name varchar(20));
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;



一、删除表



DROP TABLE t1;
DROP TABLE t2;





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



二、修改字段



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





从执行结果的提示来看,t1表修改字段类型失败了,因为视图v2使用了这个字段,而t2表修改成功了,因为没有视图使用到t2的字段,虽然视图里面使用了t2表,但只是用来关联,视图的字段并没有使用t2表的字段,所以t2表的字段类型能修改成功。



为了后面实验能顺利实现目标,此处修改v2的视图,让其获取t2的字段



ALTER TABLE T2 MODIFY NAME VARCHAR(20);
CREATE OR REPLACE VIEW v2 as select b.* from t1 a inner join t2 b on a.id = b.id;



三、新增字段



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;





如何修改被视图引用的表定义?



那么问题来了,怎么样才能修改类似上面修改字段的修改被视图引用的表的定义呢?



我觉得可以分以下几步



备份视图定义到文本->备份表定义到文本->文本中修改表定义->备份表(ALTER TABLE XX RENAME TO XX_BAK)->新增修改后的表->插入数据->备份视图文本刷新视图



其中比较难获取的一个内容是,表被哪些视图引用?这里面需要使用pg_rewrite获取引用关系,以及with recursive .. as 循环获取。



一、备份视图定义到文本



先获取表设计到哪些视图,这个SQL稍微有点复杂,这里分几步来说明



通过pg_rewrite拿到表与视图的依赖关系



select c.nspname as schemaname,b.relname,rel_oid,b.relkind,d.oid as ori_oid,d.relname ori_name
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





通过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
,0 as 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 mydb1 -s -t v2 -t v3 -c -f view.ddl -p 25308





二、备份表定义到文本->文本中修改表定义->备份表(ALTER TABLE XX RENAME TO XX_BAK)->新增修改后的表并插入数据



备份表定义到文本:使用gs_dump将t2的表结构导出到文件



文本中修改表定义:将name的字段类型从原来的varchar(30)修改为varchar(50)



备份表(ALTER TABLE XX RENAME TO XX_BAK):在文本中增加ALTER TABLE RENAME动作



新增修改后的表并插入数据:在文本中增加插入数据SQL



gs_dump mydb1 -s -t t2 -f t2.ddl -p 25308



上述内容修改后,结果如下图



执行该文本语句



gsql -d mydb1 -p 25308 -r -f t2.ddl



三、刷新视图



执行导出的v2,v3视图



gsql -d mydb1 -p 25308 -r -f view.ddl



然后检查t2表是否修改了定义,并查看视图是否能够查询



\d t2
select * from v2;
select * from v3;





总结



因为视图使用表时会产生依赖关系,在修改被视图依赖的表的定义时,特定情况下是没办法修改的,这里我认为可以通过以下步骤来实现:备份视图定义到文本->备份表定义到文本->文本中修改表定义->备份表(ALTER TABLE XX RENAME TO XX_BAK)->新增修改后的表->插入数据->备份视图文本刷新视图



其中备份视图定义这一步,需要先知道你需要修改的表的相关视图是什么。这个查询的过程需要使用pg_rewrite表和with recursive xx as递归获取相关视图。获取到相关视图备份下来以后,剩下的步骤就比较简单了。



点击关注,第一时间了解华为云新鲜技术~



用户头像

传送门:https://bbs.huaweicloud.com/ 2020.07.14 加入

华为云开发者社区,提供全面深入的云计算前景分析、丰富的技术干货、程序样例,分享华为云前沿资讯动态,方便开发者快速成长与发展,欢迎提问、互动,多方位了解云计算!

评论

发布
暂无评论
GaussDB(DWS)应用实战:对被视图引用的表进行DDL操作