主页 / dba / 问题 / 267947
Accepted
John Bachir
Asked: 2020-05-27 18:40:48 +0800 CST

如何在表扫描期间将列设置为 NOT NULL 而不锁定表？

  • 772

（以前的问题是：Postgres 在设置多列不为空时会使用多列索引吗？）

通常，当我将列设置为不为空时，如果它没有索引，那么我首先添加它，以便 postgres 可以（希望）在锁定表的同时进行表扫描时使用索引，以便表被锁定更短的时间。

我想设置几列不为空，如下所示：

alter table foos
  alter column bar1 set not null
  alter column bar2 set not null
  alter column bar3 set not null
  alter column bar4 set not null;

如果我为这些列创建一个多列索引，postgres 在进行此更改之前扫描锁定表时会使用它吗？

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4);

如果我在 IS NULL（或者，IS NOT NULL）上创建了部分索引怎么办？

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null;
postgresql index

2 个回答

  1. Best Answer

    另一个 postgresql 贡献者的另一个答案。

    在执行“alter table set not null”期间，PostgreSQL 甚至不会尝试使用任何索引。它只是没有实施。

    正确执行索引扫描是困难的部分。我们不能只做这样的查询

    select exists(select from foos where bar1 is null)

    由于各种原因从alter table 命令。这样的功能将需要大量代码（并且在某些边缘情况下可能还需要脆弱的代码），大量工作，仅适用于有限的用例。开发人员不喜欢的东西。实际上，pgsql-hackers 社区不喜欢 NOT NULL 存储在系统目录中的方式。重新设计这部分目录会更清晰。之后，可以使用短锁执行 SET NOT NULL NOT VALID 和没有排他锁的表验证。类似于alter table .. add constraint ... not valid+alter table .. validate constraint用于检查约束或外键。但这样的重新设计工作量更大，没有人愿意这样做。

    但我有一个好消息：在 PostgreSQL 12（及更高版本）中，扫描整个表并不是唯一的选择。alter table set not null可以通过现有的检查约束证明 NOT NULL 的正确性。因此，可以这样做：

    -- short-time exclusive lock
alter table foos 
  add constraint foos_not_null 
  check (bar1 is not null) not valid;

-- seqscan, but without exclusive lock, concurrent sessions can read/write
alter table foos validate constraint foos_not_null;

-- exclusive lock, but foos_not_null proves: there is no NULL in this column
-- so this alter table would be fast
alter table foos alter column bar1 set not null;
-- not needed anymore
alter table foos drop constraint foos_not_null;

    那是我的补丁。是的，这看起来像一个解决方法。但是，幸运的是，它被合并了，现在可以set not null不用长排他锁了。

    • 16

  2. 我查看了源代码（ATRewriteTable中的函数src/backend/commands/tablecmds.c），PostgreSQL 总是使用对表的顺序扫描来验证NOT NULL约束。

    因此创建索引不会加快执行速度。

    • 4

相关问题