PostgreSQL 55006 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 55006: Object in Use — Causes, Fixes, and Prevention

What Is Error 55006?

PostgreSQL error code 55006 (object_in_use) occurs when you attempt to perform an operation on a database object — such as dropping a database, tablespace, or index — while that object is still actively being used by another session or process. PostgreSQL raises this error intentionally as a safety mechanism to prevent data corruption. The most classic example is running DROP DATABASE while active client connections still exist on that database.

Top 3 Causes

1. Dropping a Database with Active Connections

This is by far the most common trigger. When you issue DROP DATABASE, PostgreSQL checks for any live connections and refuses to proceed if any exist — even idle ones from connection pools or monitoring tools.

-- Check who is connected to the target database
SELECT pid, usename, application_name, state
FROM pg_stat_activity
WHERE datname = 'my_database';

-- Terminate all connections to the database
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'my_database'
  AND pid <> pg_backend_pid();

-- Now drop the database safely
DROP DATABASE my_database;

-- PostgreSQL 13+: use WITH (FORCE) to auto-terminate connections
DROP DATABASE my_database WITH (FORCE);

2. Dropping a Non-Empty Tablespace

A tablespace cannot be dropped if it still contains tables, indexes, or any other objects. Even a single leftover index will trigger error 55006.

-- Find objects still using the tablespace
SELECT schemaname, tablename
FROM pg_tables
WHERE tablespace = 'my_tablespace';

SELECT indexname
FROM pg_indexes
WHERE tablespace = 'my_tablespace';

-- Move remaining objects to the default tablespace
ALTER TABLE my_schema.my_table SET TABLESPACE pg_default;
ALTER INDEX my_schema.my_index SET TABLESPACE pg_default;

-- Now it's safe to drop
DROP TABLESPACE my_tablespace;

3. DDL Conflicts with Ongoing Maintenance Operations

Running DROP INDEX or ALTER TABLE while a REINDEX CONCURRENTLY or VACUUM FULL is in progress on the same object can trigger lock conflicts closely related to 55006. These maintenance commands hold internal locks that block competing DDL statements.

-- Check for running maintenance queries and lock waits
SELECT pid, query, state, wait_event_type, wait_event,
       now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Safely cancel (not terminate) a blocking maintenance query
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query ILIKE '%REINDEX%'
   OR query ILIKE '%VACUUM%';

Quick Fix Summary

Situation	Fix
Active connections on DB	`pg_terminate_backend()` then `DROP DATABASE`
PostgreSQL 13+	`DROP DATABASE name WITH (FORCE)`
Non-empty tablespace	Move objects with `ALTER TABLE/INDEX SET TABLESPACE`
Maintenance lock conflict	Use `pg_cancel_backend()` or wait for completion

Prevention Tips

1. Block new connections before dropping a database.
Use REVOKE CONNECT to prevent new sessions from connecting, then drain existing ones before executing the drop.

-- Block new connections
REVOKE CONNECT ON DATABASE my_database FROM PUBLIC;

-- Terminate existing sessions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'my_database'
  AND pid <> pg_backend_pid();

-- Safely drop
DROP DATABASE my_database;

2. Set lock_timeout to avoid indefinite blocking.
Configure a session-level or transaction-level lock timeout so that DDL commands fail fast instead of hanging when they encounter an in-use object.

-- Set a 5-second lock timeout for the current session
SET lock_timeout = '5s';

-- Or apply it locally within a transaction
BEGIN;
SET LOCAL lock_timeout = '3s';
DROP INDEX my_index;
COMMIT;

Related Errors

55000 (object_not_in_prerequisite_state): Object is not in the right state for the requested operation.
55P03 (lock_not_available): Lock could not be acquired immediately when using NOWAIT.
40P01 (deadlock_detected): Multiple sessions waiting on each other's locks, often occurring in the same high-concurrency scenarios as 55006.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community