pgtools
Скачать
Материалы / Истории / Тайна пропавшей транзакции
История

Тайна пропавшей транзакции

История о том, почему периодические джобы теряют строки на границах интервала и чем now() отличается от clock_timestamp() в PostgreSQL.

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

— Может, ваши джобы подкуплены мошенниками? — ухмыльнулся Макс, пережёвывая салат с сомнительного вида курицей.

Ваня нахмурился:

— Ну серьёзно же. Каждые 10 минут запускаем выборку: выбираем всё, где время обнаружения больше, чем now() - interval '10 minutes', и рассылаем на почту. Должно работать как часы, а иногда некоторые строки в выборку не попадают!

SELECT *
FROM fraud_alerts
WHERE founded > now() - interval '10 minutes';

Макс медленно кивнул:

— А вы считаете, что джобы у вас запускаются идеально ровно каждые 10 минут?

— Ну… думаю, не совсем идеально.

— Вот именно. Не совсем, — Макс чуть прищурился. — Поверь, ваш джоб далеко не единственный процесс в системе. Планировщик может запустить его на несколько секунд позже. А в сбойных ситуациях процесс вообще могут прибить на середине выполнения.

— Неужели всё так плохо? У нас же база на очень мощном сервере.

— А вы верите, что базы и серверы живут в идеальном мире? Так вот, они живут в аду асинхронности, блокировок и подвисаний. Думаю, что ваша выборка теряет данные на границе интервала, — подытожил Макс.

— Что же делать? — Ваня выглядел обескураженно.

— Классика: заведи служебную таблицу, например job_last_run, и храни в ней последнее значение founded, до которого рассылка уже дошла. Следующий запуск должен искать не «последние 10 минут», а всё, что появилось после последней успешно обработанной границы.

-- условно, внутри функции или процедуры


-- 1. читаем прошлую границу
SELECT last_founded
INTO v_last_founded
FROM job_last_run
WHERE job_name = 'fraud_monitor'
FOR UPDATE;


-- 2. заранее фиксируем новую верхнюю границу
SELECT max(founded)
INTO v_max_founded
FROM fraud_alerts
WHERE founded > v_last_founded;
-- тут конечно проверяем, что v_max_founded не NULL и все такое


-- 3. выбираем данные в стабильном интервале
SELECT *
FROM fraud_alerts
WHERE founded > v_last_founded
  AND founded <= v_max_founded
ORDER BY founded, id;


-- 4. после успешной рассылки обновляем границу
UPDATE job_last_run
SET last_founded = v_max_founded
WHERE job_name = 'fraud_monitor';

— Так вы не будете зависеть от того, запустился джоб ровно в 15:00:00 или в 15:00:08, — заключил Макс.

— А зачем второй пункт? Почему мы заранее делаем SELECT max(founded)?

— Потому что между выборкой и обновлением служебной таблицы в fraud_alerts могут попасть новые записи. Если ты просто обновишь last_founded текущим временем, часть записей рискует оказаться между двумя мирами: в прошлую рассылку они ещё не попали, а для следующей уже будут считаться старыми. А вы, кажется, не хотите пропускать транзакции? — улыбнулся Макс.

— Выглядит просто и надёжно. И почему мы раньше до этого не додумались? — Ваня выглядел одновременно радостным и раздражённым.

Макс философски посмотрел на свою подозрительную курицу и суп Вани:

— По той же причине, почему я каждый раз надеюсь, что в столовой будет вкусный обед: излишний оптимизм часто побеждает здравый смысл.

На следующий день

Макс сидел у себя в кабинете и листал результаты аудита по таблице fraud_alerts.

Перед этим ему позвонил Ваня:

— Макс, всё плохо. Твоё гениальное решение не помогло. У меня есть конкретные примеры: записи не попадают в рассылку, хотя точно должны!

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

А Ване он поручил:

— Найди свежий пример. Как только появится — сразу скидывай.

Через два часа пришло сообщение от Вани с новой «непойманной» транзакцией:

founded = '14:59:45'


Джоб запускался в 15:00:00.
Запись должна была попасть в рассылку.
Но не попала.

Макс открыл аудит этой записи. На первый взгляд всё было нормально. Потом он добрался до временных меток и поднял бровь:

action_tstamp_tx   = 14:59:45  -- время старта транзакции
action_tstamp_stm  = 14:59:45  -- время старта SQL-оператора
action_tstamp_clk  = 15:00:07  -- реальные часы в момент аудита строки

А вот и ключик нашелся. Он позвонил Ване.

— Вань, ты же уже знаешь, что время в базе — это не часы на вокзале. Когда в системе что-то всерьёз зависит от точного времени, нужно обкладываться логами и смотреть, кто именно это время поставил.

— Но ведь founded = '14:59:45', а джоб запускался уже в 15:00! — застонал Ваня. — Что может быть не так с этим временем?

Макс откинулся на спинку кресла:

— Вы когда вставляете записи в fraud_alerts, откуда берёте founded?

— Ну… просто now() в insert select.

— Вот тут и кроется причина.

Макс открыл пример запроса:

INSERT INTO fraud_alerts (
    operation_id,
    client_id,
    amount,
    founded
)
SELECT
    operation_id,
    client_id,
    amount,
    now()
FROM suspicious_operations
WHERE check_result = 'fraud';

— В PostgreSQL now() — это не «текущее живое время на момент вызова». Это время старта текущей транзакции. То же самое, что transaction_timestamp().

— То есть?

— То есть если транзакция началась в 14:59:45, то now() внутри неё будет возвращать 14:59:45. Даже если сам insert select тяжёлый, выполняется 20 секунд и фактически заканчивается уже после 15:00.

Ваня помолчал.

Макс продолжил:

— В твоём примере запись получила founded = 14:59:45, потому что так сказал now(). Но для других транзакций эта запись стала видимой только после завершения вставки и коммита — уже около 15:00:07. Когда джоб рассылки стартовал в 15:00:00, этой записи для него ещё не существовало.

— А следующий джоб её уже не взял…

— Потому что по значению founded она выглядела старой. Именно.

Ваня выдохнул:

— И что теперь делать? Сдвинуть время запуска джоба, чтобы он точно стартовал после завершения поиска?

— Это плохой путь. Сегодня поможет, завтра снова разъедется. У тебя проблема не в расписании, а в том, что для поля founded используется не то время.

Макс сделал глоток холодного кофе и продиктовал минимальное исправление:

— В insert select не заполняйте founded через now(). Сначала вставляйте записи без этого значения или с NULL, а в конце обработки проставляйте время отдельным шагом. В PostgreSQL для этого нужен clock_timestamp(), потому что он возвращает реальные часы на момент вызова.

-- 1. вставляем найденные записи без founded
INSERT INTO fraud_alerts (
    operation_id,
    client_id,
    amount,
    founded,
    batch_id
)
SELECT
    operation_id,
    client_id,
    amount,
    NULL,
    v_batch_id
FROM suspicious_operations
WHERE check_result = 'fraud';


-- 2. в конце обработки проставляем фактическое время обнаружения
WITH fixed_time AS (
    SELECT clock_timestamp() AS ts
)
UPDATE fraud_alerts fa
SET founded = fixed_time.ts
FROM fixed_time
WHERE fa.batch_id = v_batch_id
  AND fa.founded IS NULL;


COMMIT;

— Так у записей будет время, близкое к фактическому завершению их формирования, а не время старта длинной транзакции, — сказал Макс.

Ваня задумался:

— Просто. Даже слишком.

Макс усмехнулся:

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

Шпаргалка по времени в PostgreSQL

В PostgreSQL есть несколько функций времени, и они отвечают на разные вопросы.

now()
current_timestamp
transaction_timestamp()

Возвращают время старта текущей транзакции. Значение фиксируется один раз на транзакцию.

statement_timestamp()

Возвращает время старта текущего SQL-оператора. Внутри одного SQL-запроса значение будет стабильным.

clock_timestamp()

Возвращает реальные системные часы на момент вызова. Значение может меняться даже внутри одного SQL-запроса.

timeofday()

Тоже возвращает текущее время, но в виде текстовой строки. Для прикладной логики обычно лучше использовать clock_timestamp().

Мораль

Если джоб запускается каждые 10 минут, это не значит, что данные нужно искать за последние 10 минут.

Для периодической обработки лучше хранить состояние: последнюю успешно обработанную границу, статус обработки или отдельную очередь событий.

А если в PostgreSQL вам нужно именно «текущее живое время», не используйте now() по привычке. Внутри транзакции оно может оказаться совсем не таким текущим, как кажется.

Публикация в Telegram