«ON CONFLICT DO UPDATE command cannot affect row a second time»: как мы теряли данные из-за этой ошибки
Привет! На связи Валерия Елпатьевская, ментор курса «Инженер данных» 👋🏻
Сегодня расскажу о том, с чем недавно сама столкнулась. Ситуация: мы потеряли часть данных, смотрим логи, а там:
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
Какой же second time? Как вообще такое возможно? Так вот рассказываю.
Допустим, в батче пришли две записи с одним id:
INSERT INTO users (id, updated_at, name)
SELECT id, updated_at, name
FROM staging
ON CONFLICT (id) DO UPDATE
SET
updated_at = EXCLUDED.updated_at,
name = EXCLUDED.name;
Если staging содержит несколько строк с одинаковым id, PostgreSQL завершит такую операцию ошибкой, как выше.
Причина в том, что внутри одного INSERT несколько входных строк пытаются изменить одну и ту же target-строку. PostgreSQL не выбирает автоматически, какая из них является истиной. Он не пытается угадать, а просто отказывается выполнять операцию.
Причём проблема особенно неприятна, когда данные приходят батчами, так как дубликаты могут быть незаметны внутри отдельных партий, но появиться после объединения нескольких источников/партиций.
Поэтому перед UPSERT лучше явно определить правило разрешения конфликта и избавиться от дубликатов:
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY id
ORDER BY updated_at DESC
) AS rn
FROM staging
)
INSERT INTO users (id, updated_at, name)
SELECT id, updated_at, name
FROM ranked
WHERE rn = 1
ON CONFLICT (id) DO UPDATE
SET
updated_at = EXCLUDED.updated_at,
name = EXCLUDED.name;
Теперь для каждого id в батче остаётся ровно одна запись — например, самая свежая по updated_at.
📌 Вывод: ON CONFLICT решает конфликт между входящей и целевой строкой, но не занимается бизнес-логикой выбора между несколькими строками с одним ключом.
Ставьте 🔥 и пересылайте тем, кто мог бы столкнуться с этой проблемой ❤️
📈 Симулейтив | 📱 ВК | 📱 YouTube | 📱 Канал о DS
Привет! На связи Валерия Елпатьевская, ментор курса «Инженер данных» 👋🏻
Сегодня расскажу о том, с чем недавно сама столкнулась. Ситуация: мы потеряли часть данных, смотрим логи, а там:
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
Какой же second time? Как вообще такое возможно? Так вот рассказываю.
Если вы делаете `INSERT ... ON CONFLICT DO UPDATE` большими батчами (пакетами данных), дедуплицируйте входные данные до `UPSERT`.
Допустим, в батче пришли две записи с одним id:
INSERT INTO users (id, updated_at, name)
SELECT id, updated_at, name
FROM staging
ON CONFLICT (id) DO UPDATE
SET
updated_at = EXCLUDED.updated_at,
name = EXCLUDED.name;
Если staging содержит несколько строк с одинаковым id, PostgreSQL завершит такую операцию ошибкой, как выше.
Причина в том, что внутри одного INSERT несколько входных строк пытаются изменить одну и ту же target-строку. PostgreSQL не выбирает автоматически, какая из них является истиной. Он не пытается угадать, а просто отказывается выполнять операцию.
Причём проблема особенно неприятна, когда данные приходят батчами, так как дубликаты могут быть незаметны внутри отдельных партий, но появиться после объединения нескольких источников/партиций.
Поэтому перед UPSERT лучше явно определить правило разрешения конфликта и избавиться от дубликатов:
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY id
ORDER BY updated_at DESC
) AS rn
FROM staging
)
INSERT INTO users (id, updated_at, name)
SELECT id, updated_at, name
FROM ranked
WHERE rn = 1
ON CONFLICT (id) DO UPDATE
SET
updated_at = EXCLUDED.updated_at,
name = EXCLUDED.name;
Теперь для каждого id в батче остаётся ровно одна запись — например, самая свежая по updated_at.
📌 Вывод: ON CONFLICT решает конфликт между входящей и целевой строкой, но не занимается бизнес-логикой выбора между несколькими строками с одним ключом.
Ставьте 🔥 и пересылайте тем, кто мог бы столкнуться с этой проблемой ❤️
📈 Симулейтив | 📱 ВК | 📱 YouTube | 📱 Канал о DS