作者
digoal
日期
2019-11-15
标签
PostgreSQL , GENERATED column
背景
PostgreSQL 12 提供了GENERATED column的功能，支持自动生成字段的值（基于当前表的当前行的其他列（其他列不能是GENERATED column，也不能是系统列除了tableoid））。 这个值可以是一个表达式，所以表达式的op或者func必须由immutable的函数或者操作符组成。
PG 12只支持store模式，暂时不支持virtual虚拟列，所以会占用空间，未来版本会支持不占用空间（查询时计算）的虚拟列。
limit
- The generation expression can only use immutable functions
- Subqueries or any reference to anything other than the current row are forbidden
- A generation expression cannot reference another generated column
- A generation expression cannot reference a system column, except for the tableoid
- A generated column cannot have a column default or an identity definition
- A generated column cannot be part of a partition key
- Foreign tables can have generated columns
- Generated columns can’t be written explicitly by INSERT or UPDATE, however it’s possible to specify the keyword DEFAULT in the INSERT or UPDATE list
- Access privileges for generated columns follow the normal rules of the column permissions
- Generated columns are updated after the BEFORE trigger is processed, therefore it is not allowed to access generated columns in BEFORE triggers
例子
1、创建表，写时计算age
```
testdb=> CREATE TABLE people(
name text,
birthday date,
age int );
testdb=> WITH year AS (
SELECT ( random() * 100 )::int % 70 AS y
)
INSERT INTO people( name, age, birthday )
SELECT 'Person ' || v, y, current_date - ( y * 365 )
FROM generate_series(1, 1000000 ) v, year;
```
占用空间
```
testdb=> SELECT pg_relation_size( 'people' );
pg_relation_size
52183040
```
2、创建immutable函数，计算age
testdb=> CREATE OR REPLACE FUNCTION
f_person_age( birthday date )
RETURNS int
AS $CODE$
BEGIN
RETURN extract( year FROM CURRENT_DATE )
- extract( year FROM birthday )
+ 1;
END
$CODE$
LANGUAGE plpgsql IMMUTABLE;
3、建表，自动生成age值，age字段作为generated 字段(表达式为immutable函数，使用birthday自动计算age)
testdb=> CREATE TABLE people_gc_stored (
name text,
birthday date,
age int GENERATED ALWAYS AS ( f_person_age( birthday ) ) STORED
);
4、写入people_gc_stored
```
testdb=> INSERT INTO people_gc_stored( name, birthday )
SELECT 'Person ' || v, current_date - v
FROM generate_series(1, 1000000 ) v;
空间占用如下，age字段占用了空间（因为是stored的模式）
testdb=> SELECT pg_relation_size( 'people_gc_stored' );
pg_relation_size
52183040
```
5、测试非immutable函数，导致的generate错误
```
testdb=> CREATE TABLE people_gc_stored (
name text,
birthday date,
age int GENERATED ALWAYS AS (
extract( year FROM CURRENT_DATE )
- extract( year FROM birthday )
+ 1 ) STORED
);
ERROR: generation expression is not immutable
```
6、直接更新generated column的值会导致错误，它是在更新其他字段时才会被更新的
testdb=> UPDATE people_gc_stored SET age = 40;
ERROR: column "age" can only be updated to DEFAULT
DETAIL: Column "age" is a generated column.
7、权限测试，和普通列一样，generated column的权限可以单独赋予
```
testdb=# REVOKE ALL ON people_gc_stored FROM public;
testdb=# GRANT SELECT( name, age ) ON people_gc_stored TO harry;
testdb=> SELECT * FROM luca.people_gc_stored LIMIT 5;
ERROR: permission denied for table people_gc_stored
testdb=> SELECT min( age ), max( age ) FROM luca.people_gc_stored;
min | max
-----|------
1 | 2740
(1 row)
testdb=> SELECT min( birthday ), max( birthday ) FROM luca.people_gc_stored;
ERROR: permission denied for table people_gc_stored
testdb=# REVOKE SELECT ON people_gc_stored FROM harry;
testdb=# GRANT SELECT( name, birthday ) ON people_gc_stored TO harry;
testdb=> SELECT min( birthday ), max( birthday ) FROM luca.people_gc_stored;
min | max
---------------|------------
0720-12-07 BC | 2019-11-03
(1 row)
testdb=> SELECT min( age ), max( age ) FROM luca.people_gc_stored;
ERROR: permission denied for table people_gc_stored
```
参考
https://www.postgresql.org/docs/12/sql-createtable.html
GENERATED ALWAYS AS ( generation_expr ) STORED
This clause creates the column as a generated column. The column cannot be written to, and when read the result of the specified expression will be returned.
The keyword STORED is required to signify that the column will be computed on write and will be stored on disk.
The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
This clause creates the column as an identity column. It will have an implicit sequence attached to it and the column in new rows will automatically have values from the sequence assigned to it.
The clauses ALWAYS and BY DEFAULT determine how the sequence value is given precedence over a user-specified value in an INSERT statement. If ALWAYS is specified, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is specified, then the user-specified value takes precedence. See INSERT for details. (In the COPY command, user-specified values are always used regardless of this setting.)
The optional sequence_options clause can be used to override the options of the sequence. See CREATE SEQUENCE for details.
https://pgdba.org/post/2019/10/generated_columns/
https://fluca1978.github.io/2019/11/04/PostgreSQL12GeneratedColumns.html
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
