The dependency ladder

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.

The dependent objects are tracked using the fields classid, objid and objsubid.

The field 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 pg_class.

The field objid is the dependend object’s oid stored in the classid’s relation.

The field 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 refclassid, refobjid and 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 refobjid.

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 public.foo.

The object listed in pg_type is the data type which is implicitly associated with any table in the database. The dependency type is i, internal. 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 id. 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 _RETURN.

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 _RETURN in pg_rewrite twice. 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.

Wrap up

The view 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 CASCADE clause.

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.

Urban ladder, copyright Federico Campoli