ON CONFLICT DO UPDATEが「cannot affect row a second time」で落ちる原因は1つだけ

PostgreSQL
SQL

Sep 4, 2020

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

起きたこと

ON CONFLICT (column) DO UPDATE を使ったUPSERTで以下のエラーが出た。

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

ネットで見かける解説記事だと「競合キーが複数該当している」「インデックスの設定を見直せ」みたいな曖昧な理由が並んでいることが多いけど、実際は違う。

原因は1つしかない

同一のINSERTコマンドが渡す行の中に、同じ一意制約キーを持つ行が2つ以上ある、これだけ。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100)
);

INSERT INTO users (id, username, email)
VALUES
    (1, 'john_doe', 'john.doe@example.com'),
    (2, 'john_doe', 'john.doe_new@example.com')
ON CONFLICT (username) DO UPDATE
SET email = EXCLUDED.email;

このVALUES句には username = 'john_doe' の行が2つある。PostgreSQLは1コマンド内で同じ行（既存行にせよ、これから挿入する行にせよ）に対してDO UPDATEを2回適用できないので、ここでエラーになる。

エラーメッセージのHINTがそのまま答えを言っている。「同一コマンド内で挿入しようとしている行に、制約対象の値の重複がないか確認しろ」と。インデックスの張り方が悪いとか、競合キーの設計が悪いとかいう話ではない。

よくある誤った対処法

インデックスを見直す → 意味がない。一意インデックス（arbiter）自体はすでに正しく機能している。問題はデータ側にある
競合キーの列を変更する → 根本原因と無関係。キーをどう定義し直しても、渡す行に重複があれば同じエラーが出る

正しい直し方

INSERTに渡す前に重複を排除する。DISTINCT ON を使うのが手軽。

INSERT INTO users (id, username, email)
SELECT DISTINCT ON (username) id, username, email
FROM (VALUES
    (1, 'john_doe', 'john.doe@example.com'),
    (2, 'john_doe', 'john.doe_new@example.com')
) AS t(id, username, email)
ORDER BY username, id DESC
ON CONFLICT (username) DO UPDATE
SET email = EXCLUDED.email;

ORDER BY username, id DESC で同一usernameのうち後勝ちにしている。要件次第で並び順は変える。

行数が少ないなら単純にコマンドを分けてもいい。

INSERT INTO users (id, username, email) VALUES (1, 'john_doe', 'john.doe@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

INSERT INTO users (id, username, email) VALUES (2, 'jane_doe', 'jane.doe@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

ただしバッチ処理で件数が多い場合は現実的じゃないので、DISTINCT ON かアプリ側での重複排除が定石。

補足：行の順序で別のエラーになるケースもあるらしい

pgsql-hackersのメーリングリストで、同じ意味に見える2パターンのINSERTが違うエラーコードを返す件が議論されていた。

CREATE TABLE ioc (i int, UNIQUE(i));
INSERT INTO ioc VALUES (1);

-- パターン1
INSERT INTO ioc VALUES (1), (20) ON CONFLICT (i) DO UPDATE SET i = 20;
-- ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

-- パターン2
INSERT INTO ioc VALUES (20), (1) ON CONFLICT (i) DO UPDATE SET i = 20;
-- ERROR: duplicate key value violates unique constraint "ioc_i_key"

行の並び順によって「cannot affect row a second time」になるか「duplicate key value violates unique constraint」になるかが変わる、という報告。この挙動の内部的な理由まではメーリングリスト上でも完全には決着していなかったので、詳細未確認。実務上は「どちらのエラーが出ても原因（同一コマンド内の重複）は共通」と理解しておけば十分。

まとめ

このエラーを見たら、テーブル設計やインデックスを疑う前に、INSERT文に渡している行データ自体に重複がないかを見る。それだけで大体解決する。