GaussDB(DWS)对象依赖案例集锦

    GaussDB(DWS)中，PG_DEPEND系统表记录数据库对象之间的依赖关系。这个信息允许DROP命令找出哪些对象必须由DROP CASCADE删除，或是在DROP RESTRICT的情况下避免删除。PG_SHDEPEND系统表记录数据库对象和共享对象（比如角色）之间的依赖性关系，作用与PG_DEPEND类似，只是PG_SHDEPEND是在集群里面所有的数据库之间共享的：每个数据库集群只有一个PG_SHDEPEND，而不是每个数据库一个。

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，objid表示依赖对象的OID。例如，create view v1 as select * from t1，则查询依赖关系的时候，objid为v1的oid，refobjid为t1的oid。

2. 案例一：删除表时报错：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被其他对象依赖，因此导致该报错。如不需保留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表得到保留。

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

tddb=# drop user usr1;
ERROR:  role "usr1" cannot be dropped because some objects depend on it
DETAIL:  1 object in database postgres

    根据报错内容，是postgres库下存在该视图的依赖，去postgres库下查看：

postgres=# select usename,usesysid from pg_user where usename = 'usr1';
 usename | usesysid 
---------+----------
 usr1    |    24670
(1 row)

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

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

postgres=# select oid,relname from pg_class where oid = 2615;
 oid  |   relname    
------+--------------
 2615 | pg_namespace
(1 row)

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

    查询pg_depend视图为空，继续查询pg_shdepend视图可以看到一条记录，根据classid查到依赖对象所在的系统表，根据objid查到该对象的名字，从上面的结构可以看到，是usr1在postgres库下的同名schema导致用户无法被drop，删除该schema后用户能够重新删除。