ON CONFLICT в PostgreSQL: UPSERT без гонки между SELECT и INSERT

Проверка «строки ещё нет» отдельным SELECT, а затем INSERT не защищает от дубля. Между командами другой сеанс может вставить тот же ключ. Первый SELECT останется правдивым для своего снимка данных, но вывод «теперь можно вставлять» уже устареет.

В PostgreSQL ожидаемый конфликт разрешают в самом INSERT: ON CONFLICT DO NOTHING пропускает строку, а ON CONFLICT DO UPDATE обновляет найденную по уникальному ключу запись. Решение остаётся за базой, рядом с ограничением, которое действительно обеспечивает уникальность.

SQL / 01koddo

Один уникальный ключ, два конкурирующих INSERT

Воспроизводимая таблица с составным ключом

Примеры можно выполнить в одной сессии PostgreSQL 18. Временная таблица исчезнет при закрытии соединения и не затронет постоянные данные:

CREATE TEMP TABLE api_resources (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  external_id text NOT NULL,
  payload jsonb NOT NULL,
  version integer NOT NULL DEFAULT 1,
  CONSTRAINT api_resources_tenant_external_key
    UNIQUE (tenant_id, external_id)
);

INSERT INTO api_resources (tenant_id, external_id, payload)
VALUES (7, 'cmd-42', '{"status":"queued"}')
RETURNING id, tenant_id, external_id, payload, version;

Здесь уникален не каждый столбец отдельно, а пара (tenant_id, external_id). Значение cmd-42 можно использовать в другом tenant, но внутри tenant_id = 7 второй такой ключ конфликтует. Подробнее о диагностике самого нарушения и SQLSTATE 23505 рассказано в статье про duplicate key value violates unique constraint.

Почему SELECT перед INSERT оставляет гонку

Типичная ошибочная последовательность состоит из двух команд:

SELECT id
FROM api_resources
WHERE tenant_id = 7 AND external_id = 'cmd-99';

-- Если строк нет:
INSERT INTO api_resources (tenant_id, external_id, payload)
VALUES (7, 'cmd-99', '{"status":"queued"}');

На уровне изоляции Read Committed, который используется по умолчанию, каждый обычный SELECT видит данные, зафиксированные к началу этой команды. Чем он отличается от Repeatable Read и Serializable и какие аномалии каждый пропускает — в разборе уровней изоляции. Поэтому два сеанса могут одновременно получить ноль строк. SELECT ... FOR UPDATE не исправит схему: блокировать нечего, пока строка отсутствует.

Затем оба сеанса выполнят INSERT с одной парой значений. Уникальное ограничение сохранит целостность: один запрос вставит строку, второй может дождаться завершения конкурента и затем получить 23505, если тот зафиксирует транзакцию. Проблема не в ограничении — оно как раз остановило дубль. Ошибка в предположении, что результат отдельного SELECT резервирует ключ для будущей команды.

ON CONFLICT DO NOTHING: пропустить ожидаемый повтор

Если повторная доставка означает «ресурс уже зарегистрирован», конфликт можно пропустить:

INSERT INTO api_resources (tenant_id, external_id, payload)
VALUES (7, 'cmd-42', '{"status":"queued"}')
ON CONFLICT (tenant_id, external_id) DO NOTHING
RETURNING id, version;

Результат содержит ноль строк: ключ уже занят, вставка не выполнена, а DO NOTHING ничего не обновляет. Исходная строка остаётся с id = 1 и version = 1.

Для DO NOTHING цель конфликта синтаксически необязательна. Без неё PostgreSQL подавит конфликт с любым подходящим UNIQUE, PRIMARY KEY, исключающим ограничением или уникальным индексом таблицы. В прикладном запросе явная цель обычно безопаснее: она документирует ожидаемый повтор, а конфликт только с другим уникальным правилом остаётся ошибкой.

DO NOTHING не возвращает существующую строку и не означает «найти или создать с гарантированным id в ответе». Если приложению нужен id, оно должно обработать пустой результат и отдельно прочитать запись в соответствии со своим уровнем изоляции и политикой повторов.

ON CONFLICT DO UPDATE и псевдотаблица excluded

Когда повтор должен изменить разрешённые поля, используйте DO UPDATE:

INSERT INTO api_resources AS resource (
  tenant_id,
  external_id,
  payload
)
VALUES (7, 'cmd-42', '{"status":"done"}')
ON CONFLICT (tenant_id, external_id) DO UPDATE
SET payload = excluded.payload,
    version = resource.version + 1
RETURNING id, tenant_id, external_id, payload, version;

excluded — специальное имя строки, которую запрос предлагал вставить. В примере excluded.payload равно {"status":"done"}. Текущая строка таблицы доступна через псевдоним resource, поэтому счётчик увеличивается от сохранённого resource.version.

Не копируйте из excluded все столбцы автоматически. Ключ tenant, владелец, дата создания или другой неизменяемый атрибут не должны перезаписываться только потому, что клиент повторил запрос. В SET перечисляют лишь поля, которые бизнес-операция действительно разрешает обновить.

Conflict target: столбцы или ON CONSTRAINT

Запись по столбцам просит PostgreSQL вывести подходящий уникальный индекс:

ON CONFLICT (tenant_id, external_id) DO UPDATE

Порядок столбцов при таком выводе несущественен, но их набор должен соответствовать подходящему уникальному индексу. В примере арбитром становится индекс, созданный для UNIQUE (tenant_id, external_id). Обычный неуникальный индекс для ON CONFLICT недостаточен.

Именованное ограничение можно выбрать напрямую:

INSERT INTO api_resources (tenant_id, external_id, payload)
VALUES (7, 'cmd-42', '{"status":"reviewed"}')
ON CONFLICT ON CONSTRAINT api_resources_tenant_external_key DO UPDATE
SET payload = excluded.payload
RETURNING id, payload, version;

ON CONSTRAINT удобно, когда контракт приложения намеренно привязан к конкретному имени ограничения. Вывод по столбцам устойчивее к замене уникального индекса на эквивалентный и потому чаще подходит для обычного UPSERT. В обоих вариантах арбитр должен соответствовать тому бизнес-ключу, конфликт по которому считается ожидаемым.

Составной ключ нельзя сокращать до (external_id): такого уникального арбитра в таблице нет, и PostgreSQL отклонит запрос. Устройство составных UNIQUE и создаваемых для них индексов разобрано в материале про ограничения и ошибку 23505 и справочнике по индексам PostgreSQL.

Условный DO UPDATE … WHERE

Условие после DO UPDATE позволяет не переписывать строку, если новые данные совпадают со старыми:

INSERT INTO api_resources AS resource (
  tenant_id,
  external_id,
  payload
)
VALUES (7, 'cmd-42', '{"status":"archived"}')
ON CONFLICT (tenant_id, external_id) DO UPDATE
SET payload = excluded.payload,
    version = resource.version + 1
WHERE resource.payload IS DISTINCT FROM excluded.payload
RETURNING id, payload, version;

Первый такой запрос обновит payload и вернёт строку с version = 3. Повторите его без изменений — условие станет ложным, обновления не будет, а RETURNING выдаст ноль строк. IS DISTINCT FROM здесь сравнивает значения с определённой семантикой для NULL; в этой таблице payload объявлен NOT NULL, но выражение остаётся ясным и при последующем изменении схемы.

PostgreSQL сначала находит конфликт и блокирует строку, а затем проверяет WHERE. Ложное условие отменяет обновление, но не получение блокировки. Поэтому этот приём сокращает ненужные записи новых версий строк, а не превращает конфликтующий запрос в неблокирующий.

Что именно возвращает RETURNING

RETURNING вычисляется только для строк, которые оператор действительно вставил или обновил:

Пустой результат не доказывает, что ключа нет. Напротив, при DO NOTHING он часто означает, что уникальный ключ уже занят; при условном обновлении — что существующая строка не прошла WHERE. Приложение должно различать эти договорённости по форме своего запроса, а не считать RETURNING безусловным способом получить существующую запись.

Конкурентная семантика без лишних обещаний

Без фильтрующего WHERE оператор ON CONFLICT DO UPDATE атомарно выбирает для каждой предложенной строки один из двух исходов: вставку или обновление. Условие WHERE добавляет третий вариант — конфликтующая строка заблокирована, но не обновлена. При высокой конкуренции запрос может ждать транзакцию, которая меняет тот же уникальный ключ. На Read Committed обновление может затронуть версию строки, которой не было видно в обычном снимке команды. DO NOTHING также может пропустить вставку из-за результата конкурентной транзакции, ещё не видимого снимку INSERT.

Гарантия относится к одному оператору и выбранному конфликту. Она не делает атомарной цепочку из UPSERT, списания денег и отправки сообщения; такую бизнес-операцию всё равно оформляют транзакцией. Она также не отменяет независимые ошибки: другие ограничения могут быть нарушены, взаимная блокировка возможна, а на более строгих уровнях изоляции приложение должно уметь повторять всю транзакцию после ошибки сериализации.

После любой SQL-ошибки явная транзакция требует отката; порядок восстановления описан в разборе current transaction is aborted. Не ловите 23505 и не продолжайте команды в той же транзакции вместо ON CONFLICT или ROLLBACK.

Практический выбор

1. Опишите бизнес-ключ ограничением UNIQUE или уникальным индексом. Для любого ON CONFLICT арбитр должен быть NOT DEFERRABLE; исключающие ограничения поддерживаются только для DO NOTHING.
2. Уберите отдельный SELECT, если он нужен только для ответа на вопрос «можно ли вставлять».
3. Используйте явный conflict target: полный набор столбцов либо ON CONSTRAINT с проверенным именем.
4. Выберите DO NOTHING, если существующую строку нельзя менять; учитывайте ноль строк из RETURNING.
5. Для DO UPDATE перечислите разрешённые поля и берите входные значения из excluded.
6. Добавляйте WHERE, только если пропуск одинакового обновления нужен по смыслу; строка всё равно может блокироваться, а RETURNING — остаться пустым.

Атомарный UPSERT начинается не с ключевых слов ON CONFLICT, а с корректного уникального правила. Оператор лишь задаёт, что PostgreSQL должен сделать, когда именно это правило обнаружит конкурентный или повторный ключ.