On 26/02/26 19:14, Tom Lane wrote:
>> 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?
>
An addition to this, the expression is evaluated multiple times for
each check constraint that a virtual column have.
> 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.
>
Please see the attached patch where I implement this. I followed a
similar pattern used for stored generated columns with some small
changes. Some special handling for virtual columns was removed but I
still left some comments but I'm not sure if it's really needed.
With this change I think that doc/src/sgml/ddl.sgml should be updated
to mention that a virtual generated column expression is also
evaluated when writing the data, or perhaps we can just remove the
following:
as if it were a normal column. A virtual generated column occupies no
- storage and is computed when it is read. Thus, a virtual generated column
+ storage. Thus, a virtual generated column
is similar to a view and a stored generated column is similar to a
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
