Re: Adding a stored generated column without long-lived locks

On Mon, 2026-08-31 at 20:41 +0200, Alberto Piai wrote:
> I liked the = form too (but: there is a "but" coming later :)), I always
> looked at this from the perspective of a user who is intentionally
> running through these steps to perform a very specific migration.
> Assuming they don't make mistakes (or the expression is not nullable, as
> in the simple case of "b = a + 1" where the referenced a is NOT NULL), =
> works fine.
>
> The problem is that it's relatively easy to make a mistake (or a
> malicious user could take advantage of it) whenever the expression is
> nullable. In that case, a row could be added to the table that still
> satisfies the constraint (since CHECK constraints are satisfied when the
> expression evaluates to NULL), the alter table would happily run
> through, and the db would be left in an inconsistent state.
>
> In the case above of "b = a + 1", if a is nullable, b is NOT NULL and a
> row (a, b) with values (null, 42) is inserted, rewriting operations like
> update ... set a = a, or pg_dump/pg_restore would fail.

Embarrassing - I should have seen that during my review.

You are right, it's best to forget about check constraints with = here.

Yours,
Laurenz Albe