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

Переходные таблицы, введенные в PostgreSQL 10, предоставляют эту возможность через клаузу REFERENCING в триггерах на уровне выражений. Этот механизм предоставляет весь набор результатов модификации в виде временных таблиц — NEW TABLE для операций INSERT/UPDATE и OLD TABLE для операций DELETE/UPDATE, что позволяет функции триггера выполнять обработку на основе наборов. Вместо того чтобы проходить по строкам по отдельности, вы можете выполнить одно SQL выражение, которое действует на все затронутые строки одновременно.

CREATE TRIGGER bulk_audit_trigger
AFTER UPDATE ON inventory
REFERENCING NEW TABLE AS updated_items OLD TABLE AS previous_items
FOR EACH STATEMENT
EXECUTE FUNCTION log_inventory_changes();

Внутри log_inventory_changes(), запрос к updated_items возвращает все строки, затронутые вызывающим UPDATE, что позволяет эффективно выполнять массовые операции, такие как INSERT INTO audit_log SELECT * FROM updated_items;.

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

Платформа электронной коммерции испытывала серьезные проблемы с производительностью во время ночных массовых корректировок цен, затрагивающих 100 000 записей продуктов. Существующая архитектура использовала триггер уровня строки AFTER для записи аудиторских записей, что вызывало 100 000 последовательных INSERT в таблицу price_history и исчерпывало ресурсы пула соединений.

Решение 1: Обработка пакетов на стороне приложения Команда рассматривала возможность убрать триггер и управлять аудитом в приложении на Java, используя пакетные вставки JDBC. Хотя это бы уменьшило нагрузку на процессор базы данных, это ввело бы критические риски консистентности: если приложение зависло в середине пакета, зафиксированные обновления цен навсегда останутся без соответствующих аудиторских записей, что нарушит соответствие SOX. Кроме того, этот подход потребовал бы сложного управления распределенными транзакциями между сервером приложений и PostgreSQL.

Решение 2: Асинхронная очередь сообщений Еще одно предложение заключалось в том, чтобы записывать идентификаторы строк в поток Redis во время обновления, а затем обрабатывать аудит с помощью фонового работника. Это разъединило пути записи, но жертвовало немедленной транзакционной консистентностью. Асинхронный работник мог отставать в условиях высокой нагрузки, создавая временные разрывы в аудиторской цепочке, которые могли бы быть замечены регулирующими аудиторами. Кроме того, обеспечение семантики точно-один против PostgreSQL и Redis добавляло значительную сложность инфраструктуры.

Решение 3: Триггер уровня выражения с переходными таблицами Выбранный подход заменил триггер уровня строки на триггер уровня выражения, используя REFERENCING NEW TABLE AS new_prices. Функция триггера выполняла одну операцию на основе набора: INSERT INTO price_history SELECT product_id, old_price, new_price, NOW() FROM new_prices;. Это обеспечивало строгую ACID совместимость в рамках одной транзакции, обрабатывая все изменения за один проход.

Результат: Время завершения ночной партии сократилось с 45 секунд до 300 миллисекунд. Генерация WAL (Write-Ahead Log) снизилась на 90%, и система устранила всплески блокировок, ранее вызванные массовой рекурсией триггеров.

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

Как переходные таблицы взаимодействуют с триггерами BEFORE и триггерами INSTEAD OF на представлениях?

Переходные таблицы доступны исключительно в триггерах AFTER на обычных таблицах. Триггеры BEFORE работают с отдельными строками до того, как будет материализирован окончательный набор результатов выражения, поэтому полный набор измененных строк еще не существует. Триггеры INSTEAD OF на представлениях не могут использовать переходные таблицы, потому что они определяют альтернативные пути выполнения, а не наблюдают за фактическим результатом операций DML на основе таблиц.

Может ли функция триггера изменять данные внутри NEW TABLE или OLD TABLE, чтобы изменить окончательный результат?

Нет, переходные таблицы являются только для чтения снимками, доступными только во время выполнения триггера. Они представляют собой неизменяемые представления строк, затронутых выражением, и не могут быть изменены с помощью операций UPDATE, DELETE или INSERT. Чтобы изменить значения перед их фиксацией, необходимо использовать триггеры уровня строки BEFORE, которые непосредственно манипулируют переменной NEW, или выполнять отдельные DML операции против постоянных таблиц.

Почему операции TRUNCATE не заполняют переходную таблицу OLD TABLE?

Хотя PostgreSQL поддерживает триггеры на TRUNCATE на уровне выражения, эта команда DDL удаляет все строки, не генерируя отдельные переходные состояния строк через систему MVCC. TRUNCATE обходит механизм версии кортежей, используемый для построения переходных отношений, поэтому не может заполнять OLD TABLE удаленными строками. Аудит операций TRUNCATE требует альтернативных механизмов, таких как событийные триггеры или потоки логической декодировки.