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

Сьогодні наше завдання — розробити функцію, яка:

1. Перевіряє баланс клієнта. Перед списанням суми треба перевірити, чи достатньо коштів.
2. Списує кошти з балансу. Якщо на балансі достатньо грошей, відбувається списання.
3. Логує успішні та неуспішні операції. Усі дії записуються в таблицю логів для подальшого аналізу.

Це не просто нудна функція для віднімання. Тут ми будемо використовувати вкладені транзакції, щоб відкотити зміни, якщо щось піде не так (наприклад, недостатньо коштів або помилка при записі лога). Побачимо користь точок збереження (SAVEPOINT) і навчимося робити процедури стійкими до помилок.

Створюємо початкові таблиці

Перед тим як приступити до створення функції, давай підготуємо базу даних. Нам потрібно три таблиці:

1. clients — для зберігання даних про клієнтів і їх баланс.
2. payments — для запису успішних транзакцій.
3. logs — для зберігання інформації про всі спроби платежів (успішних і неуспішних).

-- Таблиця клієнтів
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL DEFAULT 0
);

-- Таблиця успішних платежів
CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    amount NUMERIC(10, 2) NOT NULL,
    payment_date TIMESTAMP DEFAULT NOW()
);

-- Таблиця логів
CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    message TEXT NOT NULL,
    log_date TIMESTAMP DEFAULT NOW()
);

Заповнимо таблицю clients тестовими даними

INSERT INTO clients (full_name, balance)
VALUES 
    ('Otto Song', 100.00),
    ('Maria Chi', 50.00),
    ('Anna Vel', 0.00);

Тепер у нас є три клієнти: у Отто на рахунку 100, у Марії — 50, а у Анни — 0.

Реалізація бізнес-логіки: ПРОЦЕДУРА vs ФУНКЦІЯ

Коротко:

• Для бізнес-операцій "все або нічого" достатньо функції.
• Для поетапного керування транзакціями, часткових комітів, відкатів, логування помилок — використовуй процедуру (CREATE PROCEDURE).

Чому не функція? Справа в тому, що в PostgreSQL 17 всередині функції ти НЕ можеш використовувати ні COMMIT, ні SAVEPOINT, ні ROLLBACK. Всі зміни робляться атомарно в межах зовнішньої транзакції.

Тільки процедура (CREATE PROCEDURE ... LANGUAGE plpgsql) дозволяє використовувати SAVEPOINT, COMMIT, ROLLBACK — але з важливими обмеженнями:

• Всередині процедури дозволені SAVEPOINT, COMMIT, RELEASE SAVEPOINT.
• ROLLBACK TO SAVEPOINT заборонений у PL/pgSQL (буде помилка), замість цього використовують блоки BEGIN ... EXCEPTION ... END, які роблять "віртуальний сейвпоінт".

Основна техніка відкату частини коду:

BEGIN
    -- твій код
EXCEPTION
    WHEN OTHERS THEN
        -- Цей блок при помилці відкотить ВСІ зміни всередині блоку!
        -- У логу можна залишити інформацію:
        INSERT INTO logs (...) VALUES (...);
END;

Створюємо процедуру для платежу з частковим відкатом і логуванням

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Отримуємо баланс клієнта
    SELECT balance INTO current_balance
    FROM clients
    WHERE client_id = in_client_id;

    IF NOT FOUND THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Клієнта не знайдено, операцію відхилено');
        RAISE EXCEPTION 'Клієнт з ID % не знайдений', in_client_id;
    END IF;

    -- Перевіряємо достатність коштів
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Недостатньо коштів для списання ' || in_payment_amount || ' грн.');
        -- Завершуємо процедуру
        RETURN;
    END IF;

    -- Блок для атомарних змін; при помилці — відкат (віртуальний savepoint)
    BEGIN
        -- Списуємо баланс
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- Додаємо запис про успішний платіж
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- Логуємо успіх
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Успішне списання ' || in_payment_amount || ' грн.');

    EXCEPTION
        WHEN OTHERS THEN
            -- Всі зміни всередині цього блоку скасовуються
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, 'Помилка при платіжці: ' || SQLERRM);
            -- (не потрібен явний ROLLBACK TO SAVEPOINT — він заборонений і не потрібен)
    END;
END;
$$;

Коротко що відбувається:

• Якщо коштів мало/клієнта нема — логуємо і виходимо.
• Весь критичний код поміщено в блок BEGIN ... EXCEPTION ... END.
• Якщо будь-яка помилка всередині цього блоку — всі зміни в ньому автоматично скасовуються; пишемо помилку в логи.
• Немає прямого використання SAVEPOINT і ROLLBACK TO SAVEPOINT — це так і має бути, в PL/pgSQL працює тільки через блоки EXCEPTION.

Виклик процедури

Важливо: викликати процедуру треба командою CALL ..., причому зовнішнє з'єднання з базою має бути в режимі autocommit або поза явною великою транзакцією!

CALL process_payment(1, 30.00);   -- Успішний платіж
CALL process_payment(2, 100.00);  -- Недостатньо коштів
CALL process_payment(999, 50.00); -- Клієнта нема

Перевірка результатів

• Зміни балансу клієнта — тільки якщо платіж пройшов.
• Таблиця payments — запис тільки при успішному списанні.
• logs — історія всіх спроб (і помилок).

SELECT * FROM clients;
SELECT * FROM payments;
SELECT * FROM logs;

Реальне застосування

Процедури для обробки транзакцій — одна з центральних частин систем, пов'язаних з фінтехом, e-commerce і навіть ігровими платформами. Уяви собі інтернет-магазин, який має враховувати баланс подарункових сертифікатів, а також списувати їх при покупках, або банківську систему з тисячами операцій на секунду.

Ці знання знадобляться тобі на практиці, допоможуть убезпечити дані твоїх клієнтів і уникнути катастрофічних помилок при обробці платежів.