docs/reference/rules/adding-not-null-field.md
Diagnostic Category: lint/safety/addingNotNullField
Since: vnext
[!NOTE] This rule is recommended. A diagnostic error will appear when linting your code.
Sources:
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.
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.
-- 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;
{
"linter": {
"rules": {
"safety": {
"addingNotNullField": "error"
}
}
}
}