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)      

postgres=# select atthasmissing,attmissingval from pg_attribute where attrelid=16387 and attname='id3';
 atthasmissing | attmissingval
---------------+---------------
 f             |
(1 row)      

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)