Практические примеры работы с вложенными транзакциями

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

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 и даже игровыми платформами. Представьте себе интернет-магазин, который должен учитывать баланс подарочных сертификатов, а также списывать их при совершении покупок, или банковскую систему с тысячами операций в секунду.

Эти знания пригодятся вам на практике, помогут обезопасить данные ваших клиентов и избежать катастрофических ошибок в обработке платежей.

2

Задача

SQL SELF, 54 уровень, 2 лекция

Недоступна

Обработка платежа для клиента

Обработка платежа для клиента

Открыть