使 ALTER TABLE 等待鎖定而不阻塞其他任何東西
許多 PostgreSQL
ALTER TABLE命令，例如添加具有預設值的新列，在 PostgreSQL 的最新版本中進行了巧妙的優化，***一旦 Postgres 短暫獲得表上的鎖，***它們基本上可以立即執行，即使在大表上也是如此。不幸的是，最後的警告很重要。來自連結的部落格文章的類似命令
ALTER TABLE users ADD COLUMN credits bigint NOT NULL DEFAULT 0;仍然需要等待
users表上的排他鎖才能執行，即使它會在獲得鎖後立即執行。更糟糕的是，在等待該鎖時，它會阻塞所有涉及該表的寫入和讀取。重現這一點的一些簡單步驟（在 Postgres 13.3 中測試）：
- 在一個
psqlshell 中，創建一個表，然後啟動一個事務，從表中讀取，然後不送出：CREATE TABLE users (id SERIAL, name TEXT); INSERT INTO users (name) VALUES ('bob'), ('fred'); START TRANSACTION; SELECT * FROM users WHERE id = 1;
- 讓第一個 shell 打開，然後打開第二個 shell 並嘗試更改表：
ALTER TABLE users ADD COLUMN credits bigint NOT NULL DEFAULT 0;觀察到這個查詢掛起，等待第一個 shell 中的事務被送出。 3. 打開第三個終端，然後嘗試執行
SELECT * FROM users WHERE id = 2;觀察這也掛起；它現在被阻塞等待
ALTER TABLE命令完成，而後者又被阻塞等待第一個事務完成。似乎大多數或所有
ALTER TABLE命令的行為都是這樣的。即使操作本身非常快或者可以在整個操作不持有鎖的情況下執行，仍然需要在開始工作ALTER TABLE之前短暫地獲取表上的排他鎖，並且在等待該鎖時，所有其他語句觸摸桌子 - 甚至閱讀！- 被阻止。不用說，如果您想對偶爾涉及長時間執行事務的表進行更改，那麼這種行為是非常有問題的。如果該
ALTER TABLE語句被一個長時間執行的事務阻塞，而該事務恰好在執行該語句時持有涉及該表的任何類型的鎖，則ALTER TABLE與該表的所有互動都將被阻塞，直到該隨機長時間執行的事務結束為止，並且任何依賴於該表的東西都可能會遇到停機時間。這個問題有規範的解決方案嗎？
我嘗試過的一個粗略的解決方案是使用一個包裝腳本，它通過設置為一個小值（例如 5 秒）
ALTER TABLE的連接反复嘗試執行該語句。lock_timeout如果ALTER TABLE由於鎖定超時而失敗，則事務中止並且腳本擷取錯誤，等待一兩分鐘，然後再次嘗試整個過程。這避免了徹底的停機時間，但仍然會影響性能，因為每次執行該語句的失敗嘗試ALTER TABLE仍然會阻塞查詢幾秒鐘。我真正想做的是以某種方式告訴 Postgres，我希望
ALTER TABLE語句等待片刻，它可以獲取表上的鎖，同時不會阻塞其他查詢。（我不介意這是否意味著它會等待數小時，直到它最終到達沒有其他查詢觸及表的時刻；如果它避免阻塞其他查詢，那絕對是一個可以接受的權衡。）有什麼方法可以做到這一點 - 也許我可以在ALTER TABLE語句中包含一些咒語，或者我可以設置一些配置參數來改變這種行為？
不幸的是，除了在循環中重試之外，沒有很好的選擇。但是您也許可以使重試更聰明。當我需要這樣做並且可以在事務塊中時，我顯式地獲取鎖，並使用NOWAIT選項。
但仍然會影響性能，因為每次執行 ALTER TABLE 語句的失敗嘗試仍然會阻塞查詢幾秒鐘。
您可以將超時值設置為低於（遠低於）幾秒。或者您可以使用 NOWAIT，它應該與將 lock_timeout 設置為其可能的最低值大致相同，但是一旦獲得該鎖就會自動重置（與多語句事務有關）。
我真正想做的是以某種方式告訴 Postgres 我希望 ALTER TABLE 語句等待片刻，它可以獲取表上的鎖，同時不會阻塞其他查詢。
是的，在這裡有一些更好的選擇會很好。不過，確切地弄清楚那會是什麼樣子可能會引起爭議。可能類似於 MySQL 的低優先級鎖，它將自己保持在等待隊列中，但如果其他服務員可以立即以它想要的模式獲得鎖，則讓其他服務員跳過它。