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

Question

我想提高包含 10 多万条记录的大型表中数字列的精度。 ALTER TABLE mytable ALTER COLUMN mycolumn TYPE numeric(15,6) 的标准方式将锁定表。这是一个高度事务性的表。有没有办法在不锁定表、不重新创建表、复制数据和重命名/交换表的情况下执行此操作？我目前正在运行 dockerized postgres 9.5

Answer 1

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

---

编辑:a_horse_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 字段是如何分解为精度和小数位数的，但如果你愿意，你可以这样做。)

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

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