PostgreSQLで「存在すれば更新、なければ追加」を実装するときは、アプリ側でSELECTして分岐するより、INSERT文のON CONFLICTを使う方が同時実行に強い設計にしやすくなります。
SELECTしてからINSERTする問題
存在確認とINSERTが別SQLだと、その間に別トランザクションが同じ行を作成する可能性があります。これが競合状態です。
ON CONFLICTを使う
次のSQLでは、user_idが重複した場合に既存行を更新します。
INSERT INTO user_preferences (user_id, theme)
VALUES (?, ?)
ON CONFLICT (user_id)
DO UPDATE SET
theme = EXCLUDED.theme,
updated_at = CURRENT_TIMESTAMP
RETURNING user_id, theme, updated_at;
EXCLUDEDは、今回INSERTしようとした値を参照するための名前です。RETURNINGを使うと、保存後の値を追加のSELECTなしで取得できます。
Javaから使う
JDBCではPreparedStatementに上のSQLを渡し、user_idとthemeをバインドしてexecuteQueryします。RETURNINGを付けているため、ResultSetから保存後の値を取得できます。
重複を無視する場合
二重登録を防ぎたいだけなら、DO NOTHINGを使います。
INSERT INTO processed_requests (request_id)
VALUES (?)
ON CONFLICT (request_id)
DO NOTHING;
Webhookやキュー処理の重複登録防止に使えます。
一意制約が必要
ON CONFLICTで重複を判定するには、PRIMARY KEYやUNIQUE制約など、DB側で一意性を定義しておく必要があります。
PostgreSQLではPRIMARY KEYやUNIQUE制約を作成すると、一意性を保証するユニークB-treeインデックスも自動作成されます。
NULLの注意
UNIQUE制約では、デフォルトではNULL同士は同じ値として扱われません。NULLも重複として扱いたい場合はNULLS NOT DISTINCTを検討します。
同時実行時のポイント
PostgreSQLのデフォルト分離レベルRead Committedでは、ON CONFLICT DO UPDATEを使った各行は、無関係なエラーがなければINSERTかUPDATEのどちらかになります。
単純なUPSERTなら、アプリ側のSELECT分岐よりDBの一意制約とON CONFLICTに任せる方が競合を避けやすくなります。
まとめ
- 重複判定はPRIMARY KEYやUNIQUE制約に任せる
- 重複を無視するならDO NOTHING
- 重複時に更新するならDO UPDATE
- 今回送った値はEXCLUDEDから参照する
- 保存後の値はRETURNINGで取得する
- JavaではPreparedStatementからそのまま実行できる