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
PostgreSQL技術週刊第9期：PostgreSQL11新特性解讀之新增非空預設值欄位不需要重寫表
2018-12-16
SQL
需要取最近的非空值
2024-08-11
為 protocol 中屬性新增預設值
2019-04-17
Protocol
jpa~為欄位新增insert的預設值
2024-09-27
肯定賦值斷言與非空斷言
2020-12-04
賦值
${VAR:=預設值}和${VAR:-預設值} 區別
2024-03-25
預設非安全埠列表
2020-04-14
Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間方法！
2018-03-22
Oracle
AssetBoundle載入非預設資源
2018-12-20
git伺服器非預設埠
2018-09-20
Git伺服器
預設值的作用域
2019-02-15
HTTP請求預設值
2020-10-12
HTTP
Laravel 生成 url,預設值
2020-04-24
Laravel
生成 URL 的預設值
2020-04-17
mysql datetime增加預設值
2021-05-19
MySql
HTML 元素的預設值
2021-01-04
HTML
震驚 PHP empty 函式判斷結果為空，但實際值卻為非空
2018-05-28
PHP函式
Sql Server 自定義約束實現：某列可空，但非空值唯一不重複
2021-09-09
SQLServer
sql設定欄位預設值
2020-04-04
SQL
為什麼 php empty 函式判斷結果為空，但實際值卻為非空
2019-02-16
PHP函式
Java中使用Optional檢測並獲得非空值的幾種方法
2024-05-20
Java
HTML常用元素的預設值
2021-09-09
HTML
CSS 值為0不需要單位
2019-01-08
CSS
EOSCleos連線到非預設主機/埠
2018-08-02
2.5.6 建立預設的永久表空間
2020-03-03
2.5.7 建立預設臨時表空間
2020-03-03
【JAVA】判斷字串非空
2019-03-25
Java字串
Python中如何給字典設定預設值
2023-03-03
Python
MySQL欄位預設值設定詳解
2021-03-01
MySql
oracle增加欄位帶預設值
2020-06-17
Oracle
查詢oracle欄位預設值
2024-08-21
Oracle
非極大值抑制
2020-11-10
判斷空值
2018-12-08
Navicat for MySQL 15使用教程：何時使用預設值以及如何選用恰當的預設值
2021-01-05
MySql
2.5.9.2 重寫預設表空間型別
2020-03-05
型別
2.5.9.1 指定預設表空間的型別
2020-03-05
型別
SharePoint 多行文字欄位設定預設值
2018-11-30

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

標籤

背景

原理

PostgreSQL 11測試

PostgreSQL 低版本測試

小結

參考

相關文章