為什麼 ADD CONSTRAINT … NOT VALID 需要很長時間？

我在這樣的一些列中添加了 NOT NULL 約束，期望該命令非常快，因為NOT VALID. 但是，它花了大約 60-100 秒，並且似乎一直鎖定表（基於在那段時間其他事情失敗）。這是在一個大約有 8,600,000 行的表上。為什麼會這樣？執行 4 個命令而不是 1 個命令會更好嗎？

Postgres 12

ALTER TABLE my_table
 ADD CONSTRAINT my_table_column1_not_null CHECK (column1 IS NOT NULL) NOT VALID,
 ADD CONSTRAINT my_table_column2_not_null CHECK (column2 IS NOT NULL) NOT VALID,
 ADD CONSTRAINT my_table_column3_not_null CHECK (column3 IS NOT NULL) NOT VALID,
 ADD CONSTRAINT my_table_column4_not_null CHECK (column4 IS NOT NULL) NOT VALID;

ALTER TABLE 需要表上的“訪問獨占”鎖。雖然它一旦獲得它只會持有該鎖幾微秒，但如果任何其他會話已經在表上持有任何鎖，它仍然可能在等待獲取它時無限期地阻塞。一旦它確實阻塞了其他會話，即使是想要更弱鎖定的會話，也會在它後面阻塞。（PostgreSQL 不允許隊列跳轉，因此等待被授予的強鎖將阻塞傳入的弱鎖，即使這些傳入的鎖與已經持有的鎖兼容。）

如果您希望 ALTER TABLE 幾乎立即成功或失敗，請將 lock_timeout 設置為較低的值。嘗試將它們作為單獨的語句執行會使情況變得更糟，而不是更好。

引用自：https://dba.stackexchange.com/questions/268301