Horton

Re: Virtual generated columns

Richard Guo <guofenglinux@gmail.com>

View thread
From: Richard Guo <guofenglinux@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, jian he <jian.universality@gmail.com>, Dean Rasheed <dean.a.rasheed@gmail.com>
Date: 2025-01-08T08:22:47Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Expand virtual generated columns for ALTER COLUMN TYPE

  2. Eliminate code duplication in replace_rte_variables callbacks

  3. Expand virtual generated columns in the planner

  4. Virtual generated columns

  5. Additional tests for stored generated columns

  6. Improve generated_stored test

  7. Fix handling of CREATE DOMAIN with GENERATED constraint syntax

  8. Add pg_constraint rows for not-null constraints

  9. Put generated_stored test objects in a schema

  10. Rename regress test generated to generated_stored

  11. Small code simplification

  12. Remove useless code

  13. Remove useless initializations

  14. doc: Clarify that pg_attrdef also stores generation expressions

  15. Clean out column-level pg_init_privs entries when dropping tables.

  16. Re-implement the ereport() macro using __VA_ARGS__.

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