Вызов процедур и функций внутри транзакций

В современных системах базы данных бизнес-логика часто реализуется на стороне сервера — с помощью процедур и функций. Работая с PostgreSQL, важно понимать разницу между функциями и процедурами (особенно с появлением процедур в версии 11+) и то, как они взаимодействуют с транзакциями.

Ниже я расскажу основные факты о механике транзакций, вложенных вызовах и частичном откате изменений в процедурах/функциях PostgreSQL 17, согласно официальной документации и актуальным ограничениям.

Ключевые понятия: функции vs процедуры

Функция (CREATE FUNCTION) — выполняется всегда в рамках одной внешней транзакции; внутри функций нельзя использовать явные транзакционные команды (BEGIN, COMMIT, ROLLBACK, SAVEPOINT).

• Любые изменения фиксируются или откатываются только на уровне внешней транзакции.
• Для «частичного отката» внутри функций применяется BEGIN ... EXCEPTION ... END, но это не позволяет делать коммиты внутри функции.

Процедура (CREATE PROCEDURE) — появилась для управления транзакциями непосредственно на сервере (например, выполнять частичные коммиты, откаты этапов и т.п.).

• В процедурах (PL/pgSQL) можно использовать COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT.
• ВАЖНО: нельзя использовать ROLLBACK TO SAVEPOINT в PL/pgSQL-процедуре (выдаст синтаксическую ошибку).
• Процедуры можно вызывать только отдельной SQL-командой CALL ..., а не через SELECT или внутри других функций.

Как вызывать одну процедуру/функцию из другой?

Функции «прозрачно» вызывают другие функции через обычное обращение по имени:

-- Пример: функция для расчёта скидки
CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF order_total >= 100 THEN
        RETURN order_total * 0.1;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Функция-обработка заказа вызывает другую функцию
CREATE OR REPLACE FUNCTION process_order(order_id INT, order_total NUMERIC)
RETURNS VOID AS $$
DECLARE
    discount NUMERIC;
BEGIN
    discount := calculate_discount(order_total);
    RAISE NOTICE 'Скидка: %', discount;
    INSERT INTO orders_log (order_id, order_total, discount)
    VALUES (order_id, order_total, discount);
END;
$$ LANGUAGE plpgsql;

Всё выполняется в рамках одной внешней транзакции! Ошибка в любой функции приведёт к откату всех изменений.

Вызов процедур и вложенные транзакции

Процедуры можно вызывать внутри других процедур через команду CALL ... (в PostgreSQL 17 допускается стек вызовов CALL proc1() -> CALL proc2()), однако правила транзакций сохраняются:

• Транзакционные команды (COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT) доступны только на верхнем уровне процедур.
• Если процедура с транзакционным управлением вызывается внутри уже активной явной транзакции (например, через клиент без автокоммита), попытка выполнить COMMIT/SAVEPOINT вызовет ошибку.

Пример процедуры с управлением транзакциями

-- Процедура с поэтапным коммитом (работает только в autocommit режиме соединения)
CREATE PROCEDURE process_batch_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT order_id, order_total FROM incoming_orders LOOP
        BEGIN
            -- Сохраняем каждую партию данных отдельно
            INSERT INTO orders (order_id, total) VALUES (rec.order_id, rec.order_total);
        EXCEPTION WHEN OTHERS THEN
            INSERT INTO order_errors(order_id, err_text) VALUES (rec.order_id, SQLERRM);
        END;
        COMMIT;
    END LOOP;
END;
$$;

-- Вызов процедуры
CALL process_batch_orders();

После каждого COMMIT автоматически начинается новая транзакция.

Частичный откат (savepoint-like behavior) в PL/pgSQL

PL/pgSQL (и внутри функций, и процедур) не поддерживает команду ROLLBACK TO SAVEPOINT.

Для отката изменений части кода используется только блок BEGIN ... EXCEPTION ... END:

BEGIN
    -- какие-то действия
    BEGIN
        -- потенциально ошибочная операция
    EXCEPTION WHEN OTHERS THEN
        -- все изменения этого блока откатятся
        RAISE NOTICE 'Откат внутри блока!';
    END;
END;

В процедурах также можно использовать SAVEPOINT и RELEASE SAVEPOINT, но не ROLLBACK TO SAVEPOINT. Их смысл — разграничивать этапы, но управлять ими можно только через обработку исключений.

Ограничения и лучшие практики

1. Функции — только атомарные операции: все or nothing. Если что-то пошло не так — откатятся все изменения.
2. Процедуры — только через CALL: и только отдельной SQL-командой, не из SELECT/функций. Вложенное управление транзакциями возможно, но строго с соблюдением ограничений PL/pgSQL.
3. Частичный откат — только через EXCEPTION: официально рекомендуемый и поддерживаемый способ для частичного отката (аналог SAVEPOINT).
4. Вложенные процедуры могут управлять транзакциями только при вызове через CALL: иначе произойдёт ошибка.

Вопросы по взаимодействию логики и транзакций

Могу ли я сделать "вложенную" транзакцию внутри функции?

Нет. Всё выполняется в одной транзакции. Для частичного отката — только EXCEPTION-блоки.

Могу ли я делать COMMIT/ROLLBACK внутри функции или анонимного блока?

Нет, это синтаксическая ошибка. Используйте процедуры.

Можно ли вызывать процедуру из функции?

Нет, только командой CALL. Из функции/SELECT — нельзя.

Можно ли в процедуре делать ROLLBACK TO SAVEPOINT?

Нет! В PL/pgSQL это запрещено. Используйте EXCEPTION-блоки.

2

Задача

SQL SELF, 53 уровень, 1 лекция

Недоступна

Создание и вызов процедур для расчёта и записи логов

Создание и вызов процедур для расчёта и записи логов

Открыть