pg_depend
pg_depend
is a system catalogue table recording dependencies between database objects, to ensure dropping one object leads to dependent objects being dropped, or the drop operation being blocked, depending on how the relationship between objects is defined.
pg_depend
was added in PostgreSQL 7.3 together with pg_constraint
, replacing pg_relcheck
.
Duplicate entries
It's possible that pg_depend
contains duplicate entries. This is not considered a bug; see e.g. this pgsql-hackers
thread.
Definition by PostgreSQL version
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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: 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)
Documentation: pg_depend
Change history
- PostgreSQL 14
- column
refobjectversion
added (commit cd6f479e)
- column
- PostgreSQL 7.3
- added (commit 7c6df91d)
Examples
Create an extension with dependencies:
postgres=# CREATE EXTENSION hstore_plperl CASCADE; NOTICE: installing required extension "hstore" NOTICE: installing required extension "plperl" CREATE EXTENSION
The following query lists the entries added to pg_depend
for hstore_plperl
:
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)
The following query will list the extension dependencies created for 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)
Neither of the two extensions listed can now be dropped without the CASCADE
option:
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.
References
- PostgreSQL documentation: pg_depend
Useful links
- The dependency ladder - April 2018 article by pgdba.org