Object Dependencies, DROP CASCADE Risks, Extensions, and Namespace Hygiene

Learning outcomes

Schema changes are not isolated text edits. A table can be referenced by views, foreign keys, defaults, functions, indexes, triggers, publications, extension-owned objects, and other database structures. PostgreSQL tracks many of these relationships so it can reject a drop that would leave broken objects. That protection is powerful only if operators respect the dependency evidence instead of reflexively appending CASCADE.

This lesson creates a disposable dependency graph, shows the exact difference between RESTRICT and CASCADE, queries dependency metadata, and then inspects extension membership. The destructive demonstration stays inside throwaway objects and uses transactions where rollback provides a safe rehearsal.

1. Dependency tracking is database integrity for schema objects

When PostgreSQL parses object definitions, it records many dependencies. A view depends on referenced relations/columns. A foreign key constraint depends on both sides of the relationship. A column default can depend on a sequence. An extension owns a set of member objects. These relationships let the server answer a crucial question: if this object disappears, what else becomes invalid?

Most DROP commands default to RESTRICT behavior. If dependents exist, PostgreSQL refuses the drop and describes at least some blocking dependents. That error is not an inconvenience to suppress; it is a change-impact report.

2. Build a disposable dependency graph

CREATE SCHEMA ch03_dep_lab;CREATE TABLE ch03_dep_lab.customers (    customer_id bigint PRIMARY KEY,    name text NOT NULL);CREATE TABLE ch03_dep_lab.orders (    order_id bigint PRIMARY KEY,    customer_id bigint NOT NULL        REFERENCES ch03_dep_lab.customers(customer_id),    amount numeric(12,2) NOT NULL);CREATE VIEW ch03_dep_lab.customer_order_totals ASSELECT c.customer_id, c.name,       count(o.order_id) AS order_count,       coalesce(sum(o.amount),0) AS total_amountFROM ch03_dep_lab.customers AS cLEFT JOIN ch03_dep_lab.orders AS o USING (customer_id)GROUP BY c.customer_id, c.name;

The graph now includes table-to-constraint relationships and a view that depends on both source tables. PostgreSQL has enough metadata to stop you from dropping a referenced table silently.

3. RESTRICT first: make the server tell you the impact

DROP TABLE ch03_dep_lab.customers RESTRICT;

The expected result is an error explaining that other objects depend on the table. The exact DETAIL lines can vary with server version and graph details. Read them. Do not assert that the error lists every transitive dependency in the exact order your change process cares about.

4. CASCADE is recursive authority

CASCADE tells PostgreSQL to remove dependent objects as necessary, recursively. It does not necessarily drop whole parent objects when only a child constraint depends on the target; PostgreSQL removes what the dependency graph requires. But that can still include views, constraints, functions, or other objects you did not intend to remove.

Rehearse CASCADE inside a transaction and inspect the changed catalog before rollback:

BEGIN;DROP TABLE ch03_dep_lab.customers CASCADE;SELECT to_regclass('ch03_dep_lab.customers') AS customers_after_drop,       to_regclass('ch03_dep_lab.orders') AS orders_after_drop,       to_regclass('ch03_dep_lab.customer_order_totals') AS view_after_drop;\d ch03_dep_lab.ordersROLLBACK;SELECT to_regclass('ch03_dep_lab.customers') AS customers_after_rollback,       to_regclass('ch03_dep_lab.customer_order_totals') AS view_after_rollback;

PostgreSQL transactional DDL makes this rehearsal possible for many schema changes. It is not a universal substitute for backups or staging: external effects, extension code, huge locks, rewrite costs, and operational side effects still require planning.

5. Inspect dependency metadata without pretending raw pg_depend is simple

pg_depend records dependencies among database-local objects. pg_shdepend handles dependencies involving cluster-wide shared objects such as roles/tablespaces. The catalog stores object class OIDs, object OIDs, subobject IDs, reference object IDs, and dependency types. Raw rows are hard to interpret safely, so use helper functions such as pg_describe_object() where appropriate.

SELECT d.deptype,       pg_catalog.pg_describe_object(d.classid, d.objid, d.objsubid) AS dependent_object,       pg_catalog.pg_describe_object(d.refclassid, d.refobjid, d.refobjsubid) AS referenced_objectFROM pg_catalog.pg_depend AS dWHERE d.refobjid = 'ch03_dep_lab.customers'::regclassORDER BY d.deptype, dependent_object;

This is PostgreSQL-specific diagnostic metadata. Dependency types have specific documented meanings; do not invent meaning from a one-letter code without checking the target-major documentation.

6. Dependency does not mean “application impact is fully known”

PostgreSQL tracks database-object dependencies it knows structurally. It cannot automatically know that an external BI dashboard, ORM migration, ETL job, shell script, or application string constructs SELECT * FROM app.work_orders. A clean database dependency graph is therefore necessary but not sufficient for change-impact analysis.

Production removal requires both layers:

• Database graph: views, constraints, functions, extension membership, generated/default expressions, etc.
• External consumers: application code, reports, contracts, CDC/replication consumers, jobs, operational runbooks.

7. Extension membership is lifecycle ownership, not just schema placement

An extension installed with CREATE EXTENSION has a row in pg_extension. Objects belonging to that extension are marked in dependency metadata. The extension's extnamespace identifies a principal schema for its exported objects, but an extension is not simply “everything in that schema,” and extension names themselves are not schema-qualified.

SELECT e.extname,       e.extversion,       e.extowner::regrole AS owner,       e.extnamespace::regnamespace AS main_schema,       e.extrelocatableFROM pg_catalog.pg_extension AS eORDER BY e.extname;

Chapter 01 optionally installed pg_trgm in the extensions schema if it was available. Every PostgreSQL database also normally has plpgsql installed as an extension. Do not drop either just to learn dependency semantics.

8. List extension-owned objects safely

Extension membership is represented by dependency type e referencing the pg_extension row. Use descriptions so the result is understandable:

SELECT e.extname,       pg_catalog.pg_describe_object(d.classid, d.objid, d.objsubid) AS member_objectFROM pg_catalog.pg_extension AS eJOIN pg_catalog.pg_depend AS d  ON d.refclassid = 'pg_catalog.pg_extension'::regclass AND d.refobjid = e.oid AND d.deptype = 'e'WHERE e.extname IN ('plpgsql','pg_trgm')ORDER BY e.extname, member_objectLIMIT 40;

If pg_trgm is not installed, only plpgsql rows will appear. That is expected. Optional contrib/package availability is distribution-dependent.

9. Why DROP EXTENSION can be wider than expected

DROP EXTENSION name removes extension member objects. If other user objects depend on those members, RESTRICT (the default) can block the extension drop. DROP EXTENSION ... CASCADE can recursively remove those dependent user objects too. That is precisely why “the extension is unused” must be proven rather than assumed from application code.

A safe production extension-removal process inventories extension members, queries database dependencies, checks external consumers, takes a tested recovery path, rehearses in staging, and then executes an explicit reviewed change. Never demonstrate extension deletion on the course's required plpgsql installation.

10. Deliberately wrong approach: add CASCADE until the command works

When PostgreSQL says “cannot drop ... because other objects depend on it,” an impatient operator may append CASCADE. The command succeeds and the incident begins later when a view or constraint is missing. This is equivalent to treating the server's impact warning as an obstacle rather than evidence.

The repair is an impact checklist:

1. Identify the exact object by schema-qualified/regclass identity.
2. Attempt or simulate RESTRICT in a safe environment.
3. Inspect database dependency metadata and definitions.
4. Identify external consumers the catalog cannot know.
5. Decide whether dependents should be migrated, recreated, or intentionally removed.
6. Define rollback/restore and lock expectations.
7. Only then use CASCADE when the reviewed desired change truly includes those dependents.

11. Hands-on lab: dependency impact report

1. Create ch03_dep_lab and its objects.
2. Run DROP ... RESTRICT and save the error DETAIL as evidence.
3. Query pg_depend with pg_describe_object().
4. Run the CASCADE inside a transaction, inventory remaining objects, then ROLLBACK.
5. Inspect installed extensions and list member objects for plpgsql and optionally pg_trgm.
6. Write a two-column impact report: PostgreSQL-known dependencies vs external dependencies PostgreSQL cannot prove.
7. After verification, explicitly drop the disposable schema.

-- The schema was created only for this lesson and its contents are now known.DROP SCHEMA ch03_dep_lab CASCADE;

12. Production judgment and next bridge

Schema change is graph change. Treat dependency errors as design feedback, rehearse destructive DDL in disposable/staging environments, and pair catalog evidence with code/application ownership. Extensions introduce an additional lifecycle boundary: upgrading or removing one can affect many member and dependent objects.

Lesson 5 combines namespaces, roles, ownership, privileges, search paths, and default privileges into a multi-team ServiceHub design that is testable as an access matrix.

Authoritative references

• PostgreSQL 18 — Dependency Tracking
• PostgreSQL 18 — pg_depend
• PostgreSQL 18 — pg_shdepend
• PostgreSQL 18 — pg_extension
• PostgreSQL 18 — DROP EXTENSION
• PostgreSQL 18 — Packaging Related Objects into an Extension