PostgreSQL 18 支持虚拟生成列（Virtual Generated Columns）

PostgreSQL 18 支持虚拟生成列（Virtual Generated Columns）

这个 patch 为 PostgreSQL 引入了虚拟生成列（Virtual Generated Columns）的功能。与现有的存储生成列（Stored Generated Columns）不同，虚拟生成列在读取时计算（类似于视图），而不是在写入时计算（类似于物化视图）。虚拟生成列的语法为：

... GENERATED ALWAYS AS (...) VIRTUAL

其中，VIRTUAL 是默认选项，与许多其他 SQL 产品保持一致（SQL 标准未对此进行规定）。虚拟生成列在元组中存储为 NULL 值，以节省空间并避免因完全缺失列而导致的潜在问题。

• Virtual generated columns, 类似视图, 读时计算, 不消耗存储.
• stored generated columns, 类似物化视图, 写时计算, 并消耗存储.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b

Virtual generated columns  

author  Peter Eisentraut <[email protected]>   
Fri, 7 Feb 2025 08:09:34 +0000 (09:09 +0100)  
committer Peter Eisentraut <[email protected]>   
Fri, 7 Feb 2025 08:46:59 +0000 (09:46 +0100)  
commit  83ea6c54025bea67bcd4949a6d58d3fc11c3e21b  
tree  5a5d13a9f27cd08958d821656086dd1c054516f5  tree  
parent  cbc127917e04a978a788b8bc9d35a70244396d5b  commit | diff  
Virtual generated columns  

This adds a new variant of generated columns that are computed on read
(like a view, unlike the existing stored generated columns, which are  
computed on write, like a materialized view).  

The syntax for the column definition is  

    ... GENERATED ALWAYS AS (...) VIRTUAL  

and VIRTUAL is also optional.  VIRTUAL is the default rather than  
STORED to match various other SQL products.  (The SQL standard makes  
no specification about this, but it also doesn't know about VIRTUAL or  
STORED.)  (Also, virtual views are the default, rather than  
materialized views.)  

Virtual generated columns are stored in tuples as null values.  (A  
very early version of this patch had the ambition to not store them at  
all.  But so much stuff breaks or gets confused if you have tuples  
where a column in the middle is completely missing.  This is a  
compromise, and it still saves space over being forced to use stored  
generated columns.  If we ever find a way to improve this, a bit of  
pg_upgrade cleverness could allow for upgrades to a newer scheme.)  

The capabilities and restrictions of virtual generated columns are  
mostly the same as for stored generated columns.  In some cases, this  
patch keeps virtual generated columns more restricted than they might  
technically need to be, to keep the two kinds consistent.  Some of  
that could maybe be relaxed later after separate careful  
considerations.  

Some functionality that is currently not supported, but could possibly  
be added as incremental features, some easier than others:  

- index on or using a virtual column  
- hence also no unique constraints on virtual columns  
- extended statistics on virtual columns  
- foreign-key constraints on virtual columns  
- not-null constraints on virtual columns (check constraints are supported)  
- ALTER TABLE / DROP EXPRESSION  
- virtual column cannot have domain type  
- virtual columns are not supported in logical replication  

The tests in generated_virtual.sql have been copied over from  
generated_stored.sql with the keyword replaced.  This way we can make  
sure the behavior is mostly aligned, and the differences can be  
visible.  Some tests for currently not supported features are  
currently commented out.  

Reviewed-by: Jian He <[email protected]>  
Reviewed-by: Dean Rasheed <[email protected]>  
Tested-by: Shlok Kyal <[email protected]>  
Discussion: https://www.postgresql.org/message-id/flat/[email protected]  

例子

CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) VIRTUAL);  
   6 CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);  
   7 SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_virtual_tests' ORDER BY 1, 2;  
   8  table_name | column_name | column_default | is_nullable | is_generated | generation_expression   
   9 ------------+-------------+----------------+-------------+--------------+-----------------------  
  10  gtest0     | a           |                | NO          | NEVER        |   
  11  gtest0     | b           |                | YES         | ALWAYS       | 55  
  12  gtest1     | a           |                | NO          | NEVER        |   
  13  gtest1     | b           |                | YES         | ALWAYS       | (a * 2)  
  14 (4 rows)  


-- generation expression must be immutable  
  60 CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) VIRTUAL);  
  61 ERROR:  generation expression is not immutable  
  62 -- ... but be sure that the immutability test is accurate  

  47 -- a whole-row var is a self-reference on steroids, so disallow that too  
  48 CREATE TABLE gtest_err_2c (a int PRIMARY KEY,  
  49     b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) VIRTUAL);  
  50 ERROR:  cannot use whole-row variable in column generation expression  
  51 LINE 2:     b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) VIRT...  
  52                                                  ^  
  53 DETAIL:  This would cause the generated column to depend on its own value.  


CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL, c int GENERATED ALWAYS AS (b * 3) VIRTUAL);  
  43 ERROR:  cannot use generated column "b"in column generation expression  
  44 LINE 1: ...YS AS (a * 2) VIRTUAL, c int GENERATED ALWAYS AS (b * 3) VIR...  
  45                                                              ^  
  46 DETAIL:  A generated column cannot reference another generated column.