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

Hi Xiaoxuan,

thank you for having a look at the patch!

On Fri Nov 13, 2026 at 9:06 PM CET, Xiaoxuan Li wrote:
> Hi Alberto,
>
> v12 (on e405a46c45b, Linux/aarch64, gcc 11, cassert) builds cleanly
> and passes the regression tests, but this new change loses data:
>
>> - clear atthasmissing and attmissingval for consistency with CREATE
>> TABLE: a column that was GENERATED since the beginning can't have
>> those set, so let's be consistent.
>
> A column added with a constant default has no stored value in the rows
> that existed before; they read attmissingval. The constraint is
> validated against those values, and then the command removes them:

Oops, nice catch, and what an embarassing thinko on my side.

The property I'd like to establish here is that a stored generated
column added with this command should be indistinguishable from one that
was defined with CREATE TABLE or ALTER TABLE ... ADD COLUMN.

So when I noticed the different atthasmissing/attmissingval, I thought
I'd just drop those.

Your test case shows an even bigger problem: in the presence of a
previous default value, the CHECK constraint is not sufficient to prove
that the column was completely backfilled with the correct data.

My first thought now would be to have the command bail if the column has
atthasmissing/attmissingval, and document that it must be added without
a DEFAULT value.

The (not so small, but not terrible and maybe unavoidable) downside is
that this can't be detected early, so a user who's doing this migration
and oversees the note in the docs will only notice at the very end, and
will have to start from scratch and do the work of backfilling the data
again.

Or alternatively, I give up on trying to preserve such a property. A
column which was added with a default value and freshly migrated with
this command will have atthasmissing/attmissingval. Since the constraint
has been validated aginst the missingval, we are sure that if/when the
table is eventually rewritten (for example with REPACK), the correct
value will be stored (and atthasmissing/attmissingval will be cleared).

This might allow saving some work in the backfilling phase if most rows
would end up with the default value anyway.

WDYT?

(I am inclined to go for the latter, but I want to spend some more time
thinking about this.)

Regards,

Alberto

--
Alberto Piai
Sensational AG
Zürich, Switzerland