改变数据类型的列在一个大表几乎总是一个痛苦。它可以锁定整个表的阅读和写作,因为互斥型锁通过ALTER table语句更改数据类型。在这篇文章中,我们将看一个影响最小的执行这样的操作的可能性,以最小化锁。它适用于任何数据类型;让我们看看整数大整数变化为例。
示例用例
假设我们有一个表有很多列。其中一个是类型的整数(INT, 4个字节)价值观逐步增长。过了一会儿,我们开始消息日志错误:整数的范围,这意味着我们要插入的值大于整数限制(最大价值2147483647)。简单的选择将改变其类型大整数(长整型数字8字节)。这里是如何做到这一点几乎“在线”。
首先,我们将需要创建一个新的新的数据类型的列。新列创建空的,所以它应该在毫秒。
|
1
|
改变
表
big_table
添加
列
order_id_tmp
长整型数字
;
|
以确保它没有引起太多的锁,我们可以编写一个小的SQL脚本,将超时命令如果需要很长时间才能完成,即。
|
1
2
3
4
5
6
7
8
9
10
11
|
猫
< <
EOF
>
改变
。
sql
集
statement_timeout
=
One hundred.
;
改变
表
big_table
添加
列
order_id_tmp
长整型数字
;
EOF
而
真正的
做
日期
psql
- - - - - -
qX、
- - - - - -
v
ON_ERROR_STOP
=
1
- - - - - -
f
改变
。
sql
& &
打破
睡眠
1
完成
|
Statement_timeout将终止ALTER TABLE命令如果它运行超过100毫秒,因为ON_ERROR_STOP添加到psql参数,其过程将退出一个错误。因此“打破”将不会执行超时之后因为& &操作符预计第一部分的退出代码为0,这意味着成功,在执行下一个。
接下来,我们将创建一个函数和一个触发器,将复制的价值order_id order_id_tmp新插入和更新的行。
|
1
2
3
4
5
6
7
8
9
10
|
创建
函数
order_id_bigint
(
)
返回
触发
作为
美元
身体
美元
开始
新
。
order_id_tmp
=
新
。
order_id
;
返回
新
;
结束
美元
身体
美元
语言
PLPGSQL
;
创建
触发
order_id_update_or_insert
之前
插入
或
更新
在
big_table
为
每一个
行
执行
过程
order_id_bigint
(
)
;
|
我们准备用数据填充新列。表中更大的尺寸,我们可以创建一个支持表来帮助这个进展。它应该包含主键,在这种情况下,列“ID”big_table,新的长整型数字列空行。
|
1
2
3
4
5
6
7
|
创建
表
公共
。
temp_order_id_bigint
(
id
长整型数字
不
零
)
;
创建
指数
id_sorted
在
公共
。
temp_order_id_bigint
使用
btree
(
id
ASC
)
;
插入
成
公共
。
temp_order_id_bigint_change
选择
id
从
big_table
在哪里
(
order_id
是
不
零
和
order_id_tmp
是
零
)
;
|
我们将填充数据块,Postgres不喜欢大规模更新因为MVCC多版本并发控制机制和autovacuum作品。更新会更新所有元组,将导致巨大的膨胀,和表规模将增长两倍。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
开始
事务
;
更新
公共
。
big_table
集
order_id_tmp
=
order_id
在哪里
id
在
(
选择
id
从
公共
。
temp_order_id
_长整型数字
订单
通过
id
ASC
限制
5000年
)
;
删除
从
公共
。
temp_order_id_bigint
在哪里
id
在
(
选择
id
从
公共
。
temp_order_id
_长整型数字
订单
通过
id
ASC
限制
5000年
)
;
提交
;
|
运行上面的事务在一个循环中,直到临时表,temp_order_id_bigint,是空的。
使用限制,最好适合你;根据行大小,您可以实现更好的结果较低或较高的值。在我的测试用例,5000年是最优的,每5 k行完成在不到300 ms。
填充数据后,我们准备执行列转换。我们可以用类似的方法while循环,如前所述。它将确保锁不是和阻塞其他会话等待太久。
|
1
2
3
4
5
6
|
开始
;
锁
表
big_table
在
分享
行
独家
模式
;
下降
触发
order_id_update_or_insert
在
big_table
;
改变
表
big_table
重命名
列
order_id
来
order_id_old
;
改变
表
big_table
重命名
列
order_id_tmp
来
order_id
;
提交
;
|
果不其然,我们只是改变了列从整数到整数大不到一秒的锁定。操作本身可能需要更长的时间比仅仅改变现有的列的数据类型,因为所有的准备工作,数据人口等。但我们避免了停机或维护窗口,需要使用标准的方法。
享受吧!
雷竞技下载官网Percona分布PostgreSQL提供最好的和最关键的企业组件从开源社区在一个分布,设计和测试一起工作。
