PostgreSQL11preview-新增非空預設值不需要rewritetable

德哥發表於2018-06-29

背景

PostgreSQL ，我們在給表加列時，如果不設定列的預設值，不需要rewrite table，只是對後設資料進行修改。

但是如果需要設定新增列的預設值，那麼必須rewrite table。

PostgreSQL 11，新特性，在後設資料中新增了2列(attmissingval, atthasmissing)，當新增stable或immutable表示式（非volatile）作為預設值時，在ALTER TABLE時，即刻評估表示式的值作為常量，存入新增列在pg_attribute後設資料中的attmissingval欄位中，同時將atthasmissing標記為true.

Allow ALTER TABLE to add a column with a non-null default without a table rewrite (Andrew Dunstan, Serge Rielau)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16828d5c0273b4fe5f10f42588005f16b415b2d8

Fast ALTER TABLE ADD COLUMN with a non-NULL default  
  
Currently adding a column to a table with a non-NULL default results in  
a rewrite of the table. For large tables this can be both expensive and  
disruptive. This patch removes the need for the rewrite as long as the  
default value is not volatile. The default expression is evaluated at  
the time of the ALTER TABLE and the result stored in a new column  
(attmissingval) in pg_attribute, and a new column (atthasmissing) is set  
to true. Any existing row when fetched will be supplied with the  
attmissingval. New rows will have the supplied value or the default and  
so will never need the attmissingval.  
  
Any time the table is rewritten all the atthasmissing and attmissingval  
settings for the attributes are cleared, as they are no longer needed.  
  
The most visible code change from this is in heap_attisnull, which  
acquires a third TupleDesc argument, allowing it to detect a missing  
value if there is one. In many cases where it is known that there will  
not be any (e.g.  catalog relations) NULL can be passed for this  
argument.  
  
Andrew Dunstan, heavily modified from an original patch from Serge  
Rielau.  
Reviewed by Tom Lane, Andres Freund, Tomas Vondra and David Rowley.  
  
Discussion: https://postgr.es/m/31e2e921-7002-4c27-59f5-51f08404c858@2ndQuadrant.com

原理

新增stable或immutable表示式（非volatile）作為預設值時，在ALTER TABLE時，即刻評估表示式的值作為常量，存入新增列在pg_attribute後設資料中的attmissingval欄位中，同時將atthasmissing標記為true. 結合tuple head的t_infomask掩碼以及null bitmaps，可以實現輸出時是否使用後設資料中儲存的預設值。

未來如果對新增欄位進行更新、或者寫入新記錄時帶上非預設值，則新值會直接寫入TUPLE中。

heap tuple : t_infomask 掩碼

src/include/access/htup_details.h

/*  
 * information stored in t_infomask:  
 */  
#define HEAP_HASNULL                    0x0001  /* has null attribute(s) */  
#define HEAP_HASVARWIDTH                0x0002  /* has variable-width attribute(s) */  
#define HEAP_HASEXTERNAL                0x0004  /* has external stored attribute(s) */  
#define HEAP_HASOID                             0x0008  /* has an object-id field */  
#define HEAP_XMAX_KEYSHR_LOCK   0x0010  /* xmax is a key-shared locker */  
#define HEAP_COMBOCID                   0x0020  /* t_cid is a combo cid */  
#define HEAP_XMAX_EXCL_LOCK             0x0040  /* xmax is exclusive locker */  
#define HEAP_XMAX_LOCK_ONLY             0x0080  /* xmax, if valid, is only a locker */  
  
  
.........  
  
  
/*  
 * BITMAPLEN(NATTS) -  
 *              Computes size of null bitmap given number of data columns.  
 */  
#define BITMAPLEN(NATTS)        (((int)(NATTS) + 7) / 8)

   1 /*-------------------------------------------------------------------------  
   2  *  
   3  * tupdesc_details.h  
   4  *    POSTGRES tuple descriptor definitions we can`t include everywhere  
   5  *  
   6  *  
   7  * Portions Copyright (c) 1996-2018, PostgreSQL Global Development Group  
   8  * Portions Copyright (c) 1994, Regents of the University of California  
   9  *  
  10  * src/include/access/tupdesc_details.h  
  11  *  
  12  *-------------------------------------------------------------------------  
  13  */  
  14   
  15 #ifndef TUPDESC_DETAILS_H  
  16 #define TUPDESC_DETAILS_H  
  17   
  18 /*  
  19  * Structure used to represent value to be used when the attribute is not  
  20  * present at all in a tuple, i.e. when the column was created after the tuple  
  21  */  
  22   
  23 typedef struct attrMissing  
  24 {  
  25     bool        ammissingPresent;   /* true if non-NULL missing value exists */  
  26     Datum       ammissing;      /* value when attribute is missing */  
  27 } AttrMissing;  
  28   
  29 #endif                          /* TUPDESC_DETAILS_H */

PostgreSQL 11測試

1、建立測試表，新增若干資料（5.6GB）

postgres=# create unlogged table aaa(id int, info text, crt_time timestamp);  
CREATE TABLE  
Time: 6.259 ms  
postgres=# insert into aaa select generate_series(1,1000000),repeat(md5(random()::text),10), now();  
INSERT 0 1000000  
Time: 2151.531 ms (00:02.152)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 1000000  
Time: 1235.480 ms (00:01.235)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 2000000  
Time: 2688.409 ms (00:02.688)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 4000000  
Time: 4782.437 ms (00:04.782)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 8000000  
Time: 11367.010 ms (00:11.367)  
  
  
postgres=# dt+ aaa  
                    List of relations  
 Schema | Name | Type  |  Owner   |  Size   | Description   
--------+------+-------+----------+---------+-------------  
 public | aaa  | table | postgres | 5618 MB |   
(1 row)

2、新增欄位，並新增預設值，由於只需要修改後設資料，瞬間完成。

postgres=# alter table aaa add column c1 text default `digoal`;  
ALTER TABLE  
Time: 3.013 ms

3、檢視後設資料表，可以看到，atthasmissing=true, attmissingval=我們設定的預設值。

postgres=# x  
Expanded display is on.  
postgres=# select * from pg_attribute where attrelid=`aaa`::regclass and attname=`c1`;  
-[ RECORD 1 ]-+---------  
attrelid      | 99498  
attname       | c1  
atttypid      | 25  
attstattarget | -1  
attlen        | -1  
attnum        | 4  
attndims      | 0  
attcacheoff   | -1  
atttypmod     | -1  
attbyval      | f  
attstorage    | x  
attalign      | i  
attnotnull    | f  
atthasdef     | t  
atthasmissing | t  
attidentity   |   
attisdropped  | f  
attislocal    | t  
attinhcount   | 0  
attcollation  | 100  
attacl        |   
attoptions    |   
attfdwoptions |   
attmissingval | {digoal}  
  
Time: 0.470 ms

4、檢視資料，預設值會從後設資料表中獲取。

postgres=# select id,c1 from aaa limit 10;  
 id |   c1     
----+--------  
  1 | digoal  
  2 | digoal  
  3 | digoal  
  4 | digoal  
  5 | digoal  
  6 | digoal  
  7 | digoal  
  8 | digoal  
  9 | digoal  
 10 | digoal  
(10 rows)  
  
Time: 0.510 ms

5、寫入新值，更新老值，符合最終要求

postgres=# insert into aaa values (0,`test`,now(),null);  
INSERT 0 1  
Time: 0.487 ms  
postgres=# insert into aaa values (-1,`test`,now(),`new`);  
INSERT 0 1  
Time: 0.415 ms  
postgres=# update aaa set c1=`hello digoal` where id=1;  
UPDATE 16  
Time: 2010.873 ms (00:02.011)  
  
postgres=# select id,c1 from aaa where id in (0,-1,1);  
 id |      c1        
----+--------------  
  0 |   
 -1 | new  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
(18 rows)  
  
Time: 1033.896 ms (00:01.034)

PostgreSQL 低版本測試

postgres=# create unlogged table aaa(id int, info text, crt_time timestamp);  
CREATE TABLE  
Time: 2.930 ms  
postgres=# insert into aaa select generate_series(1,1000000),repeat(md5(random()::text),10), now();  
INSERT 0 1000000  
Time: 2992.188 ms (00:02.992)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 1000000  
Time: 1521.732 ms (00:01.522)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 2000000  
Time: 2781.948 ms (00:02.782)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 4000000  
Time: 5565.887 ms (00:05.566)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 8000000  
Time: 10903.184 ms (00:10.903)  
postgres=# dt+ aaa  
                    List of relations  
 Schema | Name | Type  |  Owner   |  Size   | Description   
--------+------+-------+----------+---------+-------------  
 public | aaa  | table | postgres | 5683 MB |   
(1 row)  
  
postgres=# alter table aaa add column c1 text default `digoal`;  
ALTER TABLE  
Time: 22484.222 ms (00:22.484)

小結

PostgreSQL 11 終於可以愉快的加欄位加預設值了。

但是依舊需要注意DDL lock, 所以建議還是要設定LOCK_TIME再執行DDL。

postgres=# set lock_timeout =`1s`;  
SET  
  
alter table ....;

參考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16828d5c0273b4fe5f10f42588005f16b415b2d8

PG11新特性解讀：新增非空預設值欄位不需要重寫表
2020-02-03
非空+預設值——一種選擇方案思路
2011-04-18
PostgreSQL技術週刊第9期：PostgreSQL11新特性解讀之新增非空預設值欄位不需要重寫表
2018-12-16
SQL
表列新增預設值的方法
2015-10-23
為 protocol 中屬性新增預設值
2019-04-17
Protocol
肯定賦值斷言與非空斷言
2020-12-04
賦值
${VAR:=預設值}和${VAR:-預設值} 區別
2024-03-25
MySQL-修改欄位型別、設定預設值，以及新增註釋
2017-12-11
MySql型別
預設非安全埠列表
2020-04-14
遷移帶LOB等大欄位資料到非預設表空間
2013-11-16
Oracle修改預設表空間和預設臨時表空間
2014-03-04
Oracle
MySQL多個timestamp欄位自動新增預設值的問題
2017-04-13
MySql
Oracle資料表預設值列新增與行遷移（Row Migration）
2012-08-28
Oracle
git伺服器非預設埠
2018-09-20
Git伺服器
AssetBoundle載入非預設資源
2018-12-20
Laravel 生成 url,預設值
2020-04-24
Laravel
HTML 元素的預設值
2021-01-04
HTML
HTTP請求預設值
2020-10-12
HTTP
【譯】GOPATH 的預設值
2017-04-25
Go
sql設定欄位預設值
2020-04-04
SQL
django 設定預設值到SQL
2016-11-03
DjangoSQL
Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間方法！
2018-03-22
Oracle
C#實現函式預設值和C#4.0實現預設值
2012-05-14
C#函式
Sql Server 自定義約束實現：某列可空，但非空值唯一不重複
2021-09-09
SQLServer
震驚 PHP empty 函式判斷結果為空，但實際值卻為非空
2018-05-28
PHP函式
HTML常用元素的預設值
2021-09-09
HTML
mysql datetime增加預設值
2021-05-19
MySql
CSS border屬性預設值
2017-03-02
CSS
MySQL修改欄位預設值
2012-12-01
MySql
sqlserver 修改列的預設值
2009-02-08
SQLServer
非空閒等待事件
2012-01-19
事件
為什麼 php empty 函式判斷結果為空，但實際值卻為非空
2019-02-16
PHP函式
CSS 值為0不需要單位
2019-01-08
CSS
MySQL欄位預設值設定詳解
2021-03-01
MySql
oracle增加欄位帶預設值
2020-06-17
Oracle
陷阱！python引數預設值
2016-10-01
Python
【C++】引數的預設值
2017-02-02
C++
css的border屬性預設值
2017-03-02
CSS

PostgreSQL11preview-新增非空預設值不需要rewritetable

標籤

背景

原理

PostgreSQL 11測試

PostgreSQL 低版本測試

小結

參考

相關文章