warn-fk-lock-both-tables

What triggers it

The AT_AddConstraint subtype of ALTER TABLE where the constraint's contype is CONSTR_FOREIGN — any ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES ..., regardless of whether NOT VALID is present.

What does not

Any AlterTableCmd whose subtype isn't AT_AddConstraint, an AT_AddConstraint whose contype isn't CONSTR_FOREIGN (adding a CHECK or UNIQUE constraint, for instance), and any AlterTableStmt with no cmds array at all.

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

SHARE ROW EXCLUSIVE on the table gaining the constraint, and SHARE on the referenced table, both held for the full validation scan, which reads every row in the referencing table. Both locks block writes to their respective table for that whole duration.

Why it matters

ALTER TABLE ADD CONSTRAINT FOREIGN KEY acquires SHARE ROW EXCLUSIVE lock on both the table with the FK column AND the referenced table. This blocks writes to both tables simultaneously, doubling the blast radius.

The operation, and the mitigation

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id);

SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users (id) NOT VALID;
RESET lock_timeout;
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

This operation is irreversible, so there is no syntax that makes it safe. The second block is what care looks like — and MigrationPilot still flags it. The mitigation is process: confirm nothing reads the object, keep a way back, and do it in a window where you can watch.

Deploy and transaction boundaries

Add the constraint with NOT VALID, then run VALIDATE CONSTRAINT in its own, separate transaction. Combining them into one statement runs the validation scan under the same locks that are already blocking both tables, so splitting only helps if the two statements land in different transactions.

What it assumes

The rule fires whether or not NOT VALID is present, since the dual-table locking happens the moment the constraint is added either way — but the long validation-scan duration it's really warning about only applies when validation runs inline as part of that same statement.

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

⚠ [MP069] WARNING (line 1)
  FK "fk_user" locks both "orders" AND "users" simultaneously (SHARE ROW EXCLUSIVE). Set lock_timeout on both tables to fail fast.

  Safe alternative:
  -- Set a short lock_timeout to prevent cascading locks:
  SET lock_timeout = '3s';
  ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (...) REFERENCES users (...) NOT VALID;
  RESET lock_timeout;
  
  -- Then validate separately (weaker lock):
  ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

  Why: ALTER TABLE ADD CONSTRAINT ... FOREIGN KEY acquires SHARE ROW EXCLUSIVE lock on both the table with the FK column AND the referenced table. This blocks writes to both tables simultaneously, doubling the blast radius. If either table is high-traffic, the lock wait can cascade to all queries on both tables.
  Docs: https://migrationpilot.dev/rules/mp069

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 MP069
ALTER TABLE orders ADD CONSTRAINT fk_user

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

rules:
  MP069: false

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