pg_depend
是一个 系统目录 表,用于记录数据库对象之间的依赖关系,以确保删除一个对象会同时删除依赖对象,或者根据对象之间的关系定义方式阻止删除操作。
pg_depend
与 pg_constraint
一起在 PostgreSQL 7.3 中引入,取代了 pg_relcheck
。
重复条目
中可能包含重复条目。这不被视为一个 bug;例如,请参阅此 pg_depend
pgsql-hackers
讨论串。
按 PostgreSQL 版本定义
pg_depend (PostgreSQL 19)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 18)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 17)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 16)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 15)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 14)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 13)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 12)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 11)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 10)
Table "pg_catalog.pg_depend" Column | Type | Collation | Nullable | Default -------------+---------+-----------+----------+--------- classid | oid | | not null | objid | oid | | not null | objsubid | integer | | not null | refclassid | oid | | not null | refobjid | oid | | not null | refobjsubid | integer | | not null | deptype | "char" | | not null | Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 9.6)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 9.5)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 9.4)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 9.3)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 9.2)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 9.1)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 9.0)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 8.4)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 8.3)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
pg_depend (PostgreSQL 8.2)
Table "pg_catalog.pg_depend" Column | Type | Modifiers -------------+---------+----------- classid | oid | not null objid | oid | not null objsubid | integer | not null refclassid | oid | not null refobjid | oid | not null refobjsubid | integer | not null deptype | "char" | not null Indexes: "pg_depend_depender_index" btree (classid, objid, objsubid) "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid)
文档:pg_depend
变更历史
- PostgreSQL 17
- 扩展成员的依赖类型现在已被列出(提交 e5bc9454)
- PostgreSQL 7.3
- 已添加(提交 7c6df91d)
示例
创建带有依赖关系的扩展
postgres=# CREATE EXTENSION hstore_plperl CASCADE; NOTICE: installing required extension "hstore" NOTICE: installing required extension "plperl" CREATE EXTENSION
以下查询列出了为 hstore_plperl
添加到 pg_depend
中的条目
postgres=# SELECT * FROM pg_depend WHERE objid = (SELECT oid FROM pg_extension WHERE extname='hstore_plperl'); classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype ---------+-------+----------+------------+----------+-------------+--------- 3079 | 16585 | 0 | 2615 | 2200 | 0 | n 3079 | 16585 | 0 | 3079 | 16453 | 0 | n 3079 | 16585 | 0 | 3079 | 16580 | 0 | n (3 rows)
以下查询将列出为 hstore_plperl
创建的扩展依赖关系
postgres=# SELECT e.extname, de.extname AS dep_extname FROM pg_extension e JOIN pg_depend d ON d.objid = e.oid JOIN pg_class c ON d.refclassid = c.oid JOIN pg_extension de ON d.refobjid = de.oid WHERE e.extname = 'hstore_plperl'; extname | dep_extname ---------------+------------- hstore_plperl | plperl hstore_plperl | hstore (2 rows)
列出的两个扩展现在都不能在没有 CASCADE
选项的情况下删除
postgres=# DROP EXTENSION hstore; ERROR: cannot drop extension hstore because other objects depend on it DETAIL: extension hstore_plperl depends on extension hstore HINT: Use DROP ... CASCADE to drop the dependent objects too.
参考资料
- PostgreSQL 文档: pg_depend
有用链接
- 依赖链 - 2018 年 4 月 pgdba.org 的文章