Куда пропало обращение - развязка

В прошлом посте у нас загадочно пропало обращение 58122. В БД его не было, хотя 58121 и 58123 были, а в логах нашли запись о создании:

12:41:03 Creating ticket, id=58122

и никаких следов дальнейших действий с этим id.

В комментариях к первому посту многие правильно определили, в чем была проблема 🔥

Дальше в логах этого запроса была ошибка базы:

org.postgresql.util.PSQLException: ERROR: null value in column "department_id" of relation "ticket_status_history" violates not-null constraint

Операция по добавлению нового обращения была частью транзакции:
➡️ сначала добавлялась строка в таблицу с обращениями ticket с новым id,
➡️ затем в связанную таблицу ticket_status_history тоже добавлялась первая запись для этого обращения.

Из-за бага в одном из сценариев обязательное поле в таблице ticket_status_history не заполнилось. В БД ушел запрос с null в обязательном поле. На это БД логично ответила ошибкой нарушения non-null constraint и откатила всю транзакцию:

BEGIN

INSERT INTO ticket ...
-- sequence выдала 58122

INSERT INTO ticket_status_history ...
-- ERROR

ROLLBACK

Таким образом откатилось и создание обращения с id = 58122, поэтому такой записи в БД нет.

А вот значение sequence, с помощью которой генерируются id-шники при BIGSERIAL, обратно не откатилось. Поэтому следующее успешно созданное обращение получило id = 58123.

То есть sequence выдает значение для id, INSERT выполняется, но затем вся транзакция откатывается. Строка исчезает, а номер sequence уже потрачен. Для новой вставки sequence отдаст уже следующий номер.

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

В доке оказывается так и написано, что значения из sequence не используются повторно, чтобы избежать блокировки параллельных транзакций, получающих числа из одной и той же sequence. Причем пропуски могут появляться не только из-за rollback: например, при INSERT ... ON CONFLICT значение из sequence может быть получено еще до обнаружения конфликта. Поэтому PostgreSQL sequence не подходят как источник непрерывных последовательностей.

Ссылка на доку PostgreSQL на английском

Ссылка на доку на русском от PostgresPro

Баг, из-за которого возникал NOT NULL constraint violation, исправили. С бизнесом обсудили, стоит ли формировать номера обращений по отдельным правилам вместо использования для этого id. В тот момент сошлись, что не стоит.

А были ли у вас истории с "пропавшими" строками в БД?