PostgreSQL 排程 SQL 的三個坑

排程裡跑的 SQL 通常沒人盯著看,它每小時醒來一次,算一批彙總寫回另一張表,平常結果看起來都對,出問題的時候也不一定會報錯,數字只會慢慢歪掉。最近在一個系統的設計審查裡替 hourly rollup 作業補測試,實測出三個問題,單獨拿出來看都不難,混在一份看起來很正常的 SQL 裡就不容易發現。下面用簡化過的表重現,範例都在 PostgreSQL 16 上跑過。

更正(2026-11-02 18:45):第二節的失敗原因改為 WITH 裡的 DELETE 和主查詢 INSERT 的執行順序沒有保證;第三節的修法改成只搶一次鎖,用同一個判斷決定 DELETE 和 INSERT 要不要執行。修正建議:Codex(gpt-6-astra),Claude 驗證。

先準備兩張表,一張原始注單,一張每小時彙總:

CREATE TABLE bets (
  id         bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL,
  amount     numeric NOT NULL
);

CREATE TABLE rollup_hourly (
  bucket timestamptz PRIMARY KEY,
  n      bigint,
  total  numeric
);

INSERT INTO bets (created_at, amount) VALUES ('2026-09-01 10:30:00+00', 100);

一、timestamp 和 timestamptz 比較時,會套用 session 時區

rollup 每次要先決定算哪個小時,常見寫法是把現在時間轉成 UTC 再 date_trunc。問題出在 timestamptz AT TIME ZONE 'UTC' 回傳 timestamp without time zone,拿它和 timestamptz 欄位比較時,PostgreSQL 會用目前 session 的 TimeZone 把它轉回 timestamptz,視窗就跟著時區平移。

SET TimeZone = 'Asia/Taipei';

WITH w AS (
  SELECT date_trunc('hour', timestamptz '2026-09-01 10:30:00+00' AT TIME ZONE 'UTC') AS ws
)
SELECT pg_typeof(w.ws) AS ws_type,
       (SELECT count(*) FROM bets
         WHERE created_at >= w.ws
           AND created_at <  w.ws + interval '1 hour') AS hits
FROM w;
           ws_type           | hits
-----------------------------+------
 timestamp without time zone |    0

同一段 SQL 換成 SET TimeZone = 'UTC',hits 是 1。原本要算的是 UTC 10:00 到 11:00,在台北時區的 session 裡實際比對的是台北時間 10:00 到 11:00,往前偏了八小時,那一筆注單就漏算了。開發機和 CI 多半是 UTC,測試一路綠燈,等到連線預設帶 Asia/Taipei,或有人用 psql 手動補跑,同一份 SQL 才算出不同的結果。

這件事我過去常提醒工程師:你手上的 timestamp 可能不是你以為的那個 timestamp,UTC 轉換在實作上的行為,也常和你想像的不一樣。在 UTC 主機上開發時通常看不出來,換到非 UTC 的主機,或者資料一路走到使用者端,問題就特別多。這裡的 AT TIME ZONE 是個典型例子,名字看起來像「轉成 UTC」,實際上它把時區資訊拿掉了,後面怎麼解讀,交給當下 session 的設定決定。

修法是讓視窗邊界從頭到尾都留在 timestamptz,最直接的做法是再補一次 AT TIME ZONE 'UTC' 轉回來:

WHERE created_at >= w.ws AT TIME ZONE 'UTC'
  AND created_at <  (w.ws + interval '1 hour') AT TIME ZONE 'UTC'

PostgreSQL 12 之後 date_trunc 也可以帶時區參數,date_trunc('hour', now(), 'UTC') 回傳的就是 timestamptz,中間不會出現沒有時區的值,讀起來也比較不容易誤會。

二、寫在 CTE 裡的 DELETE,同一句的 INSERT 看不到

rollup 要能重跑,常見做法是先刪掉這個 bucket 的舊結果再重新插入。寫成一句看起來很合理:

WITH del AS (
  DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00'
)
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;

bucket 裡已經有資料時,這句就會失敗:

ERROR:  duplicate key value violates unique constraint "rollup_hourly_pkey"
DETAIL:  Key (bucket)=(2026-09-01 10:00:00+00) already exists.

PostgreSQL 文件寫明,WITH 裡的資料修改語句和主查詢是同時執行的,實際的執行順序無法預測。主查詢沒有用到 del 的結果,INSERT 做唯一性檢查時,del 不一定已經把舊的那一列刪掉,所以這種寫法只有 bucket 原本是空的時候才能確定成功,偏偏重跑的時候 bucket 一定已經有資料。

修法是拆成兩條語句,放在同一個 transaction 裡:

BEGIN;
DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00';
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;
COMMIT;

第二條語句看得到第一條的結果,整段仍然是原子的,中途失敗會一起 rollback。那個系統的排程是由 worker 讀 db/jobs/*.sql,整份檔案包在一個 transaction 裡執行,拆成兩句不需要額外處理。

三、單獨一行 pg_try_advisory_xact_lock 擋不住重疊執行

排程有時會重疊,上一輪還沒跑完,下一輪已經開始,或者兩台 worker 同時被叫醒。直覺的防法是在檔案開頭搶一個 advisory lock:

BEGIN;
SELECT pg_try_advisory_xact_lock(42) AS got;
DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00';
INSERT INTO rollup_hourly
SELECT date_trunc('hour', created_at), count(*), sum(amount)
FROM bets
GROUP BY 1;
COMMIT;

另一個 session 先拿著 42 這把鎖,再跑上面這段:

 got
-----
 f

DELETE 1
INSERT 0 1
COMMIT

鎖沒搶到,後面兩句照樣執行。pg_try_advisory_xact_lock 只回傳一個 boolean,純 SQL 檔案裡沒有 if,那個 false 沒有人讀,就只是一行查詢結果。

修法是只搶一次鎖,用同一個結果做判斷:沒搶到鎖,DELETE 和 INSERT 就都不做。純 SQL 沒有 if,可以用 DO 區塊:

BEGIN;

DO $$
BEGIN
  IF NOT pg_try_advisory_xact_lock(42) THEN
    RAISE NOTICE 'lock 42 is held, skipping this run';
    RETURN;
  END IF;

  DELETE FROM rollup_hourly WHERE bucket = '2026-09-01 10:00+00';

  INSERT INTO rollup_hourly
  SELECT date_trunc('hour', created_at), count(*), sum(amount)
  FROM bets
  WHERE created_at >= timestamptz '2026-09-01 10:00+00'
    AND created_at <  timestamptz '2026-09-01 10:00+00' + interval '1 hour'
  GROUP BY 1;
END
$$;

COMMIT;

同樣在另一個 session 持有鎖的情況下跑:

BEGIN
NOTICE:  lock 42 is held, skipping this run
DO
COMMIT

DELETE 和 INSERT 都沒有執行,舊的彙總原封不動;鎖沒有被占用時,兩句照常執行,bucket 換成新的結果。鎖只搶一次,DELETE 和 INSERT 一定一起執行或一起跳過,不會刪了舊資料卻沒插回去。要是讓每條語句各搶一次,持有鎖的一方剛好在兩句之間 commit,就會變成 DELETE 沒搶到、INSERT 搶到,bucket 已有資料時 INSERT 撞上主鍵,整個 transaction 失敗。

代價是跳過的時候不會報錯,只留下一則 NOTICE,要知道這一輪有沒有真的跑,得看 log 或另外記錄。如果希望重疊時排隊等前一輪做完,改用會阻塞的 pg_advisory_xact_lock 就好,這時鎖放在開頭那一行也有效,因為它會等到拿到為止。

另外一個:分區表的父表不能設 storage parameter

這是同一次審查裡另外看到的。在分區表上設 fillfactor,寫在父表會直接報錯:

CREATE TABLE p (id int, d date) PARTITION BY RANGE (d) WITH (fillfactor = 90);
ERROR:  cannot specify storage parameters for a partitioned table
HINT:  Specify storage parameters for its leaf partitions instead.

fillfactor 要設在每個分區上,新建分區的時候也得記得帶,不然新分區會回到預設值。

補充筆記

  • 手上的 timestamp 不一定是你以為的那個,UTC 轉換的實際行為也常和想像不同;在 UTC 主機上看不出來,到了非 UTC 主機或使用者端才出事。
  • timestamptz AT TIME ZONE 'UTC' 回傳的是 timestamp,拿它和 timestamptz 比較會套用 session 時區;視窗邊界全程留在 timestamptz,或用 PostgreSQL 12 之後帶時區參數的 date_trunc。
  • WITH 裡的 DELETE 和主查詢的執行順序沒有保證,同一句的 INSERT 可能在舊資料刪掉之前就撞上唯一鍵;要重跑就拆成兩條語句,包在同一個 transaction。
  • 單獨一行 pg_try_advisory_xact_lock 只是回傳 boolean,純 SQL 沒有流程控制;鎖只搶一次,用 DO 區塊的 IF 決定 DELETE 和 INSERT 要不要一起執行,每條語句各搶一次可能前後結果不同。
  • try-lock 跳過時不會報錯,要靠 NOTICE 或另外記錄才知道;想排隊就改用阻塞版 pg_advisory_xact_lock。
  • 分區表的 fillfactor 這類 storage parameter 只能設在葉分區上。

延伸閱讀

想法與技術判斷出自 Sheng,和 Claude 一起起草 · 範例在 PostgreSQL 16.13 重跑驗證,更正後的 DO 區塊在 PostgreSQL 16.15 重跑。