warn-concurrent-index-invalid

What triggers it

CREATE INDEX CONCURRENTLY with an explicit index name and no IF NOT EXISTS, where no earlier statement in the migration is a DROP INDEX IF EXISTS naming that same index — checked by scanning ctx.allStatements before the current statement for the text drop index, if exists, and the index name together.

What does not

Non-concurrent CREATE INDEX, a concurrent create with IF NOT EXISTS, one preceded earlier in the migration by a matching DROP INDEX IF EXISTS, or one whose index name is — or will become — owned by a UNIQUE or PRIMARY KEY constraint. adoptedLaterInMigration or constraintOwning finding an owner stands the rule down, since DROP INDEX ... IF EXISTS on a constraint-backed index is rejected by PostgreSQL outright.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

No lock is named — the danger isn't blocking, it's that a failed CREATE INDEX CONCURRENTLY leaves an invalid index behind, and retrying without dropping it first fails with "relation already exists," turning a transient failure into a stuck migration.

Why it matters

If CREATE INDEX CONCURRENTLY fails, it leaves behind an INVALID index that slows writes but is never used for queries. Retrying without first dropping the invalid index fails with "relation already exists". The exception is an index a UNIQUE or PRIMARY KEY constraint owns: PostgreSQL refuses to drop that one at all, so its retry path is REINDEX INDEX CONCURRENTLY, or dropping the constraint first.

Unsafe, and safe

CREATE INDEX CONCURRENTLY idx_email ON users (email);

DROP INDEX CONCURRENTLY IF EXISTS idx_email;
CREATE INDEX CONCURRENTLY idx_email ON users (email);

-- If a UNIQUE or PRIMARY KEY constraint owns the index, the drop is refused.
-- Rebuild it in place instead:
REINDEX INDEX CONCURRENTLY users_email_key;

What it assumes

The rule assumes a retry will happen and that the migration file is the unit of idempotency — it only searches earlier statements in the same file for the matching drop, so a drop issued from a separate cleanup script or an earlier migration file wouldn't be seen.

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

⚠ [MP070] WARNING (line 1)
  CREATE INDEX CONCURRENTLY "idx_email" on "users" without a preceding DROP INDEX IF EXISTS. If this fails and is retried, the stale invalid index will block creation.

  Safe alternative:
  -- Drop any stale invalid index before creating:
  DROP INDEX CONCURRENTLY IF EXISTS idx_email;
  CREATE INDEX CONCURRENTLY idx_email ON users (...);
  
  -- If a UNIQUE or PRIMARY KEY constraint owns idx_email, the drop is refused
  -- outright. Rebuild it in place instead:
  -- REINDEX INDEX CONCURRENTLY idx_email;

  Why: If CREATE INDEX CONCURRENTLY fails (due to deadlock, unique violation, or timeout), it leaves behind an INVALID index that is never used for queries but still slows down writes. Retrying without first dropping the invalid index fails with "relation already exists" — or worse, with IF NOT EXISTS, silently succeeds without rebuilding it. Always precede CONCURRENTLY index creation with DROP INDEX IF EXISTS to handle retries safely. The exception is an index a UNIQUE or PRIMARY KEY constraint owns: PostgreSQL refuses to drop that one at all, so its retry path is REINDEX INDEX CONCURRENTLY, or dropping the constraint first.
  Docs: https://migrationpilot.dev/rules/mp070

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 MP070
CREATE INDEX CONCURRENTLY idx_email ON users (email);

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

rules:
  MP070: false

# or keep it, and downgrade it
rules:
  MP070:
    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

• MP097ban-drop-constraint-backing-indexthe drop this rule stands down for
• MP021require-concurrent-reindexREINDEX without CONCURRENTLY blocks writes
• MP009require-drop-index-concurrentlyDROP INDEX without CONCURRENTLY