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

У сучасних системах баз даних бізнес-логіка часто реалізується на стороні сервера — за допомогою процедур і функцій. Працюючи з 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 заборонені повністю (будь-яка з них дасть синтаксичну помилку). Для часткового відкату використовуй вкладений блок BEGIN ... EXCEPTION ... END.
• Процедури можна викликати тільки окремою 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 і ROLLBACK TO SAVEPOINT у PL/pgSQL заборонені — для часткового відкату всередині процедури використовуй блок BEGIN ... EXCEPTION ... END.
• Якщо процедура з транзакційним керуванням викликається всередині вже активної явної транзакції (наприклад, через клієнт без автокоміту), спроба виконати COMMIT викличе помилку.

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

-- Процедура з поетапним комітом (працює тільки в 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 (і всередині функцій, і процедур) не підтримує жодну з команд керування точками збереження: SAVEPOINT, RELEASE SAVEPOINT і ROLLBACK TO SAVEPOINT заборонені повністю.

Для відкату змін частини коду використовується тільки блок BEGIN ... EXCEPTION ... END:

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

У процедурах для часткового відкату можна використовувати тільки вкладений блок BEGIN ... EXCEPTION ... END: команди SAVEPOINT, RELEASE SAVEPOINT і ROLLBACK TO SAVEPOINT у PL/pgSQL заборонені повністю.

Обмеження та кращі практики

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

Питання по взаємодії логіки і транзакцій

Чи можу я зробити "вкладену" транзакцію всередині функції?

Ні. Все виконується в одній транзакції. Для часткового відкату — тільки EXCEPTION-блоки.

Чи можу я робити COMMIT/ROLLBACK всередині функції або анонімного блоку?

Ні, це синтаксична помилка. Використовуй процедури.

Чи можна викликати процедуру з функції?

Ні, тільки командою CALL. З функції/SELECT — не можна.

Чи можна в процедурі робити ROLLBACK TO SAVEPOINT?

Ні! У PL/pgSQL це заборонено. Використовуй EXCEPTION-блоки.