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

Question

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

12

(Postgres在设置多个列为非空时，是否会使用多列索引？)

通常情况下，当我将一个列设置为非空时，如果它没有索引，我会先添加索引，这样PostgreSQL在进行表扫描并锁定表时可以（希望能够）使用索引，从而使表的锁定时间缩短。我想要将多个列设置为非空，如下所示：

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;

- John Bachir

2个回答

4

我查看了源代码（函数ATRewriteTable在src/backend/commands/tablecmds.c中），PostgreSQL总是使用表的顺序扫描来验证NOT NULL约束。因此，创建索引不会加快执行速度。

- Laurenz Albe

在pgsql-hackers上提到了这个问题 https://www.postgresql.org/message-id/7fc87d44-82de-4592-9cca-14536af274c3%40www.fastmail.com - John Bachir

1这个优秀的回答是针对之前问题的表述：“当设置多列为NOT NULL时，Postgres会使用多列索引吗？” - John Bachir

- Melkij · Accepted 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;

那是我的补丁。是的，这看起来像是一种变通方法。但是，幸运的是，它已经合并了，现在可以在不需要长时间独占锁的情况下设置非空。