关于数据库:GaussDBDWS应用实战对被视图引用的表进行DDL操作

摘要：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/b…
)**