PG11新特性解读：新增非空默认值字段不需要重写表
1、如何理解这个特性
在postgresql11之前，为表增加一个包含非空默认值的字段，将会导致表重写，为每一行添加该字段，并填充默认值。如果该表在增加字段前非常大，那么将会非常耗时。
而在11版本中，新增加一个功能，将不再重写表。而是将非空默认值的属性添加到系统表pg_attribute中，该表描述每一列的信息。
1）系统表pg_attribute存储所有列信息
代码语言：javascript复制postgres=# d pg_attribute
Table "pg_catalog.pg_attribute"
Column | Type | Collation | Nullable | Default
--------------- ----------- ----------- ---------- ---------
attrelid | oid | | not null |
attname | name | | not null |
atttypid | oid | | not null |
attstattarget | integer | | not null |
attlen | smallint | | not null |
attnum | smallint | | not null |
attndims | integer | | not null |
attcacheoff | integer | | not null |
atttypmod | integer | | not null |
attbyval | boolean | | not null |
attstorage | "char" | | not null |
attalign | "char" | | not null |
attnotnull | boolean | | not null |
atthasdef | boolean | | not null |
atthasmissing | boolean | | not null |
attidentity | "char" | | not null |
attgenerated | "char" | | not null |
attisdropped | boolean | | not null |
attislocal | boolean | | not null |
attinhcount | integer | | not null |
attcollation | oid | | not null |
attacl | aclitem[] | | |
attoptions | text[] | C | |
attfdwoptions | text[] | C | |
attmissingval | anyarray | | |
Indexes:
"pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
"pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)在表pg_attribute增加了2个字段，atthasmings和attmissingval。如果新增字段有非空默认值，那么atthasmings置为true，attmissingval为默认值。
例如：
代码语言：javascript复制postgres=# alter table t1 add column id3 int default 5;
ALTER TABLE
postgres=# select atthasmissing,attmissingval from pg_attribute where attrelid=16387 and attname='id3';
atthasmissing | attmissingval
--------------- ---------------
t | {5}
(1 row)2）系统表pg_attrdef，存储所有列的默认值，这个表不管是否是alter table添加非空默认值字段
代码语言：javascript复制postgres=# d pg_attrdef
Table "pg_catalog.pg_attrdef"
Column | Type | Collation | Nullable | Default
--------- -------------- ----------- ---------- ---------
oid | oid | | not null |
adrelid | oid | | not null |
adnum | smallint | | not null |
adbin | pg_node_tree | C | not null |
Indexes:
"pg_attrdef_adrelid_adnum_index" UNIQUE, btree (adrelid, adnum)
"pg_attrdef_oid_index" UNIQUE, btree (oid)postgres=# select *from pg_attrdef ;
oid | adrelid | adnum | adbin
------- --------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------
16390 | 16387 | 3 | {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 42 :constvalue 4 [ 5 0 0 0 ]}
(1 row)pg_node_tree是什么数据类型？
3）对于表中已存在的行查询时返回attmissingval属性的值，插入新的行，若指定带默认值字段，则查询时不需要返回attmissingval属性的值，否则需要返回attmissingval属性的值：
代码语言：javascript复制postgres=# select *from t1;
id1 | id2 | id3
----- ----- -----
1 | 2 |
(1 row)postgres=# insert into t1 values(2,3,NULL);
INSERT 0 1
postgres=# select *from t1;
id1 | id2 | id3
----- ----- -----
1 | 2 | 5
2 | 3 |
(2 rows)
postgres=# insert into t1 (id1,id2) values(3,4);
INSERT 0 1
postgres=# select *from t1;
id1 | id2 | id3
----- ----- -----
1 | 2 | 5
2 | 3 |
3 | 4 | 5
(3 rows)4）一旦该表被重写（vacuum full table操作），那么pg_attribute新增的两个字段值将被清除：
代码语言：javascript复制postgres=# select atthasmissing,attmissingval from pg_attribute where attrelid=16387 and attname='id3';
atthasmissing | attmissingval
--------------- ---------------
f |
(1 row)但是 pg_attrdef的值不会清除
代码语言：javascript复制postgres=# select *from pg_attrdef ;
oid | adrelid | adnum | adbin
------- --------- ------- ---------------------------------------------------------------------------------------------------------------------------------------------
16390 | 16387 | 3 | {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 42 :constvalue 4 [ 5 0 0 0 ]}
(1 row)
