PostgreSQL 排程 SQL 的三個坑

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

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

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 裡的資料修改語句和主查詢是同時執行的，共用同一個 snapshot，彼此看不到對方對資料表造成的變更。del 刪掉的那一列，對 INSERT 的唯一性檢查來說還在，所以這種寫法只有 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 沒有人讀，就只是一行查詢結果。

修法是把搶鎖放進每條語句自己的條件裡，沒搶到鎖，這句就不做事：

BEGIN;

WITH scope AS (
  SELECT timestamptz '2026-09-01 10:00+00' AS ws
  WHERE pg_try_advisory_xact_lock(42)
)
DELETE FROM rollup_hourly r
USING scope
WHERE r.bucket = scope.ws;

WITH scope AS (
  SELECT timestamptz '2026-09-01 10:00+00' AS ws
  WHERE pg_try_advisory_xact_lock(42)
)
INSERT INTO rollup_hourly
SELECT scope.ws, count(*), sum(amount)
FROM bets, scope
WHERE created_at >= scope.ws
  AND created_at <  scope.ws + interval '1 hour'
GROUP BY scope.ws;

COMMIT;

同樣在另一個 session 持有鎖的情況下跑，結果是 DELETE 0、INSERT 0 0，兩句都沒有動到資料。兩條語句各搶一次不會互相打架，同一個 transaction 裡重複取得同一把 advisory lock 會直接回傳 true，所以兩句拿到的結果一致，會一起執行或一起跳過，不會刪了舊資料卻沒插回去。

代價是跳過的時候不會有任何錯誤，要知道這一輪有沒有真的跑，得看影響列數或另外記錄。如果希望重疊時排隊等前一輪做完，改用會阻塞的 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 和主查詢共用 snapshot，同一句的 INSERT 看不到刪除結果；要重跑就拆成兩條語句，包在同一個 transaction。
單獨一行 pg_try_advisory_xact_lock 只是回傳 boolean，純 SQL 沒有流程控制；把它放進每條語句的 WHERE，同 transaction 重取會回 true，兩句結果一致。
try-lock 跳過時不會報錯，要靠影響列數或另外記錄才知道；想排隊就改用阻塞版 pg_advisory_xact_lock。
分區表的 fillfactor 這類 storage parameter 只能設在葉分區上。

PostgreSQL 排程 SQL 的三個坑