How to fix Cannot detach partition in use in PostgreSQL

PostgreSQLADVANCEDHIGHUpdated Dec 29, 2025· 151 views

PostgreSQL prevents detaching a partition when active transactions or queries are accessing the partitioned table. This happens because the DETACH operation needs locks that conflict with running queries. Use DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) or wait for transactions to complete.

Quick fix

Fixes 55006

SELECT pid, usename, application_name, state, query, query_start
FROM pg_stat_activity
WHERE datname = current_database()
  AND state != 'idle'
ORDER BY query_start;
  1. Check for active transactions using the partition
  2. Identify locks held on the partitioned table
  3. Terminate blocking transactions (if safe)
  4. Use DETACH PARTITION CONCURRENTLY (PostgreSQL 14+)
postgresql — error
SELECT pid, usename, application_name, state, query, query_start
FROM pg_stat_activity
WHERE datname = current_database()
  AND state != 'idle'
ORDER BY query_start;
Cannot detach partition in use

What this error means

Error 55006 occurs when you attempt to detach a partition from a partitioned table while that partition (or its parent table) is in active use by other transactions or queries. PostgreSQL requires exclusive locks on the partitioned table to safely detach a partition, and these locks conflict with any ongoing read or write operations. This is a safety mechanism to prevent data corruption and ensure consistency. The error message "Cannot detach partition in use" indicates that PostgreSQL cannot acquire the necessary Access Exclusive lock on either the partition being detached (in older versions) or the parent table (in all versions), because other sessions are actively using the table. This commonly occurs in production environments where queries are continuously running against the partitioned table.

Typical symptoms

  • DETACH PARTITION command hangs or times out while waiting for locks
  • Application queries appear to freeze or hang after initiating a DETACH operation
  • Error message "Cannot detach partition in use" when attempting ALTER TABLE DETACH PARTITION
  • Long-running queries or idle transactions block the DETACH operation
  • Database connections accumulate as they wait for the DETACH to complete

Common causes

  1. Active transactions are querying or modifying rows in the partitioned table or specific partition
  2. Idle transactions are holding locks on the partitioned table (even if not actively querying)
  3. Long-running batch jobs or reports are accessing the partition during the detach attempt
  4. Connection pools or ORMs maintain open transactions that hold locks on the table
  5. Prior to PostgreSQL 14, DETACH PARTITION requires Access Exclusive lock on the parent table, blocking all access to all partitions

How to fix "Cannot detach partition in use"

1Check for active transactions using the partition

First, identify which transactions are blocking the DETACH operation:

sql
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.

2Identify locks held on the partitioned table

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

sql
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.

3Terminate blocking transactions (if safe)

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

sql
-- 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.

4Use DETACH PARTITION CONCURRENTLY (PostgreSQL 14+)

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

sql
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.

5Wait for long-running queries to complete (older PostgreSQL versions)

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:

sql
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:

sql
ALTER TABLE parent_table
DETACH PARTITION partition_name;

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

6If DETACH CONCURRENTLY was interrupted, finalize it

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

sql
ALTER TABLE parent_table
DETACH PARTITION partition_name FINALIZE;

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

Advanced notes

PostgreSQL Versions and Locking: Prior to PostgreSQL 14, DETACH PARTITION acquires Access Exclusive lock on both the partition and the parent table, effectively freezing all access to all partitions until the operation completes. This is why "cannot detach partition in use" is such a severe issue in pre-14 deployments. PostgreSQL 14 introduced DETACH PARTITION CONCURRENTLY which uses only SHARE UPDATE EXCLUSIVE lock on the parent, allowing concurrent reads and writes. For production systems with continuous traffic, always upgrade to PostgreSQL 14+ if partition detachment is a regular operation.

Deadlock with DETACH CONCURRENTLY: In rare cases, DETACH PARTITION CONCURRENTLY can cause deadlocks with SERIALIZABLE or REPEATABLE READ transactions due to snapshot consistency checks (WaitForOlderSnapshots). If you encounter persistent deadlocks with concurrent detach, switch temporarily to non-concurrent detach during maintenance windows, or use SERIALIZABLE isolation on the fewest possible transactions.

Partitioned Tables with Foreign Keys: If the partitioned table has a foreign key constraint that references itself (where the partition key is part of the FK), PostgreSQL may prevent detach even if no data violations exist. The workaround is to drop the global FK, detach the partition, then rebuild independent FKs on each partition if needed.

Application Connection Pools: ORM connection pools and database drivers often maintain open transactions automatically. If you cannot identify blocking queries, check your application's connection pool settings. Tools like pgBouncer can help manage connections, and consider setting idle_in_transaction_session_timeout to automatically terminate idle transactions.

Frequently asked questions

What does "55006" mean?
Error 55006 occurs when you attempt to detach a partition from a partitioned table while that partition (or its parent table) is in active use by other transactions or queries. PostgreSQL requires exclusive locks on the partitioned table to safely detach a partition, and these locks conflict with any ongoing read or write operations. This is a safety mechanism to prevent data corruption and ensure consistency.
How do I fix "55006"?
PostgreSQL prevents detaching a partition when active transactions or queries are accessing the partitioned table. This happens because the DETACH operation needs locks that conflict with running queries. Use DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) or wait for transactions to complete.
What causes "55006"?
Active transactions are querying or modifying rows in the partitioned table or specific partition Idle transactions are holding locks on the partitioned table (even if not actively querying) Long-running batch jobs or reports are accessing the partition during the detach attempt
Is the "55006" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading