PostgreSQL 递归SQL 找出对象依赖

最新推荐文章于 2024-07-17 16:37:08 发布

weixin_33720452

最新推荐文章于 2024-07-17 16:37:08 发布

阅读量1.2k

点赞数

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/55715

版权

本文介绍了在PostgreSQL中如何通过递归SQL找出表、视图、物化视图之间的依赖关系。当尝试修改或删除对象时，依赖可能导致错误。通过`DROP TABLE ... CASCADE`可以自动处理依赖，但手动查询依赖关系则需要查询pg_rewrite表。作者提供了如何解析规则以获取依赖对象的OID，并创建递归函数以查找直接和间接依赖的对象。此外，还展示了如何获取视图定义。

摘要由CSDN通过智能技术生成

在使用数据库时，如果用到了视图，物化视图。
在表，视图，物化视图这些对象之间就会产生依赖。
例如

create table t(id int);
create view v1 as select * from t;
create view v2 as select * from v1;
create view v3 as select v1.id from v1,v2 where v1.id=v2.id;
create view v4 as   SELECT v1.id +
    FROM v1,   +
     v2,       +
     pg_class, +
     pg_authid;
。。。。

依赖关系导致的报错

如果要改t的字段，或者删除t表。会怎样呢？

postgres=# drop table t;
ERROR:  2BP01: cannot drop table t because other objects depend on it
DETAIL:  view v1 depends on table t
view v2 depends on view v1
view v3 depends on view v1
view v4 depends on view v1
materialized view v5 depends on view v4
materialized view v6 depends on view v4
view vv1v depends on table t
view vv1v1 depends on view vv1v
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
LOCATION:  reportDependentObjects, dependency.c:986

使用drop table t cascade可以自动删除依赖对象。

如果是改字段，对不起，需要把依赖对象先删掉，并重建依赖对象。

postgres=# alter table t alter column id type int8;

ERROR:  0A000: cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view vv1v depends on column "id"
LOCATION:  ATExecAlterColumnType, tablecmds.c:8225

DROP的时候，会通过reportDependentObjects函数打印依赖t表的视图。
代码如下

 src/backend/catalog/objectaddress.c

/*
 * reportDependentObjects - report about dependencies, and fail if RESTRICT
 *
 * Tell the user about dependent objects that we are going to delete
 * (or would need to delete, but are prevented by RESTRICT mode);
 * then error out if there are any and it's not CASCADE mode.
 *
 *    targetObjects: list of objects that are scheduled to be deleted
 *    behavior: RESTRICT or CASCADE
 *    msglevel: elog