require-not-valid-check

Reports a CHECK constraint added to an existing table without NOT VALID, which checks every row while blocking reads and writes.

Category	Default severity	Databases
locking	error	PostgreSQL

What happens

Adding a CHECK constraint makes PostgreSQL scan the whole table to prove that every existing row satisfies it. The scan runs under an ACCESS EXCLUSIVE lock, which blocks every read and write on the table until it finishes.

With NOT VALID, the constraint is added without the scan and applies to new and changed rows right away. VALIDATE CONSTRAINT then checks the existing rows under a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes.

Statements on a table created in the same migration are not reported: the table has no rows yet.

A column added in the same migration. When the check reads a column that was added earlier in the same migration, without a default, every row has NULL in that column. A CHECK constraint passes when its expression is true or NULL, so if the check cannot be false while that column is NULL, no existing row can break it. Then the finding is info: PostgreSQL still reads the whole table once under the lock, but nothing can fail. It stays an error when the check requires the column, as with IS NOT NULL, or when an UPDATE fills the column before the check is added.

Bad

import { MigrationInterface, QueryRunner } from 'typeorm'

export class AddUserAgeCheck1727200000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "users" ADD CONSTRAINT "CHK_users_age" CHECK ("age" >= 0)`)
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // ...
  }
}

Safe

Add the constraint with NOT VALID:

import { MigrationInterface, QueryRunner } from 'typeorm'

export class AddUserAgeCheck1727200000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "users" ADD CONSTRAINT "CHK_users_age" CHECK ("age" >= 0) NOT VALID`,
    )
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // ...
  }
}

Then validate it in a migration deployed in a later release:

import { MigrationInterface, QueryRunner } from 'typeorm'

export class ValidateUserAgeCheck1727300000000 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "users" VALIDATE CONSTRAINT "CHK_users_age"`)
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // ...
  }
}

TypeORM's createCheckConstraint() cannot add NOT VALID, so use queryRunner.query().

With TypeORM's default migrationsTransactionMode: "all", every pending migration runs in one transaction. Then a VALIDATE deployed together with the NOT VALID migration gains nothing (see validate-in-same-transaction). Deploy it in a later release, or use "each".

When to suppress

When the table is small, or the migration runs in a maintenance window.

// preflight safety-assured require-not-valid-check -- plans has 12 rows

References

• PostgreSQL: ALTER TABLE, ADD table_constraint ("Normally, this form will cause a scan of the table to verify that all existing rows in the table satisfy the new constraint. But if the NOT VALID option is used, this potentially-lengthy scan is skipped.")
• PostgreSQL: ALTER TABLE, notes ("validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered")
• PostgreSQL: check constraints ("a check constraint is satisfied if the check expression evaluates to true or the null value")
• orm-preflight lock verification results