require-concurrent-detach-partition

What triggers it

An AlterTableStmt command whose subtype is AT_DetachPartition and whose def.PartitionCmd.concurrent is falsy. CONCURRENTLY shows up in the tree as concurrent: true on the PartitionCmd, which is what the rule tests — it does not scan the SQL text for the keyword.

What does not

Anything below the ctx.pgVersion < 14 gate, since DETACH PARTITION CONCURRENTLY did not exist before then and the rule would be recommending invalid syntax. DETACH PARTITION ... CONCURRENTLY itself. DETACH PARTITION ... FINALIZE, which parses to the separate subtype AT_DetachPartitionFinalize and is never matched — verified against the bundled parser, so the completion half of a two-phase detach does not get flagged. ATTACH PARTITION, which is AT_AttachPartition.

Where it applies

Applies to PostgreSQL 14 and later. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

A plain DETACH PARTITION takes ACCESS EXCLUSIVE on the partitioned parent and holds it for the whole operation, which blocks every read and every write against every partition, not just the one being detached. Because ACCESS EXCLUSIVE conflicts with the ACCESS SHARE that a plain SELECT takes, one long-running query in front of it parks the DDL and then everything else parks behind the DDL. The CONCURRENTLY form splits the work so the parent is only locked briefly at each end.

Why it matters

On PG 14+, DETACH PARTITION CONCURRENTLY detaches the partition without blocking concurrent queries. Without CONCURRENTLY, the parent table is locked with ACCESS EXCLUSIVE.

Unsafe, and safe

ALTER TABLE events DETACH PARTITION events_2024;

ALTER TABLE events DETACH PARTITION events_2024 CONCURRENTLY;

Deploy and transaction boundaries

DETACH PARTITION CONCURRENTLY cannot run inside a transaction block, so applying the fix under a runner that wraps each migration file in BEGIN/COMMIT will make the statement fail outright. The concurrent detach also has to be able to reach a point where no transaction still sees the partition attached, and it leaves the partition in a pending state that a follow-up FINALIZE closes if the session is interrupted.

What it assumes

The rule cannot tell how long the detach will take or how busy the parent is — it never reads ctx.tableStats, ctx.activeConnections, or ctx.tableFacts — so a detach on an idle table at 3am is reported the same as one at peak. It also cannot see whether the migration runner will wrap the file in a transaction, which is what decides whether the fixed statement is even legal.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

✗ [MP046] CRITICAL (line 1)
  DETACH PARTITION "events_2024" from "events" without CONCURRENTLY takes ACCESS EXCLUSIVE lock on the parent, blocking all queries.

  Safe alternative:
  -- Use CONCURRENTLY to avoid blocking (PG 14+):
  ALTER TABLE events DETACH PARTITION events_2024 CONCURRENTLY;

  Why: DETACH PARTITION without CONCURRENTLY takes an ACCESS EXCLUSIVE lock on the parent table, blocking all reads and writes until the operation completes. DETACH PARTITION CONCURRENTLY (PG 14+) uses a two-phase approach that only briefly locks the parent.
  Docs: https://migrationpilot.dev/rules/mp046

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP046
ALTER TABLE events DETACH PARTITION events_2024;

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP046: false

# or keep it, and downgrade it
rules:
  MP046:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP001require-concurrent-index-creationCONCURRENTLY for index creation
• MP008no-multi-ddl-transactionDDL should not be wrapped in transactions
• MP004require-lock-timeoutset lock_timeout before DDL