在大表中添加NOT NULL约束，而不进行表扫描。

Question

在大表中添加NOT NULL约束，而不进行表扫描。

17

尝试在拥有10亿行的表中添加一个“NOT NULL”约束。我不能承受超过几秒钟的表锁定时间。在alter table语句期间，有没有办法防止进行全表扫描？我在列上创建了一个索引，希望它会被使用，但似乎不起作用。也许可以使用检查约束？还有其他选项吗？谢谢！

- Volker Hauf

你到底在运行什么东西，竟然连几秒钟的表锁都买不起？是宇宙软件吗？ - Mihai

4:) 收集实时数据，但仅限于宇宙的一小部分。 - Volker Hauf

1只是一些想法... 很奇怪如果你有索引它仍然使用全表扫描。如果使用了索引，并且用于查找不存在值的索引查找应该极快... 所以锁不会是一个问题。 - kan

2从PG 12开始，我们现在可以同时添加NOT NULL！https://dba.stackexchange.com/a/268128/12659 - John Bachir

翻译结果：（顺便说一句，这个问题被标记为重复的问题实际上与其他事情有关 - 如果管理员看到，请撤销标记，以便添加实际上适用于12岁以上的真正答案） - John Bachir

2个回答

11

一种可能的替代方法是使用 NOT VALID 创建一个检查约束，然后稍后验证该检查约束。这种方法只需要在创建约束时持有一个 ACCESS EXCLUSIVE 锁，持续时间应该在毫秒级别。VALIDATE 命令将执行一个耗时较长的全表扫描以验证约束，但它持有一个较不严格的 SHARE UPDATE EXCLUSIVE 锁。

至于权衡，我未能找到任何文件提及标准的 NOT NULL 约束和验证列非空的检查约束之间的内部机制差异。我记得曾经挖掘过一个论坛帖子，暗示可能存在性能差异，但我已经找不到链接了，所以这个信息尚未确认。

ALTER TABLE table ADD CONSTRAINT table_value_not_null_check CHECK (value IS NOT NULL) NOT VALID;

ALTER TABLE table VALIDATE CONSTRAINT table_value_not_null_check;

来源：

https://www.postgresql.org/docs/9.4/static/sql-altertable.html https://www.postgresql.org/docs/9.4/static/explicit-locking.html

- Marcus

1这里有一个参考标准，讨论了标准的NOT NULL约束和检查约束之间的区别：https://dba.stackexchange.com/questions/158499/postgres-how-is-set-not-null-more-efficient-than-check-constraint - stereoscott

你还可以在PG 12之后将CHECK约束转换为真正的NOT NULL，而无需长时间锁定表格。请参考：https://dba.stackexchange.com/a/268128/137516 - phiresky

相关问题
12 如何在表扫描期间将列设置为非空（NOT NULL），而不锁定表？

- Craig Ringer · Accepted Answer

目前在PostgreSQL中没有支持的、安全的方法来防止在alter table语句期间进行全表扫描。一种类似于ALTER TABLE ... ADD CONSTRAINT ... CONCURRENTLY的方式会很好，但是目前还没有人实现它。同样的情况也适用于添加一个NOT VALID约束，该约束仍然影响新行，并且稍后可以通过VALIDATE进行验证 - 这是大家都知道需要的功能，但是目前还没有时间或资金来添加。理论上，如果您知道约束是真实和有效的，您可以直接修改系统目录以添加约束。但实际上，这通常不是一个好主意。所以，实际上并没有办法做到。