GaussDB(DWS)中，系统表PG_DEPEND和PG_SHDEPEND记录数据库对象之间的依赖关系。这个信息允许DROP命令找出哪些对象必须由DROP CASCADE删除，或是在DROP RESTRICT的情况下避免删除。

PG_DEPEND：在集群内不同数据库之间不共享，每个数据库下各有一份pg_depend，记录的是每个数据库下对象之间的依赖（如表、视图等），不同库下pg_depend内容不同。

PG_SHDEPEND：在集群内不同数据库之间共享，每个数据库下pg_shdepend的内容相同，该系统表记录的是数据库对象和共享对象（比如角色）之间的依赖性关系，作用与PG_DEPEND类似。

1.查看对象依赖关系

如前所述，如需查看依赖关系，可通过对象的OID去PG_DEPEND和PG_SHDEPEND中查看：

postgres=# select * from pg_depend where objid = 24642 or refobjid = 24642;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------+-------+----------+------------+----------+-------------+---------
    1247 | 24644 |        0 |       1259 |    24642 |           0 | i
    1259 | 24642 |        0 |       2615 |     2200 |           0 | n
    9001 | 24642 |        0 |       1259 |    24642 |           0 | i
    2604 | 24645 |        0 |       1259 |    24642 |           2 | a
    1259 | 24640 |        0 |       1259 |    24642 |           2 | a
(5 rows)

postgres=# select * from pg_shdepend where objid = 24636 or refobjid = 24636;
 dbid  | classid | objid | objsubid | refclassid | refobjid | deptype | objfile 
-------+---------+-------+----------+------------+----------+---------+---------
 15258 |    2615 | 24637 |        0 |       1260 |    24636 | o       | 
(1 row)

其中，refobjid表示被引用对象的oid，refclassid表示被引用对象所在的系统表oid；objid表示依赖对象的OID（从属对象），classid表示依赖对象所在系统表的oid。例如，create view v1 as select * from t1，则查询依赖关系的时候，objid为v1的oid，refobjid为t1的oid。pg_depend和pg_shdepend中关于deptype字段的含义有所差异，区别如下：

pg_depend中deptype字段有如下几种情况：

2. 常见场景

2.1 案例一：删除表时报错：cannot drop table test because other objects depend on it

tddb=# create table t1  (a int, b serial) distribute by hash(a);
NOTICE:  CREATE TABLE will create implicit sequence "t1_b_seq" for serial column "t1.b"
CREATE TABLE
tddb=# create table t2  (a int, b int default nextval('t1_b_seq')) distribute by hash(a);
CREATE TABLE
tddb=# drop table t1;
ERROR:  cannot drop table t1 because other objects depend on it
DETAIL:  default for table t2 column b depends on sequence t1_b_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

该例中，创建t1表后，隐式创建了sequence，然后创建t2表的时候，引用了该sequence，然后删除t1表的时候，由于会级联删除sequence，但是该sequence被其他对象依赖，因此导致该报错。通过报错信息的DETAIL可以判断，如不需保留t2，可通过DROP CASCADE的方式级联删除，如需保留该表和该sequence，可以通过如下方式解除依赖关系：

tddb=# drop table t1;
ERROR:  cannot drop table t1 because other objects depend on it
DETAIL:  default for table t2 column b depends on sequence t1_b_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
tddb=# 
tddb=# alter sequence t1_b_seq owned by none;
ALTER SEQUENCE
tddb=# 
tddb=# drop table t1;
DROP TABLE
tddb=# 

这样，drop t1的时候就不会级联删除sequence，t2表得到保留。

2.2. 案例二：删除用户时报错：role "%s" cannot be dropped because some objects depend on it

postgres=# drop user jerry;
ERROR:  role "jerry" cannot be dropped because some objects depend on it
DETAIL:  privileges for database td
3 objects in database td

根据报错DETAIL信息，是td库下存在该视图的依赖，因此，首先找到该用户的oid，然后去td库下查看pg_depend中是否存在依赖关系：

td=# select usename,usesysid from pg_user where usename = 'jerry';
 usename |  usesysid  
---------+------------
 jerry   | 2147488711
(1 row)

td=# select * from pg_depend where refobjid = 2147488711;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype 
---------+-------+----------+------------+----------+-------------+---------
(0 rows)

查询到pg_depend视图为空，继续查询pg_shdepend视图，可以看到如下记录：

td=# select * from pg_shdepend where refobjid = 2147488711;
    dbid    | classid |   objid    | objsubid | refclassid |  refobjid  | deptype | objfile 
------------+---------+------------+----------+------------+------------+---------+---------
      16023 |    2615 | 2147488713 |        0 |       1260 | 2147488711 | o       | 
          0 |    1262 | 2147488438 |        0 |       1260 | 2147488711 | a       | 
 2147488438 |    1259 | 2147488726 |        0 |       1260 | 2147488711 | a       | 
 2147488438 |    1259 | 2147488731 |        0 |       1260 | 2147488711 | a       | 
 2147488438 |    2615 | 2147488725 |        0 |       1260 | 2147488711 | o       | 
(5 rows)

说明有5个对象依赖该用户，且根据deptype可以看到，该用户是2个对象的属主，另外3个对象是权限相关的依赖。

以第一条记录为例，说明如何根据pg_shdepend中的内容找到依赖对象：具体思路为：

（1）根据dbid找到依赖对象所在的database

（2）在目标database下，根据classid找到依赖对象所在的系统表

（3）在第二步查到的系统表中，根据objid找到依赖对象

td=# select * from pg_database where oid = 16023;
 datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility | datacl | datfrozenxid64 
----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+--------+----------------
 postgres |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         16018 | 0            |          1663 | ORA              |        |             24
(1 row)

td=# 
td=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "l00500122".
postgres=# 
postgres=# select * from pg_class where oid = 2615;
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relp
ersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid |     relacl     | reloptions | relreplident | relfrozenxid64 
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+-------------+-----
-----------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+----------------+----------+--------------+----------------+------------+--------------+----------------
 pg_namespace |           11 |   11668 |         0 |       10 |     0 |       15739 |             0 |        1 |        15 |             1 |             0 |             0 |             0 |           0 |              0 |            0 | t           | f           | p   
           | r       |        6 |         0 | t          | f          | f           | f              | f              | 0        | f                | f              | n        | 0            | {=r/l00500122} |            | n            |           1980
(1 row)

postgres=# 
postgres=# select * from pg_namespace where oid = 2147488713;
 nspname |  nspowner  | nsptimeline | nspacl | permspace | usedspace 
---------+------------+-------------+--------+-----------+-----------
 jerry   | 2147488711 |           0 |        |        -1 |         0
(1 row)

根据上述操作在postgres库下找到了依赖对象是一个schema，schema名为jerry，其属主是jerry用户，如果需要保留此schema，需要修改该schema的属主：

postgres=# alter schema jerry owner to dbadmin;
ALTER SCHEMA

继续处理第2行记录，仍按上述处理步骤处理（dbid为0说明此系统为全局表，在任意库下查询都可以）：

td=# select * from pg_class where oid = 1262;
   relname   | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relcudescrelid | relcudescidx | relhasindex | relisshared | relpe
rsistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclusterkey | relrowmovement | parttype | relfrozenxid |     relacl     | reloptions | relreplident | relfrozenxid64 
-------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+---------------+-------------+----------------+--------------+-------------+-------------+------
----------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------+------------------+----------------+----------+--------------+----------------+------------+--------------+----------------
 pg_database |           11 |    1248 |         0 |       10 |     0 |           0 |          1664 |        1 |         3 |             1 |             0 |             0 |             0 |           0 |              0 |            0 | t           | t           | p    
          | r       |       14 |         0 | t          | f          | f           | f              | f              | 0        | f                | f              | n        | 0            | {=r/l00500122} |            | n            |           1980
(1 row)

td=# 
td=# select * from pg_database where oid = 2147488438;
 datname | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility |                          datacl                           | datfrozenxid64 
---------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+------------------+-----------------------------------------------------------+----------------
 td      |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         16018 | 24           |          1663 | ORA              | {=Tc/l00500122,l00500122=CTc/l00500122,jerry=C/l00500122} |             24
(1 row)

deptype为a，说明与权限相关，重点关注acl（datacl）列，发现jerry用户有C权限（即create权限），需要回收此权限从而消除依赖：

td=# revoke create on database td from jerry;
REVOKE

按照上述步骤依次处理第3-5行依赖，处理完成后，可以成功删除该用户。