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

德哥發表於2018-06-29
SQLView

標籤

PostgreSQL , add column default , rewrite table

背景

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


相關文章