Каждую ночь Brew обновляет остатки напитков по кофейням: из учётной системы приходит выгрузка (кофейня, напиток, остаток). Часть пар уже есть в базе — их надо обновить; часть новые — их надо вставить. Наивный код для каждой строки сначала делает SELECT, и по результату решает: INSERT или UPDATE. Это три запроса там, где хватит одного, и — что хуже — между SELECT и INSERT другой процесс может вставить ту же пару, и твой INSERT упадёт на нарушении уникальности (или, если не упадёт, появится дубль).

Цель юнита — сделать это одной атомарной командой, безопасной под конкуренцией: INSERT ... ON CONFLICT (...) DO UPDATE. Это и есть upsert — «вставь, а если такой ключ уже есть, обнови».

ON CONFLICT нужен арбитр — UNIQUE или PK

ON CONFLICT (shop_code, drink_sku) означает «если нарушится уникальность по этим колонкам». Чтобы конфликт вообще был определён, на этих колонках должно стоять ограничение уникальности — PRIMARY KEY или UNIQUE. Без него Postgres не знает, по чему ловить конфликт, и команда не скомпилируется. В нашей таблице арбитр — составной первичный ключ (shop_code, drink_sku): одна строка на пару «кофейня × напиток».

DO UPDATE и псевдотаблица EXCLUDED

При конфликте выполняется DO UPDATE SET .... Здесь доступна особая псевдотаблица EXCLUDED — это та строка, которую мы пытались вставить (её «исключили» из вставки из-за конфликта). SET on_hand = EXCLUDED.on_hand означает «возьми новое значение остатка из входных данных». Так пишут типичный upsert-счётчик: новое значение перетирает старое. Можно и аккумулировать (SET on_hand = stock_levels.on_hand + EXCLUDED.on_hand — прибавить к текущему), ссылаясь на старое значение по имени таблицы и на новое — через EXCLUDED.

Альтернатива — DO NOTHING: при конфликте ничего не делать. Это идиома идемпотентной вставки: «вставь, если ещё нет, иначе молча пропусти». Ровно так схема Brew глушит дубли по outbox_id (таблица processed_outbox_ids) — повторная доставка события не ломает consumer.

Вставка или обновление? Приём с xmax

RETURNING отдаёт итоговую строку, но не говорит прямо, была это вставка или обновление. Известный приём — системная колонка xmax: у только что вставленной версии строки xmax = 0, у обновлённой — нет. Поэтому (xmax <> 0) AS was_update — компактный детектор. (Системные колонки xmin/xmax — это механика MVCC, разбираем её в 05-02; здесь достаточно знать, что трюк отличает вставку от обновления.)

Развилка ON CONFLICT: вставка или ветка конфликта

INSERT ... ON CONFLICT — одна атомарная команда с развилкой внутри. Арбитр (UNIQUE/PK) проверяет, есть ли уже строка с таким ключом, и выбирает ветку:

INSERT (shop_code, drink_sku, on_hand)
        │
        ▼
  ключ уже есть?  ◄── арбитр: PK (shop_code, drink_sku)
   ┌────┴───────────────────┐
   │ нет                    │ да → ON CONFLICT (...)
   ▼                        ▼
ВСТАВИТЬ строку      ┌───────┴────────────┐
was_update = false   ▼                    ▼
                 DO UPDATE             DO NOTHING
             SET on_hand =          строку не трогаем:
             EXCLUDED.on_hand       0 строк, дубль заглушен
             was_update = true

Что показывает наш код

Два upsert'а в query.sql — DO UPDATE и DO NOTHING:

-- name: UpsertStock :one
INSERT INTO stock_levels (shop_code, drink_sku, on_hand)
VALUES ($1, $2, $3)
ON CONFLICT (shop_code, drink_sku)
DO UPDATE SET on_hand = EXCLUDED.on_hand
RETURNING shop_code, drink_sku, on_hand, (xmax <> 0) AS was_update;
 
-- name: UpsertIgnore :execrows
INSERT INTO stock_levels (shop_code, drink_sku, on_hand)
VALUES ($1, $2, $3)
ON CONFLICT (shop_code, drink_sku) DO NOTHING;

В main.go мы вставляем пару CENTRAL/ESP-01, потом upsert'им её же с новым остатком (срабатывает DO UPDATE), вставляем новую пару, затем пробуем DO NOTHING на существующем ключе (0 строк, значение цело) и печатаем итог.

Запуск

Подними песочницу (из корня репозитория) и накати схему Brew + таблицу юнита:

docker compose up -d
make lecture L=03-crud-fluency/03-04-upsert-on-conflict T=db-reset
make lecture L=03-crud-fluency/03-04-upsert-on-conflict

(T=run — значение по умолчанию. Изнутри каталога юнита это make db-reset, make run.)

Вывод:

1) Первый upsert (CENTRAL/ESP-01, 50): новый ключ → вставка
   on_hand=50, was_update=false
 
2) Повторный upsert того же ключа (CENTRAL/ESP-01, 80): конфликт → обновление
   on_hand=80, was_update=true  (DO UPDATE SET on_hand = EXCLUDED.on_hand)
 
3) Upsert нового ключа (NORTH/LAT-01, 30): вставка
   on_hand=30, was_update=false
 
4) ON CONFLICT DO NOTHING для существующего ключа (CENTRAL/ESP-01, 999):
   строк затронуто: 0 (конфликт проигнорирован, on_hand остался 80)
 
5) Итоговое состояние stock_levels:
   CENTRAL/ESP-01  on_hand=80
   NORTH/LAT-01  on_hand=30

Первый upsert ключа CENTRAL/ESP-01 — вставка (was_update=false). Второй с тем же ключом — обновление (was_update=true), и on_hand стал 80: EXCLUDED.on_hand (новое значение) перетёр старое. DO NOTHING с остатком 999 для уже существующего ключа не сделал ничего — 0 строк, остаток остался 80. Никаких предварительных SELECT, никакой гонки.

Заборчик

ON CONFLICT решает именно проблему гонки: вставка и проверка конфликта — одна атомарная операция на уровне движка, поэтому два параллельных upsert'а одного ключа не создадут дубль и не упадут — один вставит, другой обновит. Это его главное преимущество перед связкой SELECT-потом-INSERT. Что важно помнить:

• В DO UPDATE SET перечисляй только то, что правда хочешь перетереть — легко случайно затереть created_at или счётчик значением из EXCLUDED.
• ON CONFLICT целится в один конкретный конфликт (один уникальный индекс). Если у таблицы несколько уникальных ограничений и строка может конфликтовать по разным, логика усложняется.
• MERGE (PG15+) гибче, но слабее под гонкой. Несколько WHEN MATCHED/NOT MATCHED-веток, но MERGE не так защищён от гонки, как ON CONFLICT, — разберём это и COPY для массовой загрузки в 09-01.
• Ночную синхронизацию в проде делают батчем, а не строка-за-строкой: COPY во временную таблицу → один INSERT ... SELECT ... ON CONFLICT.

Что забрать с собой

• INSERT ... ON CONFLICT (cols) DO UPDATE — «вставь или обнови» одной атомарной командой, безопасной под конкуренцией.
• Конфликт ловится по UNIQUE/PRIMARY KEY на указанных колонках — без такого ограничения ON CONFLICT не работает.
• EXCLUDED — псевдотаблица со строкой, которую пытались вставить; SET col = EXCLUDED.col берёт новое значение (можно и аккумулировать, ссылаясь на старое по имени таблицы).
• DO NOTHING — идемпотентная вставка: вставь, если нет, иначе молча пропусти (как дедуп по outbox_id в processed_outbox_ids).
• Приём (xmax <> 0) отличает обновление от вставки в RETURNING.

Дальше — юнит 03-05 «RETURNING old/new»: в PG18 UPDATE ... RETURNING old.status, new.status возвращает и старое, и новое значение строки одной командой — готовый аудит перехода без отдельного SELECT и без триггера.