Praktyczne przykłady pracy z zagnieżdżonymi transakcjami

Dzisiaj naszym zadaniem jest napisać funkcję, która:

1. Sprawdza saldo klienta. Przed pobraniem kasy trzeba sprawdzić, czy jest wystarczająco środków.
2. Pobiera środki z salda. Jeśli na saldzie jest wystarczająco kasy, robimy pobranie.
3. Loguje udane i nieudane operacje. Wszystko zapisujemy do tabeli logów, żeby potem można było to przeanalizować.

To nie jest po prostu nudna funkcja do odejmowania. Tutaj użyjemy zagnieżdżonych transakcji, żeby cofnąć zmiany, jeśli coś pójdzie nie tak (np. za mało środków albo błąd przy zapisie do loga). Poznamy zalety SAVEPOINT i nauczymy się robić procedury odporne na błędy.

Tworzymy początkowe tabele

Zanim zaczniemy pisać funkcję, przygotujmy bazę danych. Potrzebujemy trzech tabel:

1. clients — do trzymania danych o klientach i ich saldzie.
2. payments — do zapisywania udanych transakcji.
3. logs — do trzymania info o wszystkich próbach płatności (udanych i nieudanych).

-- Tabela klientów
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL DEFAULT 0
);

-- Tabela udanych płatności
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()
);

-- Tabela logów
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()
);

Wypełnijmy tabelę clients testowymi danymi

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

Teraz mamy trzech klientów: Otto ma na koncie 100, Maria — 50, a Anna — 0.

Implementacja logiki biznesowej: PROCEDURA vs FUNKCJA

W skrócie:

• Dla operacji biznesowych "wszystko albo nic" wystarczy funkcja.
• Dla etapowego zarządzania transakcjami, częściowych commitów, rollbacków, logowania błędów — używaj procedury (CREATE PROCEDURE).

Dlaczego nie funkcja? Chodzi o to, że w PostgreSQL 17 wewnątrz funkcji NIE możesz używać COMMIT, SAVEPOINT ani ROLLBACK. Wszystko dzieje się atomowo w ramach zewnętrznej transakcji.

Tylko procedura (CREATE PROCEDURE ... LANGUAGE plpgsql) pozwala na użycie SAVEPOINT, COMMIT, ROLLBACK — ale z ważnymi ograniczeniami:

• W procedurze można używać SAVEPOINT, COMMIT, RELEASE SAVEPOINT.
• ROLLBACK TO SAVEPOINT jest zabroniony w PL/pgSQL (będzie błąd), zamiast tego używamy bloków BEGIN ... EXCEPTION ... END, które robią "wirtualny savepoint".

Główna technika rollbacku części kodu:

BEGIN
    -- twój kod
EXCEPTION
    WHEN OTHERS THEN
        -- Ten blok przy błędzie cofnie WSZYSTKIE zmiany w środku!
        -- W logu można zostawić info:
        INSERT INTO logs (...) VALUES (...);
END;

Tworzymy procedurę płatności z częściowym rollbackiem i logowaniem

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Pobieramy saldo klienta
    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, 'Klient nie znaleziony, operacja odrzucona');
        RAISE EXCEPTION 'Klient z ID % nie znaleziony', in_client_id;
    END IF;

    -- Sprawdzamy czy są środki
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Za mało środków do pobrania ' || in_payment_amount || ' zł.');
        -- Kończymy procedurę
        RETURN;
    END IF;

    -- Blok na zmiany atomowe; przy błędzie — rollback (wirtualny savepoint)
    BEGIN
        -- Odejmujemy saldo
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- Dodajemy wpis o udanej płatności
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- Logujemy sukces
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Udane pobranie ' || in_payment_amount || ' zł.');

    EXCEPTION
        WHEN OTHERS THEN
            -- Wszystkie zmiany w tym bloku są cofane
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, 'Błąd przy płatności: ' || SQLERRM);
            -- (nie trzeba jawnie ROLLBACK TO SAVEPOINT — jest zabroniony i niepotrzebny)
    END;
END;
$$;

W skrócie co się dzieje:

• Jeśli za mało środków/klienta nie ma — logujemy i wychodzimy.
• Cały krytyczny kod jest w bloku BEGIN ... EXCEPTION ... END.
• Jeśli jakikolwiek błąd w tym bloku — wszystkie zmiany w nim są automatycznie cofane; wpisujemy błąd do logów.
• Nie używamy bezpośrednio SAVEPOINT i ROLLBACK TO SAVEPOINT — tak ma być, w PL/pgSQL działa to tylko przez bloki EXCEPTION.

Wywołanie procedury

Ważne: procedurę wywołujesz komendą CALL ..., przy czym połączenie z bazą musi być w trybie autocommit albo poza dużą jawnie rozpoczętą transakcją!

CALL process_payment(1, 30.00);   -- Udana płatność
CALL process_payment(2, 100.00);  -- Za mało środków
CALL process_payment(999, 50.00); -- Klienta nie ma

Sprawdzanie wyników

• Zmiana salda klienta — tylko jeśli płatność przeszła.
• Tabela payments — wpis tylko przy udanym pobraniu.
• logs — historia wszystkich prób (i błędów).

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

Prawdziwe zastosowanie

Procedury do obsługi transakcji to jedna z kluczowych części systemów fintech, e-commerce i nawet platform gamingowych. Wyobraź sobie sklep internetowy, który musi ogarniać saldo voucherów i pobierać je przy zakupach, albo system bankowy z tysiącami operacji na sekundę.

Ta wiedza przyda ci się w praktyce, pomoże zabezpieczyć dane twoich klientów i uniknąć katastrofalnych błędów przy obsłudze płatności.