PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系
背景
在数据库中对象与对象之间存在一定的依赖关系，例如继承表之间的依赖，视图与基表的依赖，主外键的依赖，序列的依赖等等。
在删除对象时，数据库也会先检测依赖，如果有依赖，会报错，需要使用cascade删除。
另外一方面，如果需要重建表，使用重命名的方式是有一定风险的，例如依赖关系没有迁移，仅仅迁移了表是不够的。
所以迁移，通常使用的是增量迁移数据，同时使用替换filenode的方式更加靠谱，依赖关系不变。
本文将介绍一下如何查找依赖关系。
依赖举例
在使用数据库时，如果用到了视图，物化视图。
在表，视图，物化视图这些对象之间就会产生依赖。
例如
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 level for non-error report messages
* origObject: base object of deletion, or NULL if not available
* (the latter case occurs in DROP OWNED)
*/
static void
reportDependentObjects(const ObjectAddresses *targetObjects,
DropBehavior behavior,
int msglevel,
const ObjectAddress *origObject)
{
...
依赖信息在哪里
但是我们不DROP怎么知道依赖关系呢？
视图和物化视图其实都在pg_rewrite中，通过查询ev_action就可以得到定义。
postgres=# select ev_action from pg_rewrite where ev_class='v1'::regclass;
({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowS
ecurity false :cteList <> :rtable ({RTE :alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("id")} :rtekind 0 :relid 13090504 :relkind v :tablesample <> :lateral false :inh false :inFromCl false :requiredPer
ms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>} {RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("id")} :rtekind 0 :relid 13090504 :relkind v :tablesample
<> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :securityQuals <>} {RTE :alias <> :eref {ALIAS :aliasname t :colnames ("id")} :rtekind 0 :relid 13090484 :r
elkind r :tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9) :insertedCols (b) :updatedCols (b) :securityQuals <>}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <
>} :targetList ({TARGETENTRY :expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 3 :varoattno 1 :location 25} :resno 1 :resname id :ressortgroupref 0 :resorigtbl 13090484 :resorigcol 1 :resjunk
false}) :onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :rowMarks <> :setOperations <> :constraintDeps <>})
(1 row)
如何递归的找出依赖
通过解析这个规则，可以得到依赖的对象OID。
创建一个解析函数，得到依赖的OID
create or replace function get_dep_oids(oid) returns oid[] as $$
declare
res oid[];
begin
select array_agg(unnest::oid) into res from
(
select unnest(regexp_matches(ev_action::text,':relid (\d+)', 'g')) from pg_rewrite where ev_class = $1
union
select unnest(regexp_matches(ev_action::text,':resorigtbl (\d+)','g')) from pg_rewrite where ev_class = $1
EXCEPT
select oid::text from pg_class where oid=$1
) t;
return res;
end;
$$ language plpgsql strict;
例子
查询V1依赖的对象OID
postgres=# select * from get_dep_oids('v1'::regclass);
get_dep_oids
--------------
{13090484}
(1 row)
再创建一个函数，递归的得到依赖的对象。
create or replace function recursive_get_deps(IN tbl oid, OUT oid oid, OUT relkind "char", OUT nspname name, OUT relname name, OUT deps oid[], OUT ori_oid oid, OUT ori_relkind "char", OUT ori_nspname name, OUT ori_relname name ) returns setof record as
$$
declare
begin
return query
with recursive a as (
select * from (
select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps,(select t1.oid from pg_class t1,pg_namespace t2 where t1.relnamespace=t2.oid and t1.oid=tbl) as ori_oid from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and t1.relkind in ('m','v')
) t where t.ori_oid = any(t.deps)
union
select * from (
select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps, a.oid as ori_oid from pg_class t1,pg_namespace t2,a where t1.relnamespace=t2.oid and t1.relkind in ('m','v')
) t where t.ori_oid = any(t.deps)
)
select a.oid,a.relkind,a.nspname,a.relname,a.deps,a.ori_oid,b.relkind ori_relkind, c.nspname ori_nspname,b.relname ori_relname from a,pg_class b,pg_namespace c where a.ori_oid=b.oid and b.relnamespace=c.oid order by a.nspname,a.relkind,a.relname;
end;
$$ language plpgsql strict;
例子 :
查询所有直接或间接依赖t表的对象
postgres=# select * from recursive_get_deps('t'::regclass);
oid | relkind | nspname | relname | deps | ori_oid | ori_relkind | ori_nspname | ori_relname
----------+---------+---------+---------+-----------------------------------------+----------+-------------+-------------+-------------
13090804 | m | public | v5 | {13090794} | 13090794 | v | public | v4
13090808 | m | public | v6 | {13090804,13090794,0} | 13090804 | m | public | v5
13090808 | m | public | v6 | {13090804,13090794,0} | 13090794 | v | public | v4
13090504 | v | public | v1 | {13090484} | 13090484 | r | public | t
13090508 | v | public | v2 | {13090504} | 13090504 | v | public | v1
13090790 | v | public | v3 | {13090508,13090504} | 13090504 | v | public | v1
13090790 | v | public | v3 | {13090508,13090504} | 13090508 | v | public | v2
13090794 | v | public | v4 | {13090508,1259,1260,13090504} | 13090504 | v | public | v1
13090794 | v | public | v4 | {13090508,1259,1260,13090504} | 13090508 | v | public | v2
13090815 | v | public | vv1v | {13090484} | 13090484 | r | public | t
13090819 | v | public | vv1v1 | {13090508,13090790,13090504,13090815,0} | 13090790 | v | public | v3
13090819 | v | public | vv1v1 | {13090508,13090790,13090504,13090815,0} | 13090508 | v | public | v2
13090819 | v | public | vv1v1 | {13090508,13090790,13090504,13090815,0} | 13090815 | v | public | vv1v
13090819 | v | public | vv1v1 | {13090508,13090790,13090504,13090815,0} | 13090504 | v | public | v1
(14 rows)
查到直接和间接依赖t表的对象有v5,v6,v1,v2,v3,v4,vv1v,vv1v1， 和之前DROP table t的报错内容一致。
是不是很帅呢
获取视图定义
拿到依赖关系后，我们还可以通过pg_get_viewdef拿到视图的定义
postgres=# select * from pg_get_viewdef('v4',false);
pg_get_viewdef
----------------
SELECT v1.id +
FROM v1, +
v2, +
pg_class, +
pg_authid;
(1 row)
递归语法请参考我之前写的文章
https://yq.aliyun.com/articles/54657
小结
主要用到的技巧
1. 规则表达式匹配 regexp_matches
https://www.postgresql.org/docs/9.6/static/functions-matching.html
2. 递归查询 with recursive query
https://yq.aliyun.com/articles/54657