cannot drop … because other objects depend on it, measured

ERROR: cannot drop table users because other objects depend on it (SQLSTATE 2BP01, dependent_objects_still_exist) means PostgreSQL has recorded that another object needs the one you are dropping, and it refuses to leave that object broken. The DETAIL lines list every dependent, and the HINT offers DROP ... CASCADE. In our measurements the blockers were views, foreign keys from other tables, functions using the table’s row type, and row-level security policies on other tables — and the fix is to deal with those objects deliberately, not to add CASCADE blindly.

We measured on PostgreSQL 17.11 and 12.22 in containers. The DROP TABLE and CASCADE output, the row-level security behaviour and the dependency queries were identical on both; the DROP COLUMN, DROP ROLE and pg_restore cases were measured on 17.11 only.

What blocks a DROP TABLE?

We built one table, users, with every kind of neighbour we could think of, and dropped it:

drop table users;
-- ERROR:  2BP01: cannot drop table users because other objects depend on it
-- DETAIL:  function f_set() depends on type users
-- function f_param(users) depends on type users
-- constraint orders_user_id_fkey on table orders depends on table users
-- view v_users depends on table users
-- view v_users2 depends on view v_users
-- materialized view mv_users depends on table users
-- policy p_docs on table docs depends on table users
-- HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Every table has a row type of the same name, so a function that returns setof users or takes a users parameter depends on the type, not the table. A row-level security policy on another table (p_docs on docs, with using (owner in (select id from users))) blocks the drop too. Views on views are listed with their own chain (v_users2 depends on view v_users).

What did not block: a trigger on users, and a sequence created OWNED BY users.id. Neither appears in DETAIL; both belong to the table and simply go with it. A PL/pgSQL function that only queries users in its body did not block either — its body is not tracked, so after the drop it failed at call time with 42P01: relation "users" does not exist.

What does CASCADE actually remove?

The same list as in DETAIL, announced only by a NOTICE — plus the objects that belong to the table itself, such as its triggers and the OWNED BY sequence, which no message mentions:

drop table users cascade;
-- NOTICE:  00000: drop cascades to 7 other objects
-- DETAIL:  drop cascades to function f_set()
-- drop cascades to function f_param(users)
-- drop cascades to constraint orders_user_id_fkey on table orders
-- drop cascades to view v_users
-- drop cascades to view v_users2
-- drop cascades to materialized view mv_users
-- drop cascades to policy p_docs on table docs

Afterwards orders and docs still existed: for a foreign key, CASCADE removes the constraint, not the referencing table or its rows. orders.user_id was left as a plain integer column that nothing checks any more — the opposite of what violates foreign key constraint protects you from. Views, the materialized view and both functions were gone entirely.

The policy is the one to worry about. After the cascade, docs still had row-level security enabled but zero policies, so it falls back to deny. A non-owner role with SELECT and INSERT saw 0 of the 2 rows, with no error, and its insert failed with new row violates row-level security policy for table "docs". The table owner, here a non-superuser role, still saw both rows, because row-level security was not forced. On 17.11 and 12.22 alike, reads went silently empty while writes failed.

That is the problem with CASCADE in a migration: the NOTICE is the only record of what disappeared. A migration that passed on a developer database without views can drop a reporting view in production without any error.

Why does DROP COLUMN or DROP CONSTRAINT give the same error?

Dependencies are tracked per column and per constraint, so the same code comes back:

alter table users drop column email;   -- v_users selects email
-- ERROR:  2BP01: cannot drop column email of table users because other objects depend on it
-- DETAIL:  view v_users depends on column email of table users
-- view v_users2 depends on view v_users

alter table users drop column name;    -- no view selects name
-- ALTER TABLE

alter table users drop constraint users_pkey;
-- ERROR:  2BP01: cannot drop constraint users_pkey on table users because other objects depend on it
-- DETAIL:  constraint orders_user_id_fkey on table orders depends on index users_pkey

A foreign key depends on the unique index behind the primary key, which is why DETAIL names the index. drop constraint users_pkey cascade dropped orders_user_id_fkey and nothing else (NOTICE: drop cascades to constraint orders_user_id_fkey on table orders). A view that groups by the primary key and selects other columns depends on that constraint as well; we measured that case in must appear in the GROUP BY clause.

Changing a column’s type under a view is a different error, 0A000: cannot alter type of a column used by a view or rule, with DETAIL: rule _RETURN on view v_users depends on column "email". We cover that one, and the drop-and-recreate fix, in value too long for type character varying.

How do you find the dependents before you drop?

Views are stored as rewrite rules, so a view’s dependency points from its _RETURN rule (in pg_rewrite) to the table. This recursive query follows that chain, including views on views:

with recursive deps as (
  select distinct r.ev_class as oid, 1 as level
  from pg_depend d join pg_rewrite r on r.oid = d.objid
  where d.classid = 'pg_rewrite'::regclass
    and d.refclassid = 'pg_class'::regclass
    and d.refobjid = 'users'::regclass
    and r.ev_class <> 'users'::regclass
  union
  select distinct r.ev_class, deps.level + 1
  from deps
  join pg_depend d on d.refobjid = deps.oid and d.refclassid = 'pg_class'::regclass
  join pg_rewrite r on r.oid = d.objid and d.classid = 'pg_rewrite'::regclass
  where r.ev_class <> deps.oid
)
select oid::regclass as dependent, c.relkind, level
from deps join pg_class c using (oid)
order by level, 1;
--  dependent | relkind | level
-- -----------+---------+-------
--  v_users   | v       |     1
--  v_users2  | v       |     2

For objects that point at the table itself — constraints, policies, view rules — pg_describe_object reads pg_depend for you:

select pg_describe_object(classid, objid, objsubid) as dependent, deptype
from pg_depend
where refclassid = 'pg_class'::regclass and refobjid = 'users'::regclass;
-- constraint orders_user_id_fkey on table orders | n
-- constraint users_pkey on table users           | a
-- policy p_docs on table docs                     | n
-- rule _RETURN on view v_users                   | n
-- type users                                     | i
-- (plus the toast table, and a second _RETURN row for the second column)

Functions that use the row type point at the type, not the table, so they need a second query:

select pg_describe_object(classid, objid, objsubid) as dependent
from pg_depend
where refclassid = 'pg_type'::regclass and refobjid = 'users'::regtype;
-- type users[]
-- function f_set()

The cheapest check of all is the drop itself without CASCADE, inside a transaction you roll back: the error lists every dependent, in the same order the CASCADE notice later reports them.

Why can’t I drop a role, even after DROP OWNED?

Because roles belong to the whole cluster, but ownership and grants live in each database. We gave role app a table and a SELECT grant in two databases, db_a and db_b:

-- connected to db_a
drop role app;
-- ERROR:  2BP01: role "app" cannot be dropped because some objects depend on it
-- DETAIL:  owner of table t_db_a
-- privileges for table g_db_a
-- 2 objects in database db_b

reassign owned by app to owner_new;
drop owned by app;
drop role app;
-- ERROR:  2BP01: role "app" cannot be dropped because some objects depend on it
-- DETAIL:  2 objects in database db_b

From the postgres database the DETAIL only counted: 2 objects in database db_a, 2 objects in database db_b. Only after the same two commands in db_b did drop role app succeed. reassign owned moved t_db_a to owner_new; drop owned then revoked the grant on g_db_a. Order matters: drop owned on its own drops the tables the role owns — in a separate test, the owned table was gone. Ownership in general is in must be owner of table.

Why does pg_restore —clean fail with this error?

--clean emits a plain DROP TABLE for each table in the dump, without CASCADE. When the target database has an object the dump does not know about — we added a view report_v on users — that drop fails:

pg_restore: error: could not execute query: ERROR:  cannot drop table public.users because other objects depend on it
DETAIL:  view public.report_v depends on table public.users
Command was: DROP TABLE public.users;
pg_restore: error: could not execute query: ERROR:  relation "users" already exists
pg_restore: warning: errors ignored on restore: 2

By default pg_restore continued: the old table stayed, the dumped row was loaded into it, the view survived, and the exit code was 1. A restore that reports errors and still exits with a half-old database is worth checking for in scripts: test the exit code, and restore into an empty database or drop the extra objects first.

What we did not measure

DROP SCHEMA, DROP TYPE and DROP EXTENSION, which raise the same code. Generated columns, column defaults calling a function, and SQL functions with a BEGIN ATOMIC body. --exit-on-error and --if-exists in pg_restore. Versions 13 to 16 and 18. Redshift, which has its own implementation.

Sources: PostgreSQL 17 documentation on DROP ROLE (“A role cannot be removed if it is still referenced in any database of the cluster”), read on 22 October 2026. All measurements are our own, on PostgreSQL 17.11 and 12.22 in containers, on 22 October 2026.