Date: Tue, 17 Mar 2026 11:31:47 +0100
From: Alberto Piai <alberto.piai@gmail.com>
To: pgsql-hackers@postgresql.org
Subject: Adding a stored generated column without long-lived locks
Message-ID: <abkrpUwlGngF4e-d@phidippus.sen.work>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="24dzbv6kpqxe4yje"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/abkrpUwlGngF4e-d%40phidippus.sen.work>
Precedence: bulk


--24dzbv6kpqxe4yje
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

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

--24dzbv6kpqxe4yje
Content-Type: text/x-patch; charset=utf-8
Content-Disposition: attachment;
	filename="v1-0001-Support-changing-a-column-into-a-stored-generated.patch"