«ON CONFLICT DO UPDATE command cannot affect row a second time»: почему пакетный UPSERT падает целиком и как его чинить

Статья для разработчиков и админов, которые льют в PostgreSQL данные пакетными UPSERT-ами: заявки с сайта, выгрузки из CRM, синхронизацию с внешним сервисом. Объясню, почему из-за единственного повтора ключа внутри пакета падает весь оператор с ошибкой «cannot affect row a second time» и почему Postgres принципиально не выбирает «последнюю» строку сам. Покажу три способа схлопнуть вход и где проходит граница между ON CONFLICT и MERGE. Разбор сделан на реальной выгрузке заявок с сайта небольшого рекламного агентства, с командами и цифрами.

Один повтор ключа — и весь пакет в откате

Типичная картина: скрипт каждые 15 минут забирает заявки с сайта и заливает их в базу одним оператором. В какой-то момент в логе появляется вот это. Приложение повторяет тот же пакет, получает ту же ошибку, повторяет ещё раз, а утром менеджеры открывают отчёт и видят заявки только до вчерашнего вечера. При этом ошибка полностью детерминированная: сколько ни повторяй, результат будет тем же.

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.

Код ошибки psql по умолчанию не показывает. Чтобы его увидеть, включите подробный вывод командой \set VERBOSITY verbose, и первая строка станет такой:

ERROR:  21000: ON CONFLICT DO UPDATE command cannot affect row a second time

Не все сразу понимают главное: дело не в том, что «часть строк не загрузилась». Оператор INSERT атомарен, поэтому падает он целиком. Из пакета в пятьсот строк в базу не попадает ни одна, даже те 498, у которых с ключами всё в порядке. Один дубль убивает весь пакет. Если за один прогон выгружаются заявки за три месяца, из-за одной «двойной» заявки не обновится вообще ничего.

SQLSTATE 21000 относится к классу 21 «Cardinality Violation», имя условия — cardinality_violation. В тот же класс попадает, например, подзапрос, который вернул несколько строк там, где ожидалась одна. Смысл общий: в этом контексте получено недопустимое количество строк. Это не баг, не регрессия PostgreSQL 18 и не «странность планировщика», а документированное поведение. Оно не менялось с версии 9.5, в которой появился ON CONFLICT.

• Ошибка воспроизводится стабильно: пока вход не изменён, повторы бесполезны.
• Падает весь оператор, а не проблемная строка, поэтому частичной загрузки не бывает.
• Класс ошибки — 21000 cardinality_violation. Ловите её по SQLSTATE, а не по тексту.
• К версии PostgreSQL поведение не привязано: в 9.5, 13, 16 и 18 оно одинаковое.

Почему Postgres не берёт «последнее значение» сам

Первый вопрос, который мне обычно задают: «Почему нельзя просто применить обновления по очереди?» Документация отвечает прямо: «INSERT with an ON CONFLICT DO UPDATE clause is a “deterministic” statement. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises». Иначе говоря, детерминированность — сознательное проектное решение, а не недоработка.

Если подумать, решение правильное. Строки внутри одного INSERT не обрабатываются в гарантированном порядке. В INSERT ... SELECT без ORDER BY порядок выбирает планировщик, и он может измениться после ANALYZE, при переходе на другой план или после обновления версии. Если бы Postgres молча брал «последнюю» строку, запрос мог бы на тестовом стенде записывать одно значение, а на проде другое, и отладить это было бы невозможно. Громкая ошибка лучше, чем тихо разъехавшиеся данные.

Второй важный момент — как определяется «та же строка». Повтор ищется не по всем полям, а по атрибутам арбитражного индекса (arbiter index), который выводится из conflict_target. В документации сказано: «All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes». Арбитр можно назвать и явно через ON CONSTRAINT имя. Для DO UPDATE указывать conflict_target обязательно, а в DO NOTHING его можно опустить. Если арбитр — (form_id, email), то две заявки с одинаковыми form_id и email считаются дублями, даже если у них разные телефон, комментарий и время отправки. Различия в остальных полях конфликт не снимают.

Третья ловушка, на которую регулярно попадаются: ON CONFLICT DO NOTHING в той же ситуации не падает. Первая строка из группы дублей вставится, остальные конфликтуют уже с ней и молча отбрасываются, а если ключ уже есть в таблице, отбрасывается вся группа. Люди меняют DO UPDATE на DO NOTHING, ошибка пропадает, все довольны. Через месяц выясняется, что статус и телефон у повторных заявок не обновляются вовсе, а в CRM висят данные из первой, недозаполненной отправки формы. Тишина здесь хуже ошибки.

• `DO UPDATE` требует явного conflict_target (колонки, выражения или `ON CONSTRAINT`), `DO NOTHING` — нет.
• Дубль определяется по колонкам и выражениям арбитражного индекса, а не по всей строке.
• Псевдотаблица `excluded` содержит строку, предложенную к вставке, а сама таблица (или её алиас) — существующую строку.
• Результаты BEFORE INSERT-триггеров уже отражены в значениях `excluded`. Это документировано и иногда объясняет «мистические» дубли.
• Исключающие ограничения (exclusion constraints) арбитрами для `DO UPDATE` не бывают — только уникальные индексы и NOT DEFERRABLE-ограничения.

Разбор: выгрузка заявок в «Бренд-Мастерской», 3 214 строк

Заказчик — рекламное агентство «Бренд-Мастерская», 8 рабочих мест. Заявки приходят с сайта агентства: форма «Обсудить проект», квиз по стоимости брендинга и виджет обратного звонка. Для отчётов менеджеров и директора их складывают в PostgreSQL 18 на небольшой виртуалке с Debian (2 vCPU, 4 ГБ RAM). Приёмник — таблица leads с уникальным индексом ux_leads_form_email на (form_id, lower(btrim(email))). Раз в 15 минут скрипт на Python забирает через API сайта заявки за последние 90 дней, раскладывает их по пакетам по 500 строк и заливает одним оператором через unnest массивов. Так всё проработало год, а потом к сайту подключили квиз, и раз в два-три дня отчёт по заявкам начал «замерзать».

На диагностику ушло минут двадцать. Сначала я достал из лога текст упавшего оператора. Для этого нужен log_min_error_statement на уровне error (это значение по умолчанию) и, чтобы увидеть значения параметров, ненулевой log_parameter_max_length_on_error. Потом положил вход в staging-таблицу и прогнал простой поиск повторов внутри пакета — ровно по тем выражениям, что в арбитражном индексе:

SELECT form_id, lower(btrim(email)) AS k, count(*), array_agg(phone)
FROM staging_leads
GROUP BY 1, 2
HAVING count(*) > 1
ORDER BY 3 DESC
LIMIT 20;

Результат: из 3 214 строк выгрузки 46 давали 21 повторяющийся ключ. Причина оказалась до обидного бытовой. Квиз отправлял заявку дважды: сначала после ввода email, потом после ввода телефона. К тому же посетители писали адрес как попало: Anna@Example.ru, anna@example.ru , ANNA@example.ru. В исходных данных это три разные строки, а для индекса с lower(btrim(...)) — одна. Разработчик, кстати, честно убирал дубли на входе, но по полю email как есть, без нормализации. Поэтому его дедупликация не находила ничего: её ключ не совпадал с ключом арбитражного индекса.

Лечение — схлопнуть вход в CTE через DISTINCT ON по тем же выражениям с явным правилом выбора победителя. Приоритет получает строка с заполненным телефоном, при равенстве — более свежая по submitted_at. Дополнительно в DO UPDATE добавили WHERE, чтобы не переписывать строки, в которых ничего не изменилось.

WITH src AS (
  SELECT DISTINCT ON (form_id, lower(btrim(email)))
         form_id, email, name, phone, status, submitted_at
  FROM   staging_leads
  ORDER  BY form_id, lower(btrim(email)),
            (phone IS NOT NULL) DESC, submitted_at DESC
)
INSERT INTO leads AS l (form_id, email, name, phone, status, submitted_at)
SELECT form_id, email, name, phone, status, submitted_at FROM src
ON CONFLICT (form_id, lower(btrim(email))) DO UPDATE
   SET name = excluded.name,
       phone = excluded.phone,
       status = excluded.status,
       submitted_at = excluded.submitted_at
 WHERE l.phone  IS DISTINCT FROM excluded.phone
    OR l.status IS DISTINCT FROM excluded.status
    OR l.name   IS DISTINCT FROM excluded.name;

За три месяца наблюдения выгрузка не упала ни разу. Приятный побочный эффект дал WHERE: число реально обновлённых строк за прогон упало с 3 214 до 10–60, прогон стал укладываться примерно в секунду вместо девяти, а таблица leads перестала пухнуть от мёртвых версий строк. UPDATE, который ничего не меняет, всё равно создаёт новую версию строки, пишет в WAL и добавляет работы autovacuum. При 96 прогонах в сутки на слабой виртуалке это заметно даже на таких объёмах.

• Ключ дедупликации в приложении должен совпадать с выражением арбитражного индекса символ в символ.
• Если в индексе есть `lower()` или `btrim()`, дедуплицировать нужно по ним же.
• Правило выбора победителя должно быть явным (ORDER BY), иначе оно неявное и однажды поменяется.
• `WHERE` в DO UPDATE отсекает пустые обновления: меньше WAL, меньше bloat, короче прогон.
• Если выгрузка идёт через параметры, включите `log_parameter_max_length_on_error`, иначе в логе будут только `$1`, `$2`.

Три рабочих способа схлопнуть пакет

Первый и чаще всего лучший способ — DISTINCT ON внутри CTE, как в примере выше. Он короткий, читаемый и заставляет явно записать правило приоритета в ORDER BY. Требование одно: выражения в DISTINCT ON должны совпадать с первыми выражениями в ORDER BY, иначе Postgres откажется выполнять запрос. Я использую этот способ в большинстве случаев.

Второй способ — row_number(). Он нужен, когда правило выбора победителя сложнее одного ORDER BY, например: «берём заявку с телефоном и согласием на обработку данных, если таких нет — самую свежую». Читается хуже, зато в окно можно встроить CASE и оставить в коде комментарий, почему приоритет именно такой.

WITH ranked AS (
  SELECT s.*,
         row_number() OVER (
           PARTITION BY form_id, lower(btrim(email))
           ORDER BY (phone IS NOT NULL AND consent) DESC,
                    submitted_at DESC, id DESC
         ) AS rn
  FROM staging_leads s
)
INSERT INTO leads (form_id, email, name, phone, status)
SELECT form_id, email, name, phone, status FROM ranked WHERE rn = 1
ON CONFLICT (form_id, lower(btrim(email))) DO UPDATE
   SET phone = excluded.phone, status = excluded.status;

Третий способ — агрегация, и про него забывают чаще всего. Правильный ответ на дубль — далеко не всегда «взять одну строку и выбросить остальные». Если в пакете лежат двадцать заявок одной рекламной кампании за день, для сводной таблицы нужно их количество, а не «последняя». Тогда дедупликация превращается в честный GROUP BY с count(), sum() или max(). Смешение двух семантик — «победитель» и «свёртка» — источник самых неприятных багов: ошибка пропадает, а данные молча становятся неверными.

INSERT INTO lead_stats_daily (campaign, day, leads)
SELECT coalesce(utm_campaign, '(none)'), submitted_at::date, count(*)
FROM   staging_leads
GROUP  BY 1, 2
ON CONFLICT (campaign, day) DO UPDATE
   SET leads = excluded.leads;

Обратите внимание на SET leads = excluded.leads, а не lead_stats_daily.leads + excluded.leads. Выгрузка каждый раз забирает заявки за весь период заново, поэтому значение нужно заменять, а не прибавлять, иначе счётчики будут расти с каждым прогоном. Прибавление подходит только для инкрементальной загрузки, где каждая строка приходит ровно один раз.

Есть и четвёртый вариант — дедупликация на стороне приложения: обычный словарь с ключом-кортежем перед формированием пакета. Базу он нагружает меньше и позволяет залогировать выброшенные строки, а логировать их стоит: частые дубли обычно означают проблему на сайте, например двойную отправку формы, о которой нужно сказать разработчику. Минус один, но серьёзный: нормализацию ключа придётся повторить в коде, и при первом же изменении схемы она разойдётся с определением индекса. Поэтому я предпочитаю дедупликацию в SQL: там правило одно и хранится рядом с индексом.

• `DISTINCT ON` — вариант по умолчанию, когда из нескольких строк нужна одна.
• `row_number()` — когда правило приоритета составное или условное.
• `GROUP BY` с агрегатами — когда дубли нужно считать или складывать, а не выбирать.
• Дедупликация в приложении — когда нужен лог выброшенных строк и минимальная нагрузка на СУБД.
• При полной перевыгрузке агрегаты заменяют значение, при инкрементальной — прибавляют к нему.

Где обжигаются повторно: неочевидные грабли

Первые и самые частые грабли — расхождение бизнес-ключа и ключа индекса, ровно как в разборе выше. Уникальный индекс по выражению (lower(email), btrim(phone)) или по колонке типа citext живёт своей жизнью. Проверять нужно не то, что вы считаете ключом, а то, что реально лежит в pg_indexes. Команда \d+ имя_таблицы в psql занимает три секунды и снимает половину вопросов.

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'leads';

Вторые грабли — NULL в уникальном ключе. По умолчанию NULL-значения в уникальном индексе не считаются равными друг другу, поэтому строки с NULL в ключевой колонке не конфликтуют и при UPSERT просто множатся. С PostgreSQL 15 есть UNIQUE NULLS NOT DISTINCT, которое меняет это поведение на противоположное. Если индекс объявлен с NULLS NOT DISTINCT, две заявки без email (например, из виджета обратного звонка) внезапно становятся дублями и дают ту самую 21000. Обратная ситуация — индекс обычный, а вы рассчитываете, что NULL-значения схлопнутся, — без единой ошибки приводит к росту мусора.

Третьи грабли — частичный уникальный индекс. Если он создан как ... WHERE deleted_at IS NULL, предикат нужно повторить в ON CONFLICT: ON CONFLICT (form_id, email) WHERE deleted_at IS NULL DO UPDATE .... Без предиката Postgres не выведет ваш индекс как арбитражный и ответит ошибкой «there is no unique or exclusion constraint matching the ON CONFLICT specification». Учтите и обратное: если рядом есть обычный, не частичный уникальный индекс по тем же колонкам, при выводе арбитра он тоже подойдёт.

Четвёртые грабли — несколько уникальных индексов на таблице. ON CONFLICT разрешает конфликт только по указанному арбитру. Если пакет нарушает другой уникальный индекс, например по внешнему идентификатору заявки из плагина форм, вы получите не 21000, а обычную 23505 unique_violation. Это разные диагнозы и разное лечение: 21000 означает дубли внутри одного оператора, 23505 — конфликт по индексу, который не указан как арбитр, или конкурентную вставку. Путать их — значит чинить не то.

Пятые грабли — генерируемые значения и BEFORE-триггеры. Документация прямо предупреждает: «the effects of all per-row BEFORE INSERT triggers are reflected in excluded values». Триггер, который приводит телефон к формату +7XXXXXXXXXX или подставляет email из связанного контакта, вполне может склеить в один ключ строки, которые на входе были разными. Выгрузка при этом падает не всегда, а только на определённых данных, и выглядит это как мистика, пока не вспомнишь про триггер.

• `\d+ таблица` в psql — первое, что стоит посмотреть: точное определение уникальных индексов.
• 21000 — дубли внутри одного оператора, 23505 — конфликт по другому индексу или гонка с параллельной вставкой.
• Частичный индекс требует повторить предикат в conflict_target.
• `NULLS NOT DISTINCT` (PostgreSQL 15+) переводит NULL-значения из «никогда не конфликтуют» в «конфликтуют между собой».
• BEFORE INSERT-триггеры могут создать дубли уже после того, как вы очистили вход.

MERGE в PostgreSQL 15–18: когда он лучше и чем опасен

Документация PostgreSQL в разделе про INSERT прямо советует: «You may also wish to consider using MERGE, since that allows mixing INSERT, UPDATE, and DELETE within a single statement». Сам MERGE появился в PostgreSQL 15. В 17-й версии к нему добавили RETURNING с функцией merge_action() и нестандартную, но очень полезную ветку WHEN NOT MATCHED BY SOURCE, которой удобно помечать строки, пропавшие из выгрузки. В 18-й в RETURNING появились псевдонимы old и new, причём сразу для INSERT, UPDATE, DELETE и MERGE.

MERGE INTO leads l
USING (
  SELECT DISTINCT ON (form_id, lower(btrim(email))) *
  FROM staging_leads
  ORDER BY form_id, lower(btrim(email)), submitted_at DESC
) s
   ON l.form_id = s.form_id
  AND lower(btrim(l.email)) = lower(btrim(s.email))
WHEN MATCHED AND l.status IS DISTINCT FROM s.status THEN
  UPDATE SET status = s.status, phone = s.phone
WHEN NOT MATCHED THEN
  INSERT (form_id, email, name, phone, status)
  VALUES (s.form_id, s.email, s.name, s.phone, s.status)
-- PostgreSQL 18+: old/new в RETURNING
RETURNING merge_action(), l.email, old.status AS was, new.status AS became;

Но переход на MERGE не отменяет дедупликацию, и это главное. Документация MERGE описывает то же ограничение: если несколько строк источника совпадают с одной целевой строкой, изменить её можно только один раз. «If the repeated action is an INSERT, this will cause a uniqueness violation, while a repeated UPDATE or DELETE will cause a cardinality violation; the latter behavior is required by the SQL standard». На практике это значит следующее. Если целевая строка уже есть, вторая строка источника даст 21000 с текстом «MERGE command cannot affect row a second time» и подсказкой «Ensure that not more than one source row matches any one target row». Если целевой строки ещё нет, обе строки источника пойдут в WHEN NOT MATCHED, и вы получите 23505 от уникального индекса. А если уникального индекса нет, в таблицу молча лягут два дубля.

Есть и обратная сторона, о которой обычно молчат: при конкурентной записи ON CONFLICT надёжнее. В документации по уровням изоляции написано: «In Read Committed mode, each row proposed for insertion will either insert or update. Unless there are unrelated errors, one of those two outcomes is guaranteed». А про MERGE там же: «If MERGE attempts an INSERT and a unique index is present and a duplicate row is concurrently inserted, then a uniqueness violation error is raised; MERGE does not attempt to avoid such errors by restarting evaluation of MATCHED conditions». Проще говоря, если в таблицу параллельно пишет кто-то ещё, MERGE может получить 23505 там, где ON CONFLICT спокойно сделал бы UPDATE.

С WHEN NOT MATCHED BY SOURCE тоже нужна аккуратность. Эта ветка срабатывает для всех строк таблицы, которых нет в источнике. Если выгрузка берёт только последние 90 дней, без дополнительного условия вроде AND l.submitted_at > now() - interval '90 days' MERGE пометит как «пропавшие» все старые заявки. Сначала такой запрос стоит прогнать в транзакции с ROLLBACK и посмотреть на результат RETURNING.

Мой практический вывод такой. Если задача — «залить пакет и обновить существующее», а параллельная запись возможна (вебхук сайта, второй скрипт, ручные правки через админку), я беру INSERT ... ON CONFLICT DO UPDATE с дедупликацией на входе. Если задача сложнее — нужно ещё удалять или помечать исчезнувшие строки и получать отчёт по действиям, — беру MERGE со staging-таблицей и всё равно дедуплицирую источник. MERGE не замена ON CONFLICT, а инструмент для задачи другой формы.

• MERGE появился в PG 15; `RETURNING`, `merge_action()` и `WHEN NOT MATCHED BY SOURCE` — в PG 17; `old`/`new` в RETURNING — в PG 18.
• Повторное обновление одной целевой строки в MERGE тоже даёт 21000, а повторная вставка — 23505, так что дедупликация обязательна и там.
• При конкурентной записи ON CONFLICT гарантирует «вставку или обновление», MERGE — нет.
• `WHEN NOT MATCHED BY SOURCE` без ограничивающего условия затронет все строки таблицы, которых нет в выгрузке.
• Staging-таблица плюс MERGE — хорошая связка для полной синхронизации с удалением.

Приоритеты: что делать сегодня, а на что можно не тратить время

Первое и обязательное — свести вход к одной строке на конфликтующий ключ. Не «в среднем» и не «обычно дублей не бывает», а гарантированно: конструкцией в SQL или явным словарём в коде. Это единственное настоящее исправление, всё остальное — обвязка. Если ошибка после этого всё равно появляется, ваш ключ дедупликации не совпадает с арбитражным индексом: вернитесь к \d+ и сравните посимвольно.

Второе — мониторинг по SQLSTATE, а не по тексту сообщения. Все распространённые драйверы отдают код: в psycopg 3 это e.sqlstate, в psycopg2 — e.pgcode, в JDBC — getSQLState(), в Go — поле Code у pgconn.PgError. Ловите 21000 и 23505 раздельно и реагируйте по-разному: первая означает «почини вход», вторая — «конкурентная запись или не тот индекс». Проверьте в postgresql.conf, что log_min_error_statement не понижен до panic (по умолчанию стоит error), иначе в логе будет ошибка без текста запроса.

# postgresql.conf
log_min_error_statement = error
log_parameter_max_length_on_error = 256   # параметры запроса в логе при ошибке

log_parameter_max_length_on_error по умолчанию равен 0, то есть значения параметров в лог при ошибке не пишутся. Документация предупреждает, что ненулевое значение добавляет накладные расходы на каждый оператор, поэтому на нагруженной базе я включаю его на время разбора, а не навсегда. И помните, что в лог попадут персональные данные из заявок — телефоны и email, так что доступ к логам должен быть ограничен.

Третье — WHERE в DO UPDATE. Это дешёвая правка на несколько строк, которая при частых перевыгрузках резко сокращает число пустых обновлений, объём WAL и работу autovacuum. Одна оговорка из документации: строки, для которых условие ложно, всё равно блокируются. Делается один раз и работает всегда. У «Бренд-Мастерской» именно эта правка дала больше выигрыша по времени прогона, чем всё остальное вместе взятое.

Четвёртое — размер пакета. Здесь могу успокоить: гнаться за оптимальным числом строк смысла мало. На нормальном железе разница между пакетами по 500 и по 5000 строк измеряется процентами, а вот попытка залить сотни тысяч строк одним оператором означает долгие блокировки и долгий откат при любой ошибке. Я держусь диапазона от 500 до 10 000 строк и больше об этом не думаю.

На что можно не тратить время: на переход на MERGE ради самого перехода, на ручной LOCK TABLE вокруг UPSERT (ON CONFLICT в Read Committed справляется сам) и на попытки спасти пакет через EXCEPTION WHEN cardinality_violation в plpgsql. Поймать ошибку там можно, но к этому моменту весь оператор уже откатился, а построчная обработка вместо пакетной — медленный костыль, который прячет дубли, вместо того чтобы их убирать. И не переживайте из-за производительности DISTINCT ON: на пакете в несколько тысяч строк это сортировка на миллисекунды, на фоне самой записи её не видно.

• Сегодня: дедупликация входа по выражениям арбитражного индекса.
• Сегодня: проверить `log_min_error_statement` и на время разбора включить `log_parameter_max_length_on_error`.
• На этой неделе: `WHERE` в DO UPDATE и алерт по SQLSTATE 21000/23505.
• Можно отложить: миграцию на MERGE, подбор размера пакета, ручные блокировки.
• Никогда: повторы без изменения входа и замену DO UPDATE на DO NOTHING «чтобы не падало».

Частые вопросы