Как предотвратить повторный запуск пункта ETL в PostgreSQL с помощью блокировки таблицы

При параллельном выполнении ETL-процедур в PostgreSQL часто возникает ситуация, когда один и тот же пункт (например, 23-й) запускается дважды. Это происходит, если две копии процедуры exec_batch_from_id стартуют одновременно и не видят изменений друг друга. Решение - использовать эксклюзивную блокировку (ACCESS EXCLUSIVE) на ключевую таблицу, чтобы организовать последовательное выполнение.

Почему возникает повторный запуск ETL

Процедура exec_batch_from_id запускает указанный пункт ETL, а затем рекурсивно вызывает саму себя для следующих пунктов. Если внешняя процедура запускает её дважды параллельно, каждый экземпляр может попытаться выполнить один и тот же следующий пункт (например, 23-й). Внутри процедуры уже стоит команда LOCK TABLE df.batch_log IN ACCESS EXCLUSIVE MODE, но этого может быть недостаточно, если транзакции не синхронизированы должным образом.

Как работает блокировка таблицы

Команда LOCK TABLE ... IN ACCESS EXCLUSIVE MODE блокирует таблицу так, что другие транзакции не могут читать или писать в неё до завершения текущей. Однако в PostgreSQL блокировки удерживаются до конца транзакции. Если процедура содержит обработку исключений (EXCEPTION blocks), транзакция может не завершиться корректно, и блокировка не снимет конфликт. Второй экземпляр процедуры, дождавшись освобождения блокировки, увидит уже зафиксированные изменения первого экземпляра - но только если первый экземпляр выполнил COMMIT до того, как второй начал работу.

Почему блокировка не всегда спасает

Если обе копии процедуры стартовали в рамках одной транзакции (например, вызваны из одной и той же внешней процедуры без COMMIT между вызовами), то блокировка не поможет: второй экземпляр будет ждать завершения первого, но после COMMIT первого второй всё равно может попытаться запустить тот же пункт, если логика процедуры не проверяет статус пункта после получения блокировки.

Организация очереди для параллельных инстансов

Чтобы гарантировать, что каждый пункт ETL выполняется ровно один раз, нужно:

• Проверять статус пункта сразу после получения блокировки: если пункт уже выполнен (например, поле status = 'done'), процедура должна пропустить его.
• Использовать таблицу с атомарным обновлением статуса (например, UPDATE df.batch_log SET status = 'running' WHERE id = ... AND status = 'pending') внутри блокировки.
• Убедиться, что COMMIT происходит до вызова следующей рекурсии, чтобы изменения были видны другим экземплярам.

Пример корректной логики с блокировкой

BEGIN;
LOCK TABLE df.batch_log IN ACCESS EXCLUSIVE MODE;
-- Проверяем, не выполнен ли уже пункт
SELECT status INTO v_status FROM df.batch_log WHERE id = p_batch_id;
IF v_status = 'pending' THEN
  -- Выполняем ETL
  UPDATE df.batch_log SET status = 'running' WHERE id = p_batch_id;
  -- ... выполнение пункта ...
  UPDATE df.batch_log SET status = 'done' WHERE id = p_batch_id;
  COMMIT;
  -- Рекурсивный вызов для следующего пункта
  PERFORM exec_batch_from_id(p_next_id);
ELSE
  COMMIT;
END IF;

Диагностика пересечения работы инстансов

Если в таблице логов видно, что два инстанса одновременно обрабатывают один и тот же пункт (например, 23-й) с пересекающимися временными метками start/end, это подтверждает, что блокировка не применяется должным образом или проверка статуса отсутствует. Добавьте в процедуру логирование момента получения блокировки и статуса пункта - это поможет выявить, на каком этапе происходит сбой.