在不锁定表的情况下提高数字数据类型的精度

Question

我想在一个包含1000多万条记录的大表上提高数字列的精度。ALTER TABLE mytable ALTER COLUMN mycolumn TYPE numeric(15,6)的标准方式将锁定该表。这是一个高度事务性的表。有没有一种方法可以在不锁定表、不重新创建表、复制数据和重命名/交换表的情况下做到这一点？我目前正在运行一个码头化的postgres9.5

Answer 1

我不认为更改typmod需要重写完整的表，但快速检查显示，如果在finish_heap_swap中设置断点，它实际上会重写像ALTER TABLE test_numeric ALTER COLUMN x TYPE numeric(8,4)这样的cmd-至少在9.5中是这样。

---

编辑：a_hors_with_no_name指出，只有更改精度，而不是比例时，才会执行重写。因此，您可能可以使用一个不会严重干扰操作的短暂独占锁来实现这一点。

---

否则：

您可能会欺骗并直接更新系统目录，因为您知道新的精度对具有旧精度的所有行都有效。但我有点犹豫，因为如果它这么简单，PostgreSQL很可能已经做到了。我不能推荐它，但如果你想实验、测试、阅读等，你可以从那里开始。

对于具有列x numeric(4,2):的表test_numeric

test=> select atttypmod from pg_attribute where attrelid = 'test_numeric'::regclass AND attname = 'x';
atttypmod 
-----------
262150
(1 row)

如果我ALTER它到15,6:

test=> select atttypmod from pg_attribute where attrelid = 'test_numeric'::regclass AND attname = 'x';
atttypmod 
-----------
983050
(1 row)

(我不介意阅读typmod的代码，看看32位typmod字段是如何分解为精度和规模的，但如果你愿意，你可以。)

它应该对UPDATE是安全的，pg_attribute条目具有新的atttypmod。但正如我所说，先做一些阅读和研究，看看你是否能找到直接typmod更新的先前讨论。

任何适用于延长VARCHAR(n)的内容也将适用于NUMERIC，因为两者在内部都是VARLENA存储类型，并且都具有typmod，其中较大的值是较小值的纯超集。你可能会发现更多关于人们询问延长VARCHAR而不重写的信息。