GaussDB(DWS)对象依赖案例集锦
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字段有如下几种情况:
- DEPENDENCY_NORMAL (n):依赖对象可以在不影响被引用对象的时候删除。被引用对象删除时需指定CASCADE才能删除,这种情况下依赖的对象也会被删除。
- DEPENDENCY_AUTO (a):依赖对象可以和被引用对象分别删除,也可以在被引用对象删除时自动被删除。例如:一个表上的约束是该表上的自动依赖关系,因此如果删除了表,它也会被删除。
- DEPENDENCY_INTERNAL (i):从属对象作为被引用对象的一部分创建,是内部实现的一部分,从属对象不能直接删除。删除被引用对象时会自动删除从属对象。例如,创建表之后,会同时创建一个同名的类,删除表后,该同名类也会自动被删除。
- DEPENDENCY_EXTENSION (e):依赖对象作为被依赖对象extension的一个成员。
- DEPENDENCY_PIN (p):没有依赖对象。这种类型用来标志系统本身依赖于被引用对象,因此绝对不能删除。此种情况下objid为0。
pg_shdepend中deptype字段有如下几种情况:
- SHARED_DEPENDENCY_OWNER (o):被引用对象是依赖对象的所有者。
- SHARED_DEPENDENCY_ACL (a):在依赖对象的ACL(访问控制列表,也就是权限列表)中提到被引用的对象(必须是一个角色)。不会为对象的所有者创建SHARED_DEPENDENCY_ACL,因为所有者将具有SHARED_DEPENDENCY_OWNER记录。
- SHARED_DEPENDENCY_PIN (p):没有依赖对象。这类记录标识系统自身依赖于被依赖对象,因此这种对象绝对不能被删除。此类型的记录只能被initdb创建,依赖对象的字段都为0。
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行依赖,处理完成后,可以成功删除该用户。
- 点赞
- 收藏
- 关注作者
评论(0)