PostgreSQL 递归SQL 找出对象依赖

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

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 its 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_ ::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;

 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;

 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

知识分享之PostgreSQL——OIDS的特性与新版本去除SQL 之前一直使用的PostgreSQL 9.6系列版本，由于官方不再维护了，就准备换成最新稳定版本的，查看了一下官方版本说明，发现13系列版本是目前稳定性较好的版本，于是兴冲冲的更换了过来，但随之而来的就是一些新特性，其中就比如表中的OID字段，这个字段是对象标识符，之前能用于行标记，现在发现只有表才具有这个隐藏字段，行数据没有这个支持了，于是就需要将老版本的表进行关闭掉这个字段。下面我们就开始关闭之旅。
PostgreSQL集群篇——常用的运维SQL 本文主要是我日常使用的一些运维SQL和整理于互联网上的SQL，为了方便日常的使用，特把其汇总起来，遇到常用的时将会进行补充该文，欢迎大家在评论区进行提出一些常用的SQL。