One of the things that distinguish PostgreSQL from other database systems is the way the dependencies between database objects are managed.
PostgreSQL do not allow any invalid object like views or functions. Therefore any action that can invalidate dependent objects results in an error or it’s propagated to the dependent objects.
The mechanism could be confusing at first sight. However the implementation is brilliant and gives great flexibility.
For example let’s create a table and a view defined as a select from one of the table’s fields.
db_test=> CREATE TABLE foo (id serial, bar character varying(30) ); CREATE TABLE db_test=> CREATE VIEW foobar AS SELECT bar FROM foo; CREATE VIEW
Adding a new column to the table is possible with no problems.
db_test=> ALTER TABLE foo ADD COLUMN bar2 boolean; ALTER TABLE
However, the moment when we try to drop the column
bar’, the dependency system prevents that.
db_test=> ALTER TABLE foo DROP COLUMN bar; ERROR: cannot drop table foo column bar because other objects depend on it DETAIL: view foobar depends on table foo column bar HINT: Use DROP ... CASCADE to drop the dependent objects too.
One table fits (almost) all
In PostgreSQL the dependencies for the database objects are tracked in the system table
pg_depend. There is also another system table
pg_shdepend which tracks the dependencies for objects that are shared across all the databases present in the cluster.
The table’s structure is very simple and well explained in the PostgreSQL documentation.
|classid||oid||pg_class.oid||The OID of the system catalog the dependent object is in|
|objid||oid||any OID column||The OID of the specific dependent object|
|objsubid||int4||For a table column, this is the column number (the objid and classid refer to the table itself). For all other object types, this column is zero.|
|refclassid||oid||pg_class.oid||The OID of the system catalog the referenced object is in|
|refobjid||oid||any OID column||The OID of the specific referenced object|
|refobjsubid||int4||For a table column, this is the column number (the refobjid and refclassid refer to the table itself). For all other object types, this column is zero.|
|deptype||char||A code defining the specific semantics of this dependency relationship; see text|
The dependent objects are tracked using the fields
classid is the oid of the system relation stored in
pg_class where the dependend object is listed.
For example a schema will have
classid set to
pg_namespace. However for a table or a view the
classid will be
pg_class which is recursively listed in
objid is the dependend object’s oid stored in the
objsubid is used only if the dependency involves a table’s column where it stores the column number. Otherwise the value is set to zero.
The referenced objects are tracked using the fields
refobjsubid. The fields have the same roles of the dependent’s counterpart.
The last field
deptype is used to store the semantics of the dependency.
DEPENDENCY_NORMAL (n): A normal relationship between separately-created objects. The dependent object can be dropped without affecting the referenced object. The referenced object can only be dropped by specifying CASCADE, in which case the dependent object is dropped, too. Example: a table column has a normal dependency on its data type.
DEPENDENCY_AUTO (a): The dependent object can be dropped separately from the referenced object, and should be automatically dropped (regardless of RESTRICT or CASCADE mode) if the referenced object is dropped. Example: a named constraint on a table is made auto dependent on the table, so that it will go away if the table is dropped.
DEPENDENCY_INTERNAL (i): The dependent object was created as part of creation of the referenced object, and is really just a part of its internal implementation. A DROP of the dependent object will be disallowed outright (we’ll tell the user to issue a DROP against the referenced object, instead). A DROP of the referenced object will be propagated through to drop the dependent object whether CASCADE is specified or not. Example: a trigger that’s created to enforce a foreign-key constraint is made internally dependent on the constraint’s pg_constraint entry.
DEPENDENCY_EXTENSION (e): The dependent object is a member of the extension that is the referenced object (see pg_extension). The dependent object can be dropped only via DROP EXTENSION on the referenced object. Functionally this dependency type acts the same as an internal dependency, but it’s kept separate for clarity and to simplify pg_dump.
DEPENDENCY_AUTO_EXTENSION (x): The dependent object is not a member of the extension that is the referenced object (and so should not be ignored by pg_dump), but cannot function without it and should be dropped when the extension itself is. The dependent object may be dropped on its own as well.
DEPENDENCY_PIN (p): There is no dependent object; this type of entry is a signal that the system itself depends on the referenced object, and so that object must never be deleted. Entries of this type are created only by initdb. The columns for the dependent object contain zeroes.
A needle in an haystack
In order to find the relationship between the table
public.foo and the view
public.foobar we shall query
pg_depend searching for the rows where our table is listed as
db_test=# SELECT classid::regclass, objid, objsubid, refclassid::regclass, refobjid::regclass, refobjsubid, deptype FROM pg_depend WHERE refobjid='public.foo'::regclass ; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype ------------+-------+----------+------------+----------+-------------+--------- pg_type | 16512 | 0 | pg_class | foo | 0 | i pg_attrdef | 16513 | 0 | pg_class | foo | 1 | a pg_class | 16508 | 0 | pg_class | foo | 1 | a pg_rewrite | 16517 | 0 | pg_class | foo | 2 | n (4 rows)
The query returns four dependencies for the table
The object listed in
pg_type is the data type which is implicitly associated with any table in the database. The dependency type is
That means that the data type cannot be dropped autonomously. However if we drop the table
public.foo the action is cascaded to dependent data type whether CASCADE is specified or not.
The object in
pg_attrdef defines the relationship between the sequence
foo_id_seq, created automatically because of the
serial pseudo type, and the default value on field
id. Similarly the object in
pg_class defines the relationship between the sequence
foo_id_seq and the field
Both dependencies are of type
a auto. This kind of dependency allows the drop of the depended object (e.g.
ALTER TABLE foo ALTER COLUMN id DROP DEFAULT;`) and drops the dependent objects if the referenced object is dropped.
However between the sequence
foo_id_seq and the column
id’s default value there is a normal dependency (not displayed in our query though). That means that dropping the sequence is not possible unless the
CASCADE clause is used.
db_test=# SELECT rulename, ev_class::regclass, ev_type, is_instead FROM pg_rewrite WHERE oid=16517; rulename | ev_class | ev_type | is_instead ----------+----------+---------+------------ _RETURN | foobar | 1 | t (1 row)
The relation between the table
foo and the rule in
pg_rewrite is of type normal and is limited only to the field
bar (table’s second column) as stated in
refobjsubid. As mentioned before this prevents to drop of the field unless
CASCADE is used.
The class associated with this specific rule is our view
public.foobar which is not listed explicitly in
pg_depend but related to the table
foo via the rewrite rule
db_test=# SELECT classid::regclass, objid, objsubid, refclassid::regclass, refobjid::regclass, refobjsubid, deptype FROM pg_depend WHERE refobjid='public.foobar'::regclass ; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype ------------+-------+----------+------------+----------+-------------+--------- pg_type | 16516 | 0 | pg_class | foobar | 0 | i pg_rewrite | 16517 | 0 | pg_class | foobar | 0 | i pg_rewrite | 16517 | 0 | pg_class | foobar | 0 | n (3 rows)
When searching for the view
public.foobar we find that it is referenced by the view’s data type and by the rule
The first dependency is of type internal which allows the rule to be dropped implicitly if the view is dropped.
The second dependency is of type normal which prevents the rule to be dropped autonomously without the CASCADE clause.
public.foobar is referenced with normal dependency by the rule
_RETURN. The rule can’t be dropped unless the CASCADE is used.
However, the table
public.foo is referenced with normal dependency by the rule
_RETURN but only on the field
bar. Dropping the field requires the CASCADE in order to propagate the drop to the dependent rule. Because of the normal dependency between the rule and the view, the CASCADE propagates to the entire view.
It’s clear that tracking the dependencies is not a simple task. The way PostgreSQL implements the relationships between the database objects can be confusing at first sight. But when it’s understood then it becomes clear how this simple approach gives great flexibility.
A final remark about the drop. Take extra care when using the
db_test=> ALTER TABLE foo DROP COLUMN bar CASCADE; NOTICE: drop cascades to view foobar ALTER TABLE db_test=# SELECT * FROM foobar; ERROR: relation "foobar" does not exist LINE 1: SELECT * FROM foobar;
In our example if we use
CASCADE to drop the field referenced by the view
foobar the drop propagates to the entire view and not just the column.
Thanks for reading.