相关文章推荐
俊逸的电脑桌  ·  系统函数有哪些字符串函数_数据资源平台(DR ...·  1 周前    · 
健壮的葡萄酒  ·  把数组里的数据以kv键值对的方式存入到red ...·  1 年前    · 
忐忑的显示器  ·  将字符串转换为十六进制并进行异或操作。·  1 年前    · 
大气的凳子  ·  Spark ...·  1 年前    · 
文武双全的铅笔  ·  S03E05:多个矩阵的“平均矩阵” - 掘金·  2 年前    · 
深沉的香槟  ·  图文:朝日集团将收购新西兰独立饮料_美股新闻 ...·  2 年前    · 
Code  ›  PostgreSQL 11 preview - 添加非空默认值不需要 rewrite table - online add column with default value - 墨天轮
table select postgresql
https://www.modb.pro/db/92995
慷慨的匕首
2 年前
  • 学习
    • 课程中心
      推荐优质内容、热门课程
    • 学习路径
      预设学习计划、达成学习目标
    • 知识图谱
      综合了解技术体系知识点
    • 课程库
      快速筛选、搜索相关课程
    • 视频学习
      专业视频分享技术知识
    • 电子文档
      快速搜索阅览技术文档
  • 文档
  • 工具
    • SQLRUN
      在线数据库即时SQL运行平台
    • 数据库在线实训平台
      实操环境、开箱即用、一键连接
    • Oracle巡检
      简单两步,查看报告分析
    • AWR分析
      上传AWR报告,查看分析结果
    • SQL格式化
      快速格式化绝大多数SQL语句
    • SQL审核
      审核编写规范,提升执行效率
    • PLSQL解密
      解密超4000字符的PL/SQL语句
    • OraC函数
      查询Oracle C 函数的详细描述
    • Bethune X
      数据库智能监控巡检平台,90天试用
  • 暂无图片
    • 数据库
    • 云计算
    • 数据产品
    • 中间件
    • 操作系统
    • 芯片
  • 我的订单
  • 登录后可立即获得以下权益
    免费培训课程
    收藏优质文章
    疑难问题解答
    下载专业文档
    签到免费抽奖
    提升成长等级
    立即登录
    登录 注册
      • 登录 注册
    • 首页
    • 资讯
    • 数说
    • 活动
    • 大会
    • 课程
    • 文档
    • 排行
    • 问答
    • 云市场
    • 我的订单
    暂无图片
    觉得内容不错?
    一键收藏 方便随时查看
    暂无图片
    暂无图片
    微信扫码
    复制链接
    新浪微博
    分享数说
    暂无图片
    采集到收藏夹
    分享到数说
    首页 / PostgreSQL 11 preview - 添加非空默认值不需要 rewrite table - online add column with default value

    PostgreSQL 11 preview - 添加非空默认值不需要 rewrite table - online add column with default value

    digoal 2018-05-18
    339

    作者

    digoal

    2018-05-18

    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 /*------------------------------------------------------------------------- 3 * tupdesc_details.h 4 * POSTGRES tuple descriptor definitions we can't include everywhere 10 * src/include/access/tupdesc_details.h 11 * 12 *------------------------------------------------------------------------- 13 */ 15 #ifndef TUPDESC_DETAILS_H 16 #define TUPDESC_DETAILS_H 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 */ 23 typedef struct attrMissing 25 bool ammissingPresent; /* true if non-NULL missing value exists */ 26 Datum ammissing; /* value when attribute is missing */ 27 } AttrMissing; 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
    ----+--------------
    -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';

    alter table ....;

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

    PostgreSQL 许愿链接

    您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。 开不开森 .

     
    推荐文章
    俊逸的电脑桌  ·  系统函数有哪些字符串函数_数据资源平台(DRP)-阿里云帮助中心
    1 周前
    健壮的葡萄酒  ·  把数组里的数据以kv键值对的方式存入到redis - CSDN文库
    1 年前
    忐忑的显示器  ·  将字符串转换为十六进制并进行异或操作。
    1 年前
    大气的凳子  ·  Spark 创建RDD、DataFrame各种情况的默认分区数_spark 分区数-CSDN博客
    1 年前
    文武双全的铅笔  ·  S03E05:多个矩阵的“平均矩阵” - 掘金
    2 年前
    深沉的香槟  ·  图文:朝日集团将收购新西兰独立饮料_美股新闻_新浪财经_新浪网
    2 年前
    今天看啥   ·   Py中国   ·   codingpro   ·   小百科   ·   link之家   ·   卧龙AI搜索
    删除内容请联系邮箱 2879853325@qq.com
    Code - 代码工具平台
    © 2024 ~ 沪ICP备11025650号