PostgreSQL 上資料插入時產生的duplicate key conflict

事發經過

起因於最近工作上遇到的一個小插曲,剛好寫完某一個服務以後,發現某一個整合測試會在特定的情況下發生錯誤。

在經過一系列的測試以後,發現整合測試的錯誤只會發生在測試資料庫剛被初始化完成時;在進一步追下去發現,每次噴出來的錯誤訊息為 ERROR: duplicate key value violates unique constraint "files_pkey",這個錯誤其實蠻奇妙的,因為這張表的id 我是讓其內部自動產生的(auto increment),但在資料庫執行一段時間以後,發現同樣的測試又可以pass了。

最後追下去發現stckoverflow上,其它人也有遇到類似的問題,照著答案中的方向去試了以後就順利解決了。

解決方案

在PostgreSQL中,如果我們設定primary key 為serial or bigserial時,PostgreSQL內部會產生一組relation用來管理對應的primary key 其下一組資料寫入時,auto increment 後的值為何。

由於我在產生測試資料時是有包含id的值,所以造成對應的那張表的id sequence誤判了當新的一筆資料要再被寫入時對應的id值,但再過了一段時間以後,PostgreSQL內部又會重新sync id sequence,所以才會有資料庫運行一段時間後,測試又可以通過了的情況。

最後的解決方案就是重新整理測試資料,讓其不包含id,所以那張表的id的管理就完全由其資料庫內部來作,自然就解決了id sequence out of sync的問題了。
(stackoverflow 文章中有另外提到其它手動的方式,來讓 primary key sequence重新對應上表的資料)

References