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
On Tue Mar 17, 2026 at 5:31 PM +07, Alberto Piai wrote:
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.
[...]
To this effect, I started prototyping an alter table command
We currently have a way to change the expression of generated columns
(SET EXPRESSION) and a way to turn a generated column into a regular one
(DROP EXPRESSION). The new command would fit nicely and provide the
missing piece of functionality: turning an existing column into a
generated column.
A few thoughts:
- since this is specifically useful for *stored* generated columns (to
have a way to avoid a rewrite while the table is locked), I would
stick to my first proposal and require that STORED is specified
explicitly. It would still be possible to remove this requirement and
expand to virtual generated columns, should the need for this arise in
the future (I just don't see the use case right now).
- realizing that this is the opposite operation of DROP EXPRESSION gave
me a clue about how to support partitioning/inheritance.
AT_DropExpression can be applied only to the whole inheritance tree at
once (see 8bf6ec3ba3a44448817af47a080587f3b71bee08 and the associated
discussion at /messages/by-id/2793383.1672944799@sss.pgh.pa.us),
it refuses to be applied to either the parent table ONLY, or directly
to partitions. This new command should work the same way.
- while researching the above, I stumbled upon a restriction of current
DROP EXPRESSION: it doesn't seem to be possible to apply it to
partition trees deeper than just one level (parent / child tables).
This is probably an oversight, but to avoid feature-creeping this
patch, I made the new command act the same way (see test case). I'll
try to address this separately.
- I added some note in the commit message to clarify why I added the new
command to AT_PASS_SET_EXPRESSION, since this wasn't clear enough in
my first mail/patch.
- I am not particularly attached to the syntax. Alternatives that would
come to mind would be:
SET GENERATED ALWAYS AS (expr) STORED
or to match the two existing commands:
ADD EXPRESSION (expr) STORED
As I said above, I think the explicit STORED is necessary. It would be
nice if the command would make it crystal clear to the user that it
implies rewriting the table, i.e. overwriting existing data. (To me,
all three forms are clear enough, especially considering that by this
point I would have already typed ALTER twice :-))
The attached v2 patches take care of the points above. They are again
split in two commits for ease of review.
Looking forward to any comment / feedback!
Alberto
PS: A note about the timing of this mail, as I am just getting
acquainted with all of this. I am aware that we're super short of a
feature freeze, and this thread is by no means an attempt to push for
this to go in now, nor to steal brain bandwidth from more important
active threads. I just thought it's OK to put the patches and the mails
out there as I make progress, even if it's just to bring this up and
revisit at a later point in time. Let me know if instead it would be
better to sit on my thoughts until a more appropriate time in the
release cycle.
--
Alberto Piai
Sensational AG
Zürich, Switzerland
On Tue Apr 7, 2026 at 5:02 PM +08, Alberto Piai wrote:
On Tue Mar 17, 2026 at 5:31 PM +07, Alberto Piai wrote:
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.
Attached v3, just a rebase onto current master.
Regards,
Alberto
--
Alberto Piai
Sensational AG
Zürich, Switzerland