PostgreSQL SERIAL 序列不隨 OVERRIDING SYSTEM VALUE 推進

測試 seed 用 OVERRIDING SYSTEM VALUE 手動指定 ID，插入成功，但之後的一般 INSERT 卻拿到 nextval=1，造成 duplicate key 錯誤。問題只在「全量執行」時出現，單獨執行單一模組時正常。

---

📖 背景：SERIAL 和序列（Sequence）是什麼？

SERIAL 是「自動產生遞增 ID」的語法糖，背後 PostgreSQL 會建一個獨立的計數器，叫做序列（Sequence）：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,  -- 等同於建一個叫 products_id_seq 的計數器
    name TEXT
);

每次 INSERT 不指定 id，就呼叫 nextval('products_id_seq')，計數器 +1，回傳新 ID：

第 1 次 INSERT → nextval() → 計數器: 0→1 → id=1
第 2 次 INSERT → nextval() → 計數器: 1→2 → id=2

[!important] 關鍵：序列是獨立計數器，不追蹤表裡的最大值
序列只管自己數到幾，不會去看表裡現在有哪些 ID。這是造成本問題的根源。

---

🔍 復現場景

-- Step 1：清空表，序列計數器歸零
TRUNCATE products RESTART IDENTITY CASCADE;
-- 現在：計數器 = 0

-- Step 2：手動指定 id=1 插入（繞過計數器）
INSERT INTO products (id, name) OVERRIDING SYSTEM VALUE VALUES (1, 'Test Product');
-- 成功！但計數器還是 0，因為根本沒呼叫 nextval()

-- Step 3：一般 INSERT（讓系統自動產生 id）
INSERT INTO products (name) VALUES ('Another Product');
-- → nextval() 被呼叫，計數器 0→1，回傳 id=1
-- → ERROR: duplicate key value violates unique constraint "products_pkey"
-- → id=1 已經有人用了！

---

🚨 根本原因：OVERRIDING SYSTEM VALUE 繞過計數器

OVERRIDING SYSTEM VALUE 的設計就是略過序列，直接把你指定的值寫進去，完全不碰計數器。

類比：就像一個叫號機，你直接喊「89 號請到 3 號窗口」，但叫號機還停在 1。下個客人按按鈕，拿到的還是 1 號，但 89 號已經有人在那裡了。

TRUNCATE RESTART IDENTITY → 計數器歸零 = 0
OVERRIDING SYSTEM VALUE 插入 id=1 → 計數器還是 0（沒動！）
一般 INSERT → nextval() → 計數器 0→1 → 回傳 1 → 衝突！

---

✅ 解法：用 setval 手動推進計數器

seed 塞完資料後，把計數器手動對齊到表裡最大的 ID：

-- 找出目前最大 id，把計數器設到那裡
SELECT setval('products_id_seq', (SELECT MAX(id) FROM products));
SELECT setval('customers_id_seq', (SELECT MAX(id) FROM customers));
SELECT setval('orders_id_seq', (SELECT MAX(id) FROM orders));
-- ... 其他有 SERIAL 欄位的表

setval('products_id_seq', 5) 的意思是：把這個計數器強制設定為 5，下次 nextval() 就會從 6 開始。

[!tip] 通用版本（自動處理所有資料表）
可以封裝成一段 PL/pgSQL，放在 seed script 最末尾執行：

DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT schemaname, tablename, attname
    FROM pg_stats
    WHERE tablename IN ('products','customers','orders','live_sessions')
      AND attname = 'id'
  LOOP
    EXECUTE format(
      'SELECT setval(pg_get_serial_sequence(''%I.%I'', ''id''), COALESCE(MAX(id), 0) + 1, false) FROM %I.%I',
      r.schemaname, r.tablename, r.schemaname, r.tablename
    );
  END LOOP;
END $$;

⚠️ pg_stats 是統計視圖，需要 ANALYZE 才有資料。在剛 TRUNCATE+INSERT 的測試環境可能為空，導致這段什麼都不執行。建議優先用上方的簡易寫法（直接列舉資料表呼叫 setval），或改查 pg_sequences 系統目錄。

---

⚠️ 踩坑：為什麼只在全量執行時才爆？

測試用 filepath.Walk 按字母順序跑：

tests/stories/
  livestream/   ← 先跑
  order/        ← 後跑
  product/

全量執行的時序：

1. livestream/ seed 用 OVERRIDING SYSTEM VALUE 插入 orders id=1，計數器沒動
2. order/ 測試建立訂單，nextval() 回傳 1 → 撞到 livestream 留下的 id=1 → 爆炸

單獨跑 order/ 時：

• TRUNCATE 重置計數器，沒有其他模組污染，正常執行，掩蓋了這個 bug

這種「單跑 OK、全跑壞」的 bug 特別難找，因為很難想到是執行順序造成的。

排查方式：加 -v 觀察執行順序，或臨時只跑 livestream/ + order/ 組合來重現。

---

📝 重點整理

觀念	記住這一句
SERIAL	背後是獨立計數器，不是「自動看表裡最大值」
OVERRIDING SYSTEM VALUE	繞過計數器直接寫值，計數器不會跟著動
TRUNCATE RESTART IDENTITY	計數器歸零，但不追蹤你之後手動塞的 ID
setval	手動把計數器推進到正確位置的方法
這類 bug 的特徵	只在特定執行順序下出現，單一模組測試看不出來