postgresql - 在没有锁定问题的情况下在大表中创建一列 NOT NULL？

Question

我想将列更改为 NOT NULL:

ALTER TABLE "foos" ALTER "bar_id" SET NOT NULL

“foos”表有近 1 000 000 条记录。它的写入量相当低，但相当稳定。有很多阅读。

根据我的经验，像这样将大表中的列更改为 NOT NULL 可能会导致应用程序停机，大概是因为它会导致 (b) 锁定。

不过，我还没有找到一个很好的解释来证实这一点。

如果是真的，我该怎么做才能避免呢？

编辑:The docs (通过 this comment )说:

Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten.

我不确定更改 NULL 是否算作“更改现有列的类型”，但我相信我上次看到此问题时确实在该列上有一个索引。

也许删除索引，使列 NOT NULL，然后重新添加索引会有所改善？

Answer 1

我认为您可以使用检查约束而不是 set not null 来做到这一点。

ALTER TABLE foos 
     add constraint id_not_null check (bar_id is not null) not valid;

这仍然需要表上的 ACCESS EXCLUSIVE 锁，但速度非常快，因为 Postgres 不验证约束(因此它不必扫描整个表)。这将确保新行(或更改的行)不能将空值放入该列

然后(在提交 alter table 之后!)您可以:

alter table foos validate constraint id_not_null;

它不需要 ACCESS EXCLUSIVE 锁并且仍然允许访问表。