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
