constraintMissingNotValid¶

Group safety · Migrations only · Since 0.15.0
Diagnostic lint/constraintMissingNotValid

Sources: inspired by squawk/constraint-missing-not-valid

Description¶

Adding constraints without NOT VALID blocks all reads and writes.

When adding a CHECK or FOREIGN KEY constraint, Postgres must validate all existing rows, which requires a full table scan. This blocks reads and writes for the duration.

Instead, add the constraint with NOT VALID first, then VALIDATE CONSTRAINT in a separate transaction. This allows reads and writes to continue while validation happens.

Examples¶

Invalid¶

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);

code-block.sql:1:1 lint/constraintMissingNotValid ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Adding a constraint without NOT VALID will block reads and writes while validating existing rows.

  > 1 │ ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Add the constraint as NOT VALID in one transaction, then run VALIDATE CONSTRAINT in a separate transaction.

Valid¶

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;

How to configure¶

{
  "linter": {
    "rules": {
      "constraintMissingNotValid": "error"
    }
  }
}

How to suppress¶

Suppress this diagnostic with a comment:

-- pgls-ignore constraintMissingNotValid