pg_depend
A system table recording dependencies between database objects
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 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 |
refobjversion | text | C | |
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
refobjectversionadded (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