| From: | Alberto Piai <alberto(dot)piai(at)gmail(dot)com> |
|---|---|
| To: | Alberto Piai <alberto(dot)piai(at)gmail(dot)com>, Xiaoxuan Li <xiaoxuan(dot)li(dot)dev(at)gmail(dot)com> |
| Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com>, Álvaro Herrera <alvherre(at)kurilemu(dot)de>, pgsql-hackers(at)postgresql(dot)org |
| Subject: | Re: Adding a stored generated column without long-lived locks |
| Date: | 2026-11-18 16:40:47 |
| Message-ID: | DMYHEIXCJVHK.28MCAUDKHOREA@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi all,
On Sat Nov 14, 2026 at 4:50 PM CET, Alberto Piai wrote:
> 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.
Thanks again Xiaoxuan for testing and catching this.
One of the reasons I went with this design for my proposal was that I
was trying very hard to avoid creating a new state in which a column
could be. The intention was to make it possible to add a new stored
generated column without rewrites-under-lock, and without having to
reason about new states ("column is being backfilled"; "column is
half-virtual half-stored";) which would have existed in alternative
implementations I considered.
Now as you point out: the presence of atthasmissing/attmissingval _is_ a
new state. After the migration, we'd have a column which has
attgenerated='s' *and* atthasmissing/attmissingval.
That is not possible to ever reach nowadays by adding a new stored
generated column via CREATE TABLE or ALTER TABLE ... ADD COLUMN.
I prefer to avoid introducing the extra state, because I cannot be
reasonably sure that it is handled correctly everywhere.
I have verified, for example, that pg_dump --binary-upgrade emits a call
to binary_upgrade_set_missing_value() even if the column is generated.
So the new state (generated *and* atthasmissing) would be preserved
across a binary upgrade.
But that is just one aspect of it, there might be many cases I did not
think about. I prefer to be conservative about this, unless someone else
with more knowledge about postgres internals than me can show that this
would indeed be safe.
So here's v13, which:
- bails if the column has atthasmissing, because in that case we can't
be sure the values have been physically stored in each row:
ERROR: cannot convert column "b" to generated
DETAIL: Cannot ensure that column "b" of relation "t" has been completely backfilled.
HINT: Drop the column and create it again without a DEFAULT value.
- documents in alter_table.sgml that the column must be initially
defined without a DEFAULT value:
Note: the column column_name must be defined without a DEFAULT value
I am aware that this is not entirely accurate: the column could be
defined with a DEFAULT value, but if it's empty at that point then it
would never gain atthasmissing. Or it could be rewritten with REPACK or
another command which would clear the attributes on rewrite. But given
the use case for this command, this seemed good enough to me.
It is also not ideal that a user who oversees the "Note:" in the
documentation could do the whole work of backfilling the column, only to
find out at the very end of the migration that they used a DEFAULT
value. But this really cannot be detected earlier.
Happy to hear your thoughts about this,
Alberto
--
Alberto Piai
Sensational AG
Zürich, Switzerland
| Attachment | Content-Type | Size |
|---|---|---|
| v13-0001-Add-SQL-level-datum-equality-tests.patch | text/plain | 8.8 KB |
| v13-0002-Support-changing-a-column-into-a-stored-generate.patch | text/plain | 74.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Lukas Fittl | 2026-11-18 17:20:55 | Re: [BUG / PATCH] PG19: VACUUM misestimates reltuples (often 0) after on-access VM setting |
| Previous Message | Dean Rasheed | 2026-11-18 16:23:40 | Re: MERGE loses UPDATE transition rows when moving rows between partitions |