PostgreSQL Object In Use (SQLSTATE 55006)

PostgreSQL raises SQLSTATE 55006 (object_in_use) when a command attempts to modify or remove an object that is actively in use by another session. The most common manifestation is:

ERROR:  database "mydb" is being accessed by other users
DETAIL:  There are N other sessions using the database.

or for tablespaces:

ERROR:  tablespace "pg_default" cannot be dropped because some objects depend on it

This error belongs to error class 55 — Object Not In Prerequisite State — which groups errors where the database object exists but is in a state that prevents the requested operation from proceeding.

What This Error Means

Error class 55 in PostgreSQL covers situations where an object is in the wrong state for the requested operation, rather than being missing or inaccessible due to permissions. SQLSTATE 55006 specifically means the object is too busy or too active — it is currently held open by one or more sessions, and PostgreSQL refuses to proceed with a destructive operation while those connections remain.

PostgreSQL enforces this at the server level to protect data integrity. For example, DROP DATABASE requires an exclusive lock on the database being dropped. If any other backend has an open connection to that database — even an idle one — the lock cannot be acquired and PostgreSQL aborts the command immediately with this error.

Importantly, this error does not leave your current transaction in a failed state. Unlike errors in class 40 (transaction rollback) or 23 (integrity constraint violation), a 55006 error does not abort the surrounding transaction. The command simply fails, and the session remains fully operational. You can retry the command once the blocking condition is resolved.

Common Causes

1. Dropping a database with active connections. The most frequent trigger. Any session connected to the target database — including long-running queries, idle sessions, connection pool workers, or monitoring agents — will block DROP DATABASE.

2. Dropping a tablespace while objects still reside in it. A tablespace cannot be dropped if any database objects (tables, indexes, sequences) are still stored in it, or if any session holds a reference to it during an active transaction.

3. CREATE DATABASE or ALTER DATABASE operations against a template database. PostgreSQL prevents modifications to a database while it is being used as a template and another session has it open.

4. Replication or maintenance tooling holding connections open. Logical replication workers, pg_dump, vacuumdb, pg_upgrade, and connection poolers like PgBouncer can all maintain persistent connections that trigger this error unexpectedly.

How to Fix object_in_use

1. Identify all sessions connected to the target database and terminate them before retrying:

-- List all connections to the target database
SELECT pid, usename, application_name, state, query_start, query
FROM pg_stat_activity
WHERE datname = 'mydb'
  AND pid <> pg_backend_pid();

-- Terminate them (requires superuser or pg_signal_backend)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb'
  AND pid <> pg_backend_pid();

2. Drop the database after terminating connections. On PostgreSQL 13 and later, you can use WITH (FORCE) to have PostgreSQL terminate remaining connections automatically:

-- PostgreSQL 13+
DROP DATABASE mydb WITH (FORCE);

On earlier versions, you must terminate connections manually before issuing DROP DATABASE.

3. Pause or reconfigure connection poolers (PgBouncer, pgpool-II, RDS Proxy) before attempting DROP DATABASE. Connection poolers maintain persistent backend connections and will continuously reconnect, so simply terminating backends is not enough — disable the pool entry for that database first.

4. For tablespace removal, first move or drop all objects residing in the tablespace, then drop it:

-- Find all tables in a specific tablespace
SELECT schemaname, tablename
FROM pg_tables
WHERE tablespace = 'old_tablespace';

-- Move a table to a different tablespace
ALTER TABLE mytable SET TABLESPACE pg_default;

-- Drop the now-empty tablespace
DROP TABLESPACE old_tablespace;

5. For template databases, set datistemplate = false before dropping or making structural changes:

UPDATE pg_database SET datistemplate = false WHERE datname = 'template_db';
DROP DATABASE template_db;

Additional Information

• The WITH (FORCE) option for DROP DATABASE was introduced in PostgreSQL 13. On earlier versions there is no built-in way to atomically terminate connections and drop in a single statement.
• Related SQLSTATE codes in class 55 include 55000 (object_not_in_prerequisite_state) and 55P03 (lock_not_available). The 55P03 error is raised when LOCK TABLE ... NOWAIT or SELECT ... FOR UPDATE NOWAIT cannot immediately acquire a lock.
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, JDBC) surface this as a ProgrammingError or PSQLException with the SQLSTATE attached. You can match on sqlstate == "55006" for targeted retry logic.
• On managed cloud databases (Amazon RDS, Cloud SQL, Azure Database for PostgreSQL), the service control plane may hold a persistent system connection to each database. This can make DROP DATABASE appear to always block even after you terminate all visible user sessions. Consult the cloud provider's documentation for the recommended procedure.

Frequently Asked Questions

Why does my DROP DATABASE fail even though I see no active connections in pg_stat_activity? There may be sessions in pg_stat_activity that are not connected to the specific database, but a connection pooler or autovacuum worker may hold one invisibly. Also check for replication connections via pg_stat_replication. On managed cloud platforms, internal system accounts are often not visible to regular superusers.

Does 55006 mean my transaction was rolled back? No. Unlike errors in class 40 (transaction rollback), SQLSTATE 55006 does not abort the surrounding transaction. The failing command is simply not executed, and your session remains open and usable. You can fix the blocking condition and retry within the same or a new transaction.

Can I drop a database while connected to it? No. PostgreSQL will not allow you to drop the database you are currently connected to. You must connect to a different database (commonly postgres or template1) before issuing DROP DATABASE.

Is there a way to prevent this error in automated deployment scripts? Yes. Before running DROP DATABASE, explicitly terminate all connections in your script using pg_terminate_backend, and if targeting PostgreSQL 13+, use DROP DATABASE name WITH (FORCE) as a safety net. In CI/CD pipelines, also ensure connection poolers targeting that database are drained or paused before the drop step runs.