Re: Virtual generated columns

On Fri, Nov 29, 2024 at 7:14 PM Peter Eisentraut <peter@eisentraut.org> wrote:
> Here is a new patch version, with several updates.

> - Added support for ALTER TABLE ... SET EXPRESSION.

When using ALTER TABLE to set expression for virtual generated
columns, we don't enforce a rewrite, which means we don't have the
opportunity to check whether the new values for these columns could
cause an underflow or overflow.  For instance,

create table t (a int, b int generated always as (a) virtual);
insert into t values (2147483647);

# alter table t alter column b set expression as (a * 2);
ALTER TABLE

# select * from t;
ERROR:  integer out of range

The same thing could occur with INSERT.  As we don't compute virtual
generated columns on write, we may end up inserting values that cause
underflow or overflow for these columns.

create table t1 (a int, b int generated always as (a * 2) virtual);
insert into t1 values (2147483647);

# select * from t1;
ERROR:  integer out of range

I'm not sure if this is expected or not, so I just wanted to point it
out.

Thanks
Richard