How to fix Cannot detach partition in use in PostgreSQL

First, identify which transactions are blocking the DETACH operation:

SELECT pid, usename, application_name, state, query, query_start
FROM pg_stat_activity
WHERE datname = current_database()
  AND state != 'idle'
ORDER BY query_start;

This query shows all non-idle connections. Look for any queries accessing your partitioned table. The query_start column shows how long the query has been running.

Check which locks are currently held and which are being waited for:

SELECT l.pid, l.mode, l.granted, a.usename, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'your_table'::regclass
ORDER BY l.granted DESC;

Replace your_table with your partitioned table name. Locks with granted = false are waiting for access, while granted = true are holding locks.

If you identify transactions that can be safely terminated, kill them:

-- For a single blocking session
SELECT pg_terminate_backend(12345);  -- Replace 12345 with the PID

-- For all idle transactions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = current_database()
  AND state = 'idle in transaction'
  AND pid != pg_backend_pid();

Be very cautious: terminating transactions can cause data loss or application errors. Only terminate sessions you are certain can be safely killed.

If you are running PostgreSQL 14 or later, use the CONCURRENTLY option to minimize lock duration:

ALTER TABLE parent_table
DETACH PARTITION partition_name CONCURRENTLY;

This operation uses SHARE UPDATE EXCLUSIVE lock instead of Access Exclusive, allowing other transactions to continue reading and writing to the parent table (including other partitions). CONCURRENTLY cannot be used in a transaction block—run it as a standalone statement. It also cannot be used if the partitioned table has a default partition.

The CONCURRENTLY detach runs in two transactions:
1. First transaction marks the partition as detached in the catalog
2. Second transaction waits for older snapshots to clear and completes the detach

This ensures consistency while minimizing blocking.

If running PostgreSQL < 14, you must wait for all active transactions to complete before DETACH can proceed. During low-traffic periods:

1. Monitor activity to confirm no long-running queries:

SELECT COUNT(*) FROM pg_stat_activity
WHERE datname = current_database() AND state != 'idle';

2. Once the count is 0 or only idle transactions remain, attempt DETACH:

ALTER TABLE parent_table
DETACH PARTITION partition_name;

3. Consider draining application connections before the DETACH to reduce wait time.

If a concurrent detach was cancelled or crashed mid-operation, complete it with:

ALTER TABLE parent_table
DETACH PARTITION partition_name FINALIZE;

This finishes the second transaction of a previously started concurrent detach operation.