PG11新特性解读：新增非空默认值字段不需要重写表
1、如何理解这个特性
在postgresql11之前，为表增加一个包含非空默认值的字段，将会导致表重写，为每一行添加该字段，并填充默认值。如果该表在增加字段前非常大，那么将会非常耗时。
而在11版本中，新增加一个功能，将不再重写表。而是将非空默认值的属性添加到系统表pg_attribute中，该表描述每一列的信息。
1）系统表pg_attribute存储所有列信息
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为默认值。
例如：
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添加非空默认值字段
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属性的值：
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新增的两个字段值将被清除：
postgres=# select atthasmissing,attmissingval from pg_attribute where attrelid=16387 and attname='id3'; atthasmissing | attmissingval ---------------+--------------- f | (1 row)
但是 pg_attrdef的值不会清除
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)
