Skip to content

new-column-with-volatile-default (US031)#

Automatic fix is not available.

What it does#

Checks new column with volatile default.

Why not?#

Adding a new column with a volatile default triggers a table rewrite in PostgreSQL. A table rewrite creates a new physical copy of the table and replaces the original, requiring an ACCESS EXCLUSIVE lock for the duration of the rewrite.

This lock blocks all concurrent reads and writes on the table until the rewrite completes, effectively making the table unavailable during the operation and potentially causing downtime for applications concurrently accessing it.

When should you?#

If the table is empty. If the table is not empty but is not being concurrently accessed.

Note#

Unqualified references to known non-volatile built-in functions resolve to pg_catalog. Shadowing built-in functions via search_path is not modeled and may lead to missed diagnostics.

Use instead:#

  1. Add the new column, nullable, without the volatile default.
  2. Set the volatile default for the newly added column.
  3. If the column should be non-nullable:
    • Add a check constraint with: CHECK (column IS NOT NULL) NOT VALID.
    • Backfill the newly added column for all existing rows.
    • Validate the check constraint.
    • Set the column as NOT NULL.
    • Drop the check constraint.
  4. If (3) is not executed, backfill the newly added column for all existing rows.