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

网友投稿 802 2022-05-29

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

1.查看对象依赖关系

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

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

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后用户能够重新删除。

EI企业智能 Gauss AP 数据仓库服务 GaussDB(DWS)

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:FPGA从Xilinx的7系列学起(2)
下一篇:Jenkins部署GitHub上的前端项目
相关文章