Adding a stored generated column without long-lived locks

Hi,

I recently needed to add a stored generated column to a table of
nontrivial size, and realized that currently there is no way to do that
without rewriting the table under an AccessExclusiveLock.

One way I think this could be achieved:

- allow turning an existing column into a stored generated column, by
default doing a table rewrite using the new stored column expression

- when doing the above, try to detect the presence of a check constraint
which proves that the contents of the column already match its defined
expression, and in that case skip the rewrite

This would open up a path to add such a column (GENERATED ALWAYS AS
(expr) STORED) without long-lived locks:

- add column c, nullable
- add trigger to set c = expr for new/updated rows
- add constraint check (c = expr) NOT VALID
- backfill the table at the appropriate pace
- VALIDATE the constraint
- alter the column c to be GENERATED ALWAYS AS (expr) STORED, which
would skip the rewrite because of the valid check constraint on c
- clean up the trigger and the constraint

To this effect, I started prototyping an alter table command

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

The syntax seemed like a good fit because it's similar to the command to
change a column to be GENERATED AS IDENTITY, but I didn't spend a whole
lot of thought on the exact syntax yet.

The attached patches are a first prototype for discussion:

- patch v1-0001: add the command
- patch v1-0002: detect the check constraint and skip the rewrite

The check constraint must be of the form

(c = <expr>)

where `=` is a mergejoinable operator for the type c.

The <expr> in the constraint and in the column definition are matched
structurally, so they must match exactly.

Before spending more time on this, I wanted to bring this up for
discussion and to gauge interest in the idea.

Looking forward to your feedback!

Alberto

--
Alberto Piai
Sensational AG
Zürich, Switzerland