Обсуждение: 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 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