addingFieldWithDefault¶

Group safety · Recommended · Migrations only · Since 0.15.0
Diagnostic lint/addingFieldWithDefault

Sources: inspired by squawk/adding-field-with-default

Description¶

Adding a column with a DEFAULT value may lead to a table rewrite while holding an ACCESS EXCLUSIVE lock.

In Postgres versions before 11, adding a column with a DEFAULT value causes a full table rewrite, which holds an ACCESS EXCLUSIVE lock on the table and blocks all reads and writes.

In Postgres 11+, this behavior was optimized for non-volatile defaults. However:

Volatile default values (like random() or custom functions) still cause table rewrites
Generated columns (GENERATED ALWAYS AS) always require table rewrites
Non-volatile defaults are safe in Postgres 11+

Examples¶

Invalid¶

ALTER TABLE "core_recipe" ADD COLUMN "foo" integer DEFAULT 10;

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

  ! Adding a column with a DEFAULT value causes a table rewrite.

  > 1 │ ALTER TABLE "core_recipe" ADD COLUMN "foo" integer DEFAULT 10;
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i This operation requires an ACCESS EXCLUSIVE lock and rewrites the entire table.

  i Add the column without a default, then set the default in a separate statement.

Valid¶

ALTER TABLE "core_recipe" ADD COLUMN "foo" integer;
ALTER TABLE "core_recipe" ALTER COLUMN "foo" SET DEFAULT 10;
-- Then backfill and add NOT NULL constraint if needed

How to configure¶

{
  "linter": {
    "rules": {
      "addingFieldWithDefault": "error"
    }
  }
}

How to suppress¶

Suppress this diagnostic with a comment:

-- pgls-ignore addingFieldWithDefault