addingNotNullField

Diagnostic Category: lint/safety/addingNotNullField

Since: vnext

Sources: - Inspired from: squawk/adding-not-null-field

Description

Setting a column NOT NULL blocks reads while the table is scanned.

In PostgreSQL versions before 11, adding a NOT NULL constraint to an existing column requires a full table scan to verify that all existing rows satisfy the constraint. This operation takes an ACCESS EXCLUSIVE lock, blocking all reads and writes.

In PostgreSQL 11+, this operation is much faster as it can skip the full table scan for newly added columns with default values.

Instead of using SET NOT NULL, consider using a CHECK constraint with NOT VALID, then validating it in a separate transaction. This allows reads and writes to continue.

Examples

Invalid

ALTER TABLE "core_recipe" ALTER COLUMN "foo" SET NOT NULL;

code-block.sql:1:1 lint/safety/addingNotNullField ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Setting a column NOT NULL blocks reads while the table is scanned.

  > 1 │ ALTER TABLE "core_recipe" ALTER COLUMN "foo" SET NOT NULL;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i This operation requires an ACCESS EXCLUSIVE lock and a full table scan to verify all rows.

  i Use a CHECK constraint with NOT VALID instead, then validate it in a separate transaction.

Valid

-- First add a CHECK constraint as NOT VALID
ALTER TABLE "core_recipe" ADD CONSTRAINT foo_not_null CHECK (foo IS NOT NULL) NOT VALID;
-- Then validate it in a separate transaction
ALTER TABLE "core_recipe" VALIDATE CONSTRAINT foo_not_null;

How to configure

{
  "linter": {
    "rules": {
      "safety": {
        "addingNotNullField": "error"
      }
    }
  }
}