Архитектура обработки данных: использование CTE для выборки, обновления и вставки

Рассмотрим задачу обработки данных из двух исходных таблиц с последующей записью результатов и синхронизацией статусов.

Исходные условия

Имеются две таблицы исходных данных. Каждая строка в них содержит признак обработки IsProcessed, который изначально имеет значение false. Алгоритм работы должен включать следующие шаги:

• Выборка необработанных строк из обеих таблиц.
• Обработка данных (соединения, преобразования, расчёты).
• Запись результатов обработки в целевую таблицу.
• Обновление признака IsProcessed на true в исходных таблицах для обработанных строк.
• Обеспечение атомарности операции: все действия должны выполняться в рамках одной транзакции с возможностью отката при ошибках.
• Возврат рассчитанных данных клиенту.

Предлагаемое решение с CTE

Один из подходов - использование обобщённых табличных выражений (CTE, Common Table Expressions) для объединения выборки, обновления и вставки в единый запрос. Согласно документации СУБД, такая практика допустима.

WITH t1 AS (
    -- Выборка необработанных данных из первой таблицы
    SELECT * FROM table1
    WHERE "IsProcessed" = false
),
t2 AS (
    -- Выборка необработанных данных из второй таблицы
    SELECT * FROM table2
    WHERE "IsProcessed" = false
),
-- Блок обработки данных (джойны, агрегации, преобразования)
result AS (
    SELECT ... -- Результат расчётов на основе t1 и t2
),
-- Блок обновления статусов в исходных таблицах
upd1 AS (
    UPDATE table1
    SET "IsProcessed" = true
    FROM t1
    WHERE t1.id = table1.id
),
upd2 AS (
    UPDATE table2
    SET "IsProcessed" = true
    FROM t2
    WHERE t2.id = table2.id
),
-- Блок вставки результатов в целевую таблицу
ins1 AS (
    INSERT INTO result_table
    SELECT * FROM result
)
-- Финальный SELECT возвращает результат клиенту
SELECT * FROM result;

Ключевые вопросы и альтернативы

Насколько корректно такое решение? Технически оно работоспособно и обеспечивает требуемую атомарность. Однако его можно считать антипаттерном по следующим причинам:

• Нарушение принципа единой ответственности: Запрос совмещает выборку, бизнес-логику, модификацию данных и возврат результата, что снижает читаемость и усложняет поддержку.
• Сложность отладки: Трудно изолировать и протестировать отдельные этапы процесса.
• Потенциальные проблемы с производительностью: Сложные CTE с операциями DML могут создавать неочевидные планы выполнения.

Рекомендуемая альтернатива

Более чистый подход - использование явной транзакции на уровне приложения или хранимой процедуры:

1. Начать транзакцию.
2. Выполнить SELECT ... FOR UPDATE для блокировки обрабатываемых строк в исходных таблицах.
3. Провести необходимые расчёты на стороне приложения или в БД.
4. Выполнить INSERT в целевую таблицу.
5. Выполнить UPDATE исходных таблиц.
6. Зафиксировать (COMMIT) транзакцию или откатить (ROLLBACK) при ошибке.

Это разделяет логические этапы, делает код более прозрачным и упрощает мониторинг каждого шага.

Вывод

Использование CTE для DML-операций возможно, но не является лучшей практикой для production-решений. Для сложных процессов обработки данных предпочтительнее явное управление транзакциями с чётким разделением этапов: выборка, обработка, запись, обновление статусов. Это улучшает читаемость, тестируемость и долгосрочную поддержку кода.