背景：
在数据库中对象与对象之间存在一定的依赖关系，例如继承表之间的依赖，视图与基表的依赖，主外键的依赖，序列的依赖等等。
在删除对象时，数据库也会先检测依赖，如果有依赖，会报错，需要使用cascade删除。
另外一方面，如果需要重建表，使用重命名的方式是有一定风险的，例如依赖关系没有迁移，仅仅迁移了表是不够的。
所以迁移，通常使用的是增量迁移数据，同时使用替换filenode的方式更加靠谱，依赖关系不变。
本文将介绍一下如何查找依赖关系。
创建2个view，用于测试
代码语言：javascript复制在当前schema下创建2个视图
=# create view v1 as select * from pglog;
=# create view v2 as select * from v1;
在其它schema下也创建一个视图
=# create schema sm1;
=# create view sm1.v1 as select * from pglog limit 10;创建一个解析函数，得到依赖的OID
代码语言：javascript复制-- 注意下search_path，下面建的function都是只能在指定的search_path下访问到。
set search_path='public';
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;
=# select * from get_dep_oids('v1'::regclass);
get_dep_oids
──────────────
{24971}
(1 row)
14:41:19 db: postgres@postgres, pid:54661
=# select * from get_dep_oids('v2'::regclass);
get_dep_oids
──────────────
{24975}
(1 row)
=# select * from get_dep_oids('sm1.v1'::regclass);
get_dep_oids
──────────────
{24971}
(1 row)再创建一个函数，递归的得到依赖的对象。
代码语言：javascript复制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;验证效果
代码语言：javascript复制=# select * from recursive_get_deps('pglog'::regclass);
oid │ relkind │ nspname │ relname │ deps │ ori_oid │ ori_relkind │ ori_nspname │ ori_relname
───────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────────┼─────────────┼─────────────
24975 │ v │ public │ v1 │ {24971} │ 24971 │ f │ public │ pglog
24982 │ v │ public │ v2 │ {24975} │ 24975 │ v │ public │ v1
24987 │ v │ sm1 │ v1 │ {24971} │ 24971 │ f │ public │ pglog
(3 rows)
可以看到依赖到pglog表的有3个视图，分别是public schema下的 v1 和 v2 视图、sm1 schema下的v1 视图。获取视图的定义
代码语言：javascript复制14:41:21 db: postgres@postgres, pid:54661
=# select * from pg_get_viewdef('v1',false);
pg_get_viewdef
───────────────────────────────────
SELECT pglog.log_time, ↵
pglog.user_name, ↵
pglog.database_name, ↵
pglog.process_id, ↵
pglog.connection_from, ↵
pglog.session_id, ↵
pglog.session_line_num, ↵
pglog.command_tag, ↵
pglog.session_start_time, ↵
pglog.virtual_transaction_id,↵
pglog.transaction_id, ↵
pglog.error_severity, ↵
pglog.sql_state_code, ↵
pglog.message, ↵
pglog.detail, ↵
pglog.hint, ↵
pglog.internal_query, ↵
pglog.internal_query_pos, ↵
pglog.context, ↵
pglog.query, ↵
pglog.query_pos, ↵
pglog.location, ↵
pglog.application_name ↵
FROM pglog;
(1 row)
=# select * from pg_get_viewdef('v2',false);
pg_get_viewdef
────────────────────────────────
SELECT v1.log_time, ↵
v1.user_name, ↵
v1.database_name, ↵
v1.process_id, ↵
v1.connection_from, ↵
v1.session_id, ↵
v1.session_line_num, ↵
v1.command_tag, ↵
v1.session_start_time, ↵
v1.virtual_transaction_id,↵
v1.transaction_id, ↵
v1.error_severity, ↵
v1.sql_state_code, ↵
v1.message, ↵
v1.detail, ↵
v1.hint, ↵
v1.internal_query, ↵
v1.internal_query_pos, ↵
v1.context, ↵
v1.query, ↵
v1.query_pos, ↵
v1.location, ↵
v1.application_name ↵
FROM v1;
(1 row)
=# select * from pg_get_viewdef('sm1.v1',false);
pg_get_viewdef
───────────────────────────────────
SELECT pglog.log_time, ↵
pglog.user_name, ↵
pglog.database_name, ↵
pglog.process_id, ↵
pglog.connection_from, ↵
pglog.session_id, ↵
pglog.session_line_num, ↵
pglog.command_tag, ↵
pglog.session_start_time, ↵
pglog.virtual_transaction_id,↵
pglog.transaction_id, ↵
pglog.error_severity, ↵
pglog.sql_state_code, ↵
pglog.message, ↵
pglog.detail, ↵
pglog.hint, ↵
pglog.internal_query, ↵
pglog.internal_query_pos, ↵
pglog.context, ↵
pglog.query, ↵
pglog.query_pos, ↵
pglog.location, ↵
pglog.application_name ↵
FROM pglog ↵
LIMIT 10;
(1 row)
