detach-partition-non-concurrent
errorDETACH PARTITION without CONCURRENTLY
Why it's unsafe
DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent table and the partition, blocking all access to the whole partitioned table while it runs.
Safe rewrite
Use ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14+; it takes only SHARE UPDATE EXCLUSIVE on the parent, so reads and writes keep working). It must run outside a transaction block.
Example
Try it in the playground →Unsafe
ALTER TABLE measurement DETACH PARTITION measurement_y2020;Safe
ALTER TABLE measurement DETACH PARTITION measurement_y2020 CONCURRENTLY;