内容

pg_depend

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

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

pg_depend 在 PostgreSQL 7.3 中与 pg_constraint 一起添加，取代了 pg_relcheck。

重复条目

pg_depend 可能包含重复条目。这并不被认为是错误；例如，请参阅此 pgsql-hackers 线程。

按 PostgreSQL 版本定义

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

更改历史记录

示例

创建具有依赖关系的扩展

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.

分类

DDL，系统目录

另请参阅

pg_shdepend，pg_describe_object()

反馈

提交任何关于 "pg_depend" 的评论、建议或更正 此处。