"Matheus Alcantara" <matheusssilv97@gmail.com> writes:
> On Thu Feb 26, 2026 at 3:47 PM -03, Tom Lane wrote:
>> 3. It's making virtual generated columns behave (even more)
>> differently from stored generated columns. I think the
>> general plan has been to make them act as alike as possible.
>>
>> So what would comport better with the behavior of stored columns
>> is to show the expression's value. I agree with you that
>> calculating that in the error path is a no-go, but haven't we
>> computed it earlier? Or could we do so, if there are constraints
>> to be checked?
> Thanks for the feedback. After investigating the code a bit, I found
> that IIUC virtual column values are actually never computed and stored
> separately, they're computed by expanding the expression wherever the
> column is referenced.
Correct: rather than storing them, we recalculate the expression
whenever the column's value is demanded. But what I'm suggesting is
that we ought to calculate the value during INSERT/UPDATE as well,
even though it will not get written to disk. It'd be useful to do
that for the purposes of this error message. But I think we ought to
do it even when there are no constraints, because that ensures that
it's *possible* to calculate the value, and that there are not for
instance overflow problems. As things stand, it's possible to create
a row that cannot be fetched:
regression=# create table foo (f1 int, f2 int generated always as (f1 * 1000000));
CREATE TABLE
regression=# insert into foo values(1);
INSERT 0 1
regression=# table foo;
f1 | f2
----+---------
1 | 1000000
(1 row)
regression=# insert into foo values(1000000);
INSERT 0 1
regression=# table foo;
ERROR: integer out of range
That may or may not be per spec, but I submit that it's extremely
unhelpful and unfriendly, as well as being an undesirable discrepancy
from the behavior of a stored generated column.
Of course, calculating the value at insertion time won't completely
prevent such problems: if the expression is less immutable than
advertised, we could still fail at readout time. But that's pretty
clearly a case of user misfeasance. Index expressions that aren't
really immutable cause worse problems than this, so I don't have a
problem with saying "you broke it, you get to keep both pieces".
Perhaps someone will argue that their expression is too expensive for
it to be okay to calculate at insertion. But if it's that expensive,
why in the world did they make the column virtual rather than stored?
In short, I think we ought to compute these values during
INSERT/UPDATE, even though we're not going to write them to disk.
And then they'd be available to display in this error message.
regards, tom lane
