pg_depend

一个记录数据库对象之间依赖关系的系统表

pg_depend 是一个系统目录表，用于记录数据库对象之间的依赖关系，以确保删除一个对象会同时删除依赖对象，或者根据对象之间的关系定义方式阻止删除操作。

pg_depend 与 pg_constraint 一起在 PostgreSQL 7.3 中引入，取代了 pg_relcheck。

重复条目

pg_depend 中可能包含重复条目。这不被视为一个 bug；例如，请参阅此 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 的文章

另请参阅

pg_shdepend, pg_describe_object()

反馈

提交任何关于“pg_depend”的评论、建议或更正，请在此处 提交。

版本图表

实用概述

有用链接

咖啡

目录

pg_depend

重复条目

按 PostgreSQL 版本定义

pg_depend (PostgreSQL 19)

pg_depend (PostgreSQL 18)

pg_depend (PostgreSQL 17)

pg_depend (PostgreSQL 16)

pg_depend (PostgreSQL 15)

pg_depend (PostgreSQL 14)

pg_depend (PostgreSQL 13)

pg_depend (PostgreSQL 12)

pg_depend (PostgreSQL 11)

pg_depend (PostgreSQL 10)

pg_depend (PostgreSQL 9.6)

pg_depend (PostgreSQL 9.5)

pg_depend (PostgreSQL 9.4)

pg_depend (PostgreSQL 9.3)

pg_depend (PostgreSQL 9.2)

pg_depend (PostgreSQL 9.1)

pg_depend (PostgreSQL 9.0)

pg_depend (PostgreSQL 8.4)

pg_depend (PostgreSQL 8.3)

pg_depend (PostgreSQL 8.2)

变更历史

示例

参考资料

有用链接

分类

另请参阅

反馈