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

举报
Arrow0lf 发表于 2021/04/19 20:06:47 2021/04/19
【摘要】 本文主要简单介绍对象依赖的查看和典型案例

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行依赖,处理完成后,可以成功删除该用户。

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。