# PostgreSQL 排程 SQL 的三個坑

> PostgreSQL 排程的 rollup 作業實測出三個問題：session 時區讓統計視窗偏移、CTE 裡的 DELETE 對 INSERT 不可見、單獨一行 advisory lock 擋不住重疊執行。

原文：https://sheng.page/posts/postgres-scheduled-sql-pitfalls/ · 發布：2026-10-01 · 作者：Sheng

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

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

```sql
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`，視窗就跟著時區平移。

```sql
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;
```

```text
           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'` 轉回來：

```sql
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 的舊結果再重新插入。寫成一句看起來很合理：

```sql
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 裡已經有資料時，這句就會失敗：

```text
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 裡：

```sql
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：

```sql
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` 這把鎖，再跑上面這段：

```text
 got
-----
 f

DELETE 1
INSERT 0 1
COMMIT
```

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

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

```sql
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`，寫在父表會直接報錯：

```sql
CREATE TABLE p (id int, d date) PARTITION BY RANGE (d) WITH (fillfactor = 90);
```

```text
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 只能設在葉分區上。

## 延伸閱讀

- [Postgres AT TIME ZONE 'UTC' does NOT do what you think it does](https://bookofrevenue.com/blog/6ab81e9a97a13f0001f7e4e1/postgres-at-time-zone-u-does-not-do-what-you-think-it-does)：一篇短文，講的就是第一節這個坑，`AT TIME ZONE 'UTC'` 會把型別從 `timestamptz` 換成 `timestamp`。
- PostgreSQL 文件〈[Time Zone Conversion](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT)〉與〈[date_trunc](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC)〉：`AT TIME ZONE` 各種輸入型別的回傳值，以及 `date_trunc` 的 `time_zone` 參數。
- PostgreSQL 文件〈[Data-Modifying Statements in WITH](https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING)〉：`WITH` 子語句共用同一個 snapshot、彼此看不到變更的原文說明。
- PostgreSQL 文件〈[Advisory Lock Functions](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS)〉：`pg_try_advisory_xact_lock` 與阻塞版的行為差異。
- PostgreSQL 文件〈[CREATE TABLE: Storage Parameters](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS)〉：分區表不支援 storage parameter，只能設在葉分區。

> 想法與技術判斷出自 Sheng，和 Claude 一起起草 · 範例在 PostgreSQL 16.13 重跑驗證。