Adding a stored generated column without long-lived locks

First seen: 2026-03-17 10:31:47+00:00 · Messages: 7 · Participants: 2

Latest Update

2026-06-01 · claude-opus-4-6

Adding a Stored Generated Column Without Long-Lived Locks — May 2026 Summary

Overview

This thread proposes a two-patch approach to allow adding stored generated columns to existing PostgreSQL tables without requiring a long-lived AccessExclusiveLock for the full duration of a table rewrite. The patches introduce new DDL syntax (ALTER COLUMN c ADD GENERATED ALWAYS AS (expr) STORED) and an optimization that skips the table rewrite when a validated CHECK constraint proves existing data already satisfies the expression.

The Problem

Adding a stored generated column currently requires a full table rewrite under AccessExclusiveLock, making the table completely inaccessible for potentially minutes or hours on large tables. No incremental migration path exists today.

Proposed Solution

Patch 1: New DDL Syntax

Introduces ALTER TABLE t ALTER COLUMN c ADD GENERATED ALWAYS AS (expr) STORED to convert an existing column into a stored generated column. By default still performs a rewrite, but provides the foundation for Patch 2.

Key implementation choices:

Patch 2: Constraint-Based Rewrite Skip

If a validated CHECK constraint (c = expr) exists where expr structurally matches the generated column expression and uses a mergejoinable equality operator, the table rewrite is skipped entirely.

Intended Online Migration Pattern

  1. Add nullable column (lightweight lock)
  2. Add trigger to populate column for new/updated rows
  3. Add NOT VALID CHECK constraint
  4. Backfill existing rows in batches
  5. VALIDATE CONSTRAINT (ShareUpdateExclusiveLock only)
  6. ADD GENERATED ALWAYS AS (expr) STORED — skips rewrite
  7. Drop trigger and constraint

First Review: Critical Issues Found

Laurenz Albe provided the first substantive review, identifying several bugs and design gaps:

Critical NULL Handling Bug

The rewrite-skip optimization is unsound with NULL values. Since CHECK constraints treat NULL results as "not violated," a constraint CHECK (col2 = col1) passes even when col2 IS NULL. This means the constraint does NOT prove all existing values match the expression, and the rewrite is incorrectly skipped, leaving stale NULLs in place. The reviewer suggests requiring a NOT NULL constraint on the column before allowing the optimization.

Additional Bugs

Design Consistency

Laurenz argues SET EXPRESSION should also benefit from the rewrite-skip optimization for consistency — if adding a generated expression can skip the rewrite, changing one should too.

Missing Items

Design Decisions

Decision Rationale
STORED keyword required Virtual columns don't need this optimization; explicit about data overwrite
Structural expression matching Conservative but safe; avoids complex semantic equivalence
Mergejoinable operator requirement Ensures proper equality semantics, not user-defined operators
Syntax choice (ADD GENERATED) Mirrors identity column syntax (ADD GENERATED AS IDENTITY)

Current Status

Patch is at v4, rebased against master. First review received with critical correctness issue (NULL handling) that needs resolution. The patch is targeting a future commitfest (proposed for in-person discussion at pgconf.dev) and explicitly not the current release cycle.

History (1 prior analysis)
2026-06-01 · claude-opus-4-6

Design Iteration 2: Major Redesign After pgconf.dev Discussions

The author has returned from pgconf.dev with significant in-person feedback from multiple contributors, leading to a fundamentally redesigned command. This is a substantial pivot in the approach.

Key Design Change: Mandatory Constraint Reference, No Table Rewrite

The new proposed syntax is:

ALTER TABLE t ALTER COLUMN c
  ADD GENERATED ALWAYS AS (expr) STORED USING CONSTRAINT check_name

This is a significant departure from the previous design where:

  • Before: The command would attempt to find a matching constraint opportunistically; if none found, it would perform a table rewrite.
  • Now: The constraint is mandatory and explicitly named. If the constraint doesn't exist, isn't valid, or doesn't match, the command fails rather than falling back to a rewrite.

This means the implementation becomes simpler — there is never a table rewrite path in this command at all.

NULL Correctness Fix: IS NOT DISTINCT FROM

The critical NULL-handling bug (identified by both Laurenz Albe in the mailing list and Staš Kotarac Guček at the conference) is resolved by changing the required constraint form from:

CHECK (c = expr)           -- broken: NULL = x evaluates to NULL, CHECK treats as satisfied

to:

CHECK (c IS NOT DISTINCT FROM (expr))   -- correct: NULL IS NOT DISTINCT FROM NULL → true

This eliminates the soundness hole where NULL values could bypass the equality check.

Automatic Constraint Removal on Success

In the previous design, the user had to manually drop the constraint after migration. Now, upon successful conversion, the named constraint is automatically removed — conceptually "transformed" from a constraint into the generated column's expression. This simplifies the migration workflow from 7 steps to 6.

SET EXPRESSION Optimization Deferred

The author acknowledges Laurenz's point about SET EXPRESSION also benefiting from rewrite-skip but doesn't directly address it. Instead, he notes that users can work around it by adding a column with the new expression, then atomically dropping the old and renaming — all without long-lived AccessExclusiveLock.

In-Person Review Items from Álvaro Herrera

Álvaro reviewed the code at the in-person commitfest and identified concrete implementation items:

  1. Minimize CommandCounterIncrement() calls — one may be unnecessary
  2. Comment on clearing missing values during rewrite — investigate whether this belongs in the rewriting infrastructure rather than each caller
  3. Test interactions with other subcommands in the same ALTER TABLE statement
  4. Syntax confirmed: ADD (not SET) is the right choice, matching ADD GENERATED AS IDENTITY

Documentation Strategy

The author plans to add documentation in the next patch version, and separately proposes a dedicated "online schema migrations" section in the ALTER TABLE docs grouping NOT VALID/VALIDATE, ATTACH PARTITION index reuse, and this new feature together (though that would be a separate patch).