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

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 https://postgr.es/m/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