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