postgres 序列問題、手動創建的 pid 和正確的序列重置
在過去的一年中,開發人員在我們的 postgres 9.6 數據庫中遇到了序列問題,他們停止工作,因此他們採取了一些方法,例如使用基於最後插入記錄的 pid 手動創建的 pid 插入行,並使用以下程式碼重置序列:
SELECT pg_catalog.setval(pg_get_serial_sequence('mytable', 'pid'), (SELECT MAX(pid)+1 FROM mytable) );
我有足夠的經驗知道我們應該能夠依靠數據庫來創建我們的 id,並且基於最後一個最大值創建自己的 PID 並不是“最佳實踐”,也不是所有場景都安全的,儘管它通常適用於我們的日常使用。
他們第一次停止工作的原因是眾所周知的原因,例如恢復了一張表。但是,此時我認為手動程式碼和序列是相互踩踏的,序列重置程式碼並不牢固。很明顯,插入最後一個最大值會破壞序列,它只是有自己的遞增數字。
當我閱讀時,我想知道今天是否有人指出我正確的方向,以便在現有的表上恢復序列並完美地工作 - 即使程式碼到位,有時也會插入來自程式碼的 pid,基於最後一個最大值。(從長遠來看,當然,我完全知道最好刪除所有此類程式碼 - 但現在有辦法解決該程式碼嗎?)
包含在解決方案中的是 postgreSQL 9.6 中的一種方法,如果存在衝突,它可以自行重置序列 - 不確定是否可能,並且準備好接受更有經驗的講座 - 但這就是我在這裡的原因!
最後——這是讓我來到這裡的一個令人不安的事實——在 pg admin 中重置序列後,我在 pg admin 3 和 4 的表中看到了兩個 pid,這也顯示在 create 腳本中。在 psql 中執行 \d 時沒有顯示“第二個”PID 列,這很好 - 但我認為它可能是相關的。
更新 - 在最後一點,pg admin 中一個表的幽靈重複 PID 是因為同一列/表組合有兩個序列,第二個在某個時間創建以嘗試修復損壞的序列(例如 mytable_pid_seq 和mytable_pid_seq1)。不確定為什麼/如何允許在數據庫中發生這種情況。
包含在解決方案中的是 postgreSQL 9.6 中的一種方法,如果存在衝突,它可以自行重置序列 - 不確定是否可能,並且準備好接受更有經驗的講座 - 但這就是我在這裡的原因!
首先,真正的解決方法是讓程式碼始終使用序列,而不是和 的不一致
select 1+max(pk)
混合nextval('seqname')
。話雖這麼說,作為真正修復之前的創可貼解決方案,您可以在 INSERT 上為總是呼叫
nextval
序列的每一行觸發一個觸發器,這樣您就可以確定序列永遠不會落後,即使 INSERT 本身錯過了一個nextval
電話。如果該列有一個
DEFAULT nextval('seqname')
(手動設置或通過聲明設置),並且除了 的 之外還有SERIAL
一個觸發器呼叫,那隻會將序列推進兩個而不是一個。這對您的應用程序來說應該不是問題,因為由於回滾或記憶體,序列無論如何都會有漏洞。nextval``nextval``DEFAULT
呼叫
setval
withSELECT 1+max(pk) from table
也有效,但僅當沒有其他事務同時使用該序列時。所以在觸發器中這樣做聽起來不是一個好主意。通常這種調整隻在大容量載入之後進行(事實上,這就是pg_dump
當序列歸表所有時產生的)。