PostgreSQL INSERT ON CONFLICT: Правильный Upsert с Уникальными Целями, DO NOTHING vs DO UPDATE
Используйте ON CONFLICT для атомарной вставки или обновления строк на основе уникальных ограничений. Укажите точную цель конфликта, обрабатывайте повторяющиеся входные данные и понимайте, что исключенные значения заменяют, а не накапливают.
В этом материале
Короткий ответ
Оператор INSERT ... ON CONFLICT в PostgreSQL предоставляет механизм атомарного upsert. Он пытается вставить строки; если строка нарушает указанное уникальное ограничение или индекс (цель конфликта), он либо ничего не делает (DO NOTHING), либо обновляет существующую строку (DO UPDATE). Цель конфликта должна правильно идентифицировать правило уникальности. Псевдо-таблица EXCLUDED предоставляет доступ к значениям предложенной вставки для обновлений. Хотя оператор атомарен для каждой строки, он не гарантирует бизнес-семантику, такую как аддитивные обновления или обработка «точно один раз» в параллельных транзакциях.
Определите фактическое правило уникальности
Оператор ON CONFLICT разрешает нарушения арбитражных ограничений или индексов. Это уникальные ограничения (PRIMARY KEY, UNIQUE) или уникальные индексы. Документация указывает на то, что для ON CONFLICT DO UPDATE необходимо предоставить целевой объект конфликта. Этот целевой объект указывает, какие конфликты вызывают альтернативное действие, выбирая арбитражные индексы. Правило обеспечивается на уровне базы данных, а не логикой приложения. Например, таблица inventory(sku text PRIMARY KEY, qty integer NOT NULL) имеет ограничение первичного ключа на sku. Это ограничение является арбитром; любая вставка дублирующего значения sku является конфликтом.
Выберите целевой объект конфликта
Целевой объект конфликта может использовать вывод уникального индекса, указание столбцов/выражений или непосредственно указать ограничение с помощью ON CONFLICT ON CONSTRAINT. Вывод часто предпочтительнее. Для таблицы inventory первичный ключ на sku выводится с помощью ON CONFLICT (sku). Документация отмечает, что вывод выбирает все уникальные индексы, содержащие точно указанные столбцы/выражения. Если вы указываете ограничение напрямую, оно использует индекс, связанный с этим ограничением. Для частичных уникальных индексов вам необходимо включить предложение WHERE в целевой объект конфликта, чтобы соответствовать предикату индекса.
Используйте DO NOTHING намеренно
ON CONFLICT DO NOTHING молча игнорирует строки, которые вызвали бы конфликт с любым арбитражным ограничением или индексом. Целевой объект конфликта является необязательным; если он опущен, конфликты со всеми используемыми уникальными ограничениями обрабатываются. Используйте это, когда вы хотите вставлять только новые строки и игнорировать дубликаты. Например, INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT DO NOTHING; ничего не делает, если SKU 'A' уже существует. Оператор выполняется успешно, и счетчик, возвращаемый указывает на количество фактически вставленных строк (ноль в этом случае).
Обновление из исключенных значений
ON CONFLICT DO UPDATE изменяет существующую конфликтующую строку. В предложении SET специальный псевдоним EXCLUDED предоставляет значения, первоначально предложенные для вставки. Документация указывает, что при ссылке на столбец не следует включать имя таблицы. Для примера inventory INSERT INTO inventory (sku, qty) VALUES ('A', 3) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty; заменяет существующее количество на 3. Он не добавляет значения. Чтобы выполнить аддитивное обновление, вам необходимо явно ссылаться на существующую строку: SET qty = inventory.qty + EXCLUDED.qty.
Проследите за двухстрочным примером
Рассмотрим вставку двух строк, одна из которых конфликтует, а другая нет. С inventory изначально содержащим ('A', 2), выполните: INSERT INTO inventory (sku, qty) VALUES ('A', 3), ('B', 5) ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;. Строка для SKU 'A' конфликтует, вызывая обновление, которое устанавливает его qty в 3. Строка для SKU 'B' не конфликтует и вставляется. Команда возвращает INSERT 0 2, указывая на то, что было обработано две строки (одна обновлена, одна вставлена). Конечный статус: ('A', 3), ('B', 5).
Обрабатывайте дублирующиеся строки в одном операторе
PostgreSQL документирует ON CONFLICT DO UPDATE как детерминированный: одна команда не может влиять на одну и ту же существующую строку более одного раза. С повторяющимися предлагаемыми ключами, такими как VALUES ('A', 3), ('A', 4), может произойти нарушение кардинальности. Это не описывается как обычная ошибка дублирования ключей, происходящая до того, как ON CONFLICT получит шанс действовать. Не полагайтесь на то, что первое или последнее предложенное значение выиграет.
Deduplicate входные данные или агрегируйте повторяющиеся ключи в соответствии с явным бизнес-правилом перед отправкой оператора. Для заменяющих количеств решите, какое наблюдение должно выиграть; для аддитивных количеств решите, уместно ли суммирование. В случае ошибки оператора его изменения не становятся частично успешным upsert. В явной транзакции восстановите с использованием политики отката или точки сохранения приложения.
Понимание параллельного поведения
ON CONFLICT DO UPDATE гарантирует атомарный результат INSERT или UPDATE для каждой строки, даже в условиях высокой параллельности. Однако документация предупреждает, что во время выполнения CREATE INDEX CONCURRENTLY или REINDEX CONCURRENTLY на уникальном индексе INSERT ... ON CONFLICT на той же таблице может неожиданно завершиться сбоем с нарушением уникального ограничения. Оператор берет блокировку на конфликтующей строке. Если две параллельные транзакции пытаются вставить один и тот же ключ, одна успешно вставится, а другая столкнется и выполнит действие DO UPDATE на существующей строке.
Проверьте ограничения за пределами оператора
Атомарный upsert не гарантирует внешнюю обработку «точно один раз». Повторная попытка аддитивного обновления может снова увеличить количество, если приложение не дедуплицирует логическую операцию. Другие ограничения, разрешения и триггеры все еще могут отклонить оператор. По умолчанию, nullable уникальные столбцы рассматривают nulls как отличные, позволяя несколько nulls, если не указано NULLS NOT DISTINCT. Частичные уникальные индексы применяются только к их предикату; вывод целевого объекта конфликта должен выбирать соответствующий арбитр.
Что проверить
- Для ON CONFLICT DO UPDATE необходимо указать целевой объект конфликта.
- Псевдоним EXCLUDED предоставляет предложенные значения вставки для использования в предложении SET DO UPDATE.
- Дедуплицируйте предлагаемые строки по ключу арбитра перед DO UPDATE: одна команда не должна влиять на одну и ту же существующую строку более одного раза; повторяющиеся ключи могут вызвать нарушение кардинальности.
- ON CONFLICT DO UPDATE атомарен для каждой строки, но не автоматически добавляет количества; вам необходимо написать выражение, например qty = inventory.qty + EXCLUDED.qty.
- Для частичного уникального арбитражного индекса включите соответствующий предикат индекса в целевой объект конфликта, чтобы PostgreSQL мог вывести предполагаемый индекс.
- Команда tag INSERT 0 N указывает, что N строк были вставлены или обновлены; oid всегда равен 0.
- Для ON CONFLICT DO NOTHING без целевого объекта конфликта конфликты с любым уникальным ограничением игнорируются.
- Указание ограничения напрямую с помощью ON CONFLICT ON CONSTRAINT использует индекс, связанный с этим ограничением.
- Атомарное поведение insert-or-update не гарантирует успех: несвязанные ограничения, разрешения, триггеры или одновременное обслуживание уникального индекса все равно могут вызвать ошибки.
- Уникальные ограничения рассматривают NULLs как отличные по умолчанию, позволяя нескольким NULL строкам, если не указано NULLS NOT DISTINCT.
Границы применения
Это руководство описывает PostgreSQL INSERT ... ON CONFLICT, а не универсальный синтаксис для каждой базы данных. Атомарность на уровне строки не обеспечивает внешние эффекты «точно один раз» или не проверяет бизнес-количества. Дедуплицируйте предлагаемые ключи в соответствии с определенным бизнес-правилом перед DO UPDATE. Одновременное обслуживание уникального индекса и другие проверки базы данных все равно могут вызвать ошибки. Небольшой пример inventory является иллюстративным, а не выполненным тестом.