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

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.

Categories

DDL, System catalogue

See also

pg_shdepend