postgresql - 更改列集非空失败

Question

考虑下表，大约有 1000 万行

CREATE TABLE user
(
  id bigint NOT NULL,
  ...
  CONSTRAINT user_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
)

然后我应用了以下更改

ALTER TABLE USER ADD COLUMN BUSINESS_ID    VARCHAR2(50);
--OK
UPDATE USER SET BUSINESS_ID = ID; //~1500 sec
--OK
ALTER TABLE USER ALTER COLUMN BUSINESS_ID SET NOT NULL;

    ERROR: column "business_id" contains null values
    SQL state: 23502

这很奇怪，因为 id 列(已被复制到 business_id 列)不能包含空值，因为它是主键，但为了确保我检查它

select count(*) from USER where BUSINESS_ID is null
    --0 records

我怀疑这是一个错误，只是想知道我是否遗漏了一些微不足道的东西

Answer 1

唯一合乎逻辑的解释是并发INSERT。
(使用 tbl 而不是保留字 user 作为表名。)

ALTER TABLE tbl ADD COLUMN BUSINESS_ID    VARCHAR2(50);
--OK
UPDATE tbl SET BUSINESS_ID = ID; //~1500 sec
--OK

-- concurrent INSERT HERE !!!

ALTER TABLE tbl ALTER COLUMN BUSINESS_ID SET NOT NULL;</code></pre>

要防止这种情况，请改用:

ALTER TABLE tbl
  ADD COLUMN BUSINESS_ID VARCHAR(50) DEFAULT '';  -- or whatever is appropriate
...

您可能会在某些行中使用默认值。您可能需要检查一下。

或者将所有内容作为事务 block 运行:

BEGIN;
-- LOCK tbl; -- not needed
ALTER ...
UPDATE ...
ALTER ...
COMMIT;

您可能需要一个独占锁来确定，但是ALTER TABLE .. ADD COLUMN无论如何都需要一个 ACCESS EXCLUSIVE 锁。 (它只在事务结束时释放，就像所有锁一样。)