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 |   5

(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)