Ответ на вопрос

История вопроса

Эта неопределенность возникла с введением нативной функциональности UPSERT в PostgreSQL 9.5 с помощью ON CONFLICT. До этого релиза разработчики реализовывали идемпотентные вставки с использованием сложных циклов PL/pgSQL или логики на стороне приложения, подверженной ошибкам. Clause RETURNING на протяжении долгого времени был важен для получения UUID или серийных ID, но его взаимодействие с двухпутевой моделью выполнения UPSERT, когда оператор может привести как к INSERT, так и к UPDATE, создало тонкий семантический разрыв, который путает даже опытных инженеров относительно того, какая версия строки фактически возвращается.

Проблема

Когда оператор INSERT ... ON CONFLICT ... DO UPDATE сталкивается с уникальным нарушением, он переключается на обновление существующей строки. Clause RETURNING затем ссылается на окончательное сохраненное состояние этой строки. Однако, если логика вашего приложения зависит от значений, которые были сгенерированы для предполагаемой вставки — таких как временные метки created_at, значения по умолчанию или значения, вычисленные приложением — оператор вместо этого возвращает устаревшие данные существующей строки. Эта безмолвная замена вызывает десинхронизацию кэша, порчу аудиторских следов и тонкие гонки, когда downstream-системы получают временно непоследовательные метаданные.

Решение

Псевдотаблица EXCLUDED предоставляет доступ к предлагаемым значениям вставки, которые вызвали конфликт. Явно ссылаясь на EXCLUDED.column_name в вашем Clause RETURNING или в списке установки UPDATE, вы гарантируете доступ к предполагаемым новым данным независимо от того, какой путь выполнения был выбран.

INSERT INTO user_sessions 
    (user_id, login_count, last_seen, session_token)
VALUES 
    (1001, 1, NOW(), gen_random_uuid())
ON CONFLICT (user_id) 
DO UPDATE SET 
    login_count = user_sessions.login_count + 1,
    last_seen = EXCLUDED.last_seen,
    session_token = EXCLUDED.session_token
RETURNING 
    session_id,
    user_id,
    CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation_type,
    session_token,
    EXCLUDED.last_seen AS intended_timestamp;

В этой модели EXCLUDED.last_seen и EXCLUDED.session_token гарантируют, что приложение получает свежие значения из предполагаемой вставки, даже когда база данных выполняет обновление вместо этого.

Ситуация из жизни

Параллельное накопление лояльности

Финансовая платформа, обрабатывающая высокочастотные микротранзакции, столкнулась с фантомными расчетами вознаграждений. Когда два параллельных запроса пытались одновременно зачислить очки на один и тот же пользовательский аккаунт, база данных PostgreSQL правильно поддерживала атомарность, но уровень кеша Redis получал устаревшие временные метки updated_at из Clause RETURNING. Это привело к тому, что кеш отклонил законные увеличения очков как устаревшие, что вызвало утечку доходов и жалобы клиентов на отсутствие вознаграждений.

Решение A: Распространенная блокировка с Redis

Инженерная команда изначально предложила захватывать распределенные блокировки в Redis перед выполнением транзакции базы данных. Этот подход последовательным образом сериализовал конфликтующие операции и гарантировал последовательную согласованность. Однако он создал единую точку сбоя, добавил 12-18 мс сетевой задержки на запрос и создал сложные сценарии взаимной блокировки, когда транзакции отменялись после захвата блокировок. Операционные накладные расходы, связанные с управлением блокировкой, и возможность каскадных сбоев сделали эту архитектуру неудовлетворительной в масштабе.

Решение B: Чтение-модификация-запись на стороне приложения

Еще одно предложение заключалось в том, чтобы сначала проверить существование записи с помощью SELECT, а затем решать между INSERT или UPDATE в коде приложения. Хотя концептуально это просто, этот паттерн терпит катастрофический провал под параллельной нагрузкой из-за того, что READ COMMITTED изоляция позволяет невоспроизводимым чтениям между проверкой и записью. Реализация изоляции SERIALIZABLE для предотвращения гонок создала бы избыточные сбои сериализации и шторма повторных попыток, в то время как явные блокировки таблиц ограничили бы пропускную способность до неприемлемых уровней.

Решение C: Правильное использование EXCLUDED

Выбранный подход изменил запрос, чтобы использовать EXCLUDED для всех изменяемых значений в Clause RETURNING. Ссылаясь на EXCLUDED.points и EXCLUDED.calculated_at, приложение постоянно получало необходимые метаданные из попытки вставки, независимо от того, привела ли операция к созданию новой строки или обновлению.

Выбор решения и результат

Команда реализовала Решение C в микросервисе вознаграждений. Это устранило проблемы несоответствия кеша без добавления сетевых переходов или компрометации уровней изоляции. Точность накопления очков увеличилась до 99,99%, использование процессора базы данных снизилось на 35% благодаря уменьшению количества круговых запросов, и система успешно справилась с пиковыми нагрузками в Черную пятницу без ручного вмешательства.

Что часто упускают кандидаты

Как PostgreSQL определяет, какой уникальный индекс использовать для обнаружения конфликтов, когда существует несколько индексов на таблице?

PostgreSQL требует явного указания арбитра в Clause ON CONFLICT. Когда вы пишете ON CONFLICT (column_list), планировщик выбирает уникальный индекс, столбцы которого точно соответствуют предоставленному списку по порядку. Если существует несколько индексов на идентичных столбцах, выбирается тот, который был создан первым. Для частичных уникальных индексов (тех, что с WHERE условиями) или индексов выражений, вам нужно использовать синтаксис ON CONFLICT ON CONSTRAINT constraint_name; в противном случае, движок выдаст ошибку, указывая, что не может вывести арбитражный индекс. Кандидаты часто предполагают, что база данных автоматически выбирает "самый селективный" индекс или упускают из виду, что функциональные индексы требуют явного указания ограничений.

Почему Может UPSERT тихо терять обновления, когда несколько транзакций конфликтуют по тому же ключу при изоляции READ COMMITTED?

Это случается из-за поведения переоценки UPDATE. Когда Транзакция A вставляет строку и фиксирует, Транзакция B, ожидая блокировки строки, повторно выполняет свой предикат UPDATE против вновь видимой строки. Если логика UPDATE использует абсолютное присвоение (например, SET balance = 100), а не относительную арифметику с использованием EXCLUDED (например, SET balance = account.balance + EXCLUDED.amount), Транзакция B полностью перезаписывает изменения Транзакции A. Многие кандидаты ошибочно полагают, что UPSERT подразумевает автоматическое объединение или накопление, не осознавая, что Clause DO UPDATE требует явной обработки значений EXCLUDED, чтобы достичь идемпотентной семантики накопления.

В чем точная разница между проверкой xmax = 0 и xmax IS NULL, чтобы определить, было ли вставлено, и почему это различие важно для HOT обновлений?

В PostgreSQL xmax хранит идентификатор транзакции удаляющей или обновляющей транзакции. Для вновь вставленных строк xmax инициализируется в 0, никогда не NULL. Кандидаты часто ошибочно проверяют xmax IS NULL, чтобы обнаружить вставки, что всегда возвращает ложь. Проверка xmax = 0 надежно выявляет вставки по сравнению с обновлениями. Это различие становится критическим при HOT (Heap Only Tuple) обновлениях, когда PostgreSQL оптимизирует производительность, обновляя строки на месте на той же странице без изменения индексов. Хотя xmax корректно указывает на то, что строка была затронута, понимание того, что 0 означает "без предыдущего модификатора", в то время как ненулевое значение указывает на версионность, предотвращает логические ошибки при вычислении номеров генерации строк или реализации логики захвата изменения данных, которая должна различать рождения и мутации.