Praktische Beispiele für die Arbeit mit verschachtelten Transaktionen

Heute ist unsere Aufgabe, eine Funktion zu entwickeln, die:

1. Das Guthaben des Kunden prüft. Vor dem Abbuchen muss geprüft werden, ob genug Geld da ist.
2. Geld vom Konto abbucht. Wenn genug Geld drauf ist, wird abgebucht.
3. Erfolgreiche und fehlgeschlagene Aktionen loggt. Alle Aktionen werden in einer Log-Tabelle für spätere Analyse gespeichert.

Das ist nicht einfach nur eine langweilige Subtraktionsfunktion. Hier nutzen wir verschachtelte Transaktionen, um Änderungen zurückzusetzen, falls was schiefgeht (z.B. zu wenig Geld oder Fehler beim Loggen). Wir entdecken, wie nützlich SAVEPOINTs sind und lernen, wie man Prozeduren fehlertolerant macht.

Wir erstellen die Anfangstabellen

Bevor wir mit der Funktion loslegen, bereiten wir die Datenbank vor. Wir brauchen drei Tabellen:

1. clients — speichert Infos über Kunden und deren Guthaben.
2. payments — speichert erfolgreiche Transaktionen.
3. logs — speichert Infos über alle Zahlungsversuche (erfolgreich und fehlgeschlagen).

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

-- Tabelle für erfolgreiche Zahlungen
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()
);

-- Log-Tabelle
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()
);

Wir füllen die clients-Tabelle mit Testdaten

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

Jetzt haben wir drei Kunden: Otto hat 100 auf dem Konto, Maria 50 und Anna 0.

Business-Logik umsetzen: PROZEDUR vs FUNKTION

Kurz gesagt:

• Für "alles oder nichts"-Business-Operationen reicht eine Funktion.
• Für schrittweise Transaktionskontrolle, Teil-Commits, Rollbacks, Fehler-Logging — nimm eine Prozedur (CREATE PROCEDURE).

Warum keine Funktion? In PostgreSQL 17 kannst du in einer Funktion KEIN COMMIT, SAVEPOINT oder ROLLBACK nutzen. Alles läuft atomar in der äußeren Transaktion.

Nur eine Prozedur (CREATE PROCEDURE ... LANGUAGE plpgsql) erlaubt SAVEPOINT, COMMIT, ROLLBACK — aber mit wichtigen Einschränkungen:

• In der Prozedur sind SAVEPOINT, COMMIT, RELEASE SAVEPOINT erlaubt.
• ROLLBACK TO SAVEPOINT ist verboten in PL/pgSQL (gibt einen Fehler), stattdessen nutzt man BEGIN ... EXCEPTION ... END-Blöcke, die einen "virtuellen Savepoint" machen.

Die Haupttechnik für Teil-Rollbacks:

BEGIN
    -- dein Code
EXCEPTION
    WHEN OTHERS THEN
        -- Dieser Block rollt ALLE Änderungen im Block zurück, wenn ein Fehler auftritt!
        -- Im Log kann man Infos hinterlassen:
        INSERT INTO logs (...) VALUES (...);
END;

Wir bauen die Prozedur für Zahlungen mit Teil-Rollback und Logging

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Guthaben des Kunden holen
    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, 'Kunde nicht gefunden, Vorgang abgelehnt');
        RAISE EXCEPTION 'Kunde mit ID % nicht gefunden', in_client_id;
    END IF;

    -- Prüfen, ob genug Geld da ist
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Nicht genug Guthaben für Abbuchung ' || in_payment_amount || ' EUR.');
        -- Prozedur beenden
        RETURN;
    END IF;

    -- Block für atomare Änderungen; bei Fehler — Rollback (virtueller Savepoint)
    BEGIN
        -- Guthaben abbuchen
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- Erfolgreiche Zahlung eintragen
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- Erfolg loggen
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Erfolgreiche Abbuchung ' || in_payment_amount || ' EUR.');

    EXCEPTION
        WHEN OTHERS THEN
            -- Alle Änderungen im Block werden zurückgesetzt
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, 'Fehler bei Zahlung: ' || SQLERRM);
            -- (kein explizites ROLLBACK TO SAVEPOINT nötig — ist verboten und nicht erforderlich)
    END;
END;
$$;

Kurz was passiert:

• Wenn zu wenig Geld/Kunde fehlt — loggen und raus.
• Der ganze kritische Code ist im BEGIN ... EXCEPTION ... END-Block.
• Wenn irgendein Fehler im Block — alles wird automatisch zurückgesetzt; Fehler wird geloggt.
• Kein direktes SAVEPOINT oder ROLLBACK TO SAVEPOINT — das ist so gewollt, in PL/pgSQL läuft das nur über EXCEPTION-Blöcke.

Prozedur aufrufen

Wichtig: Die Prozedur rufst du mit CALL ... auf, und die Verbindung zur DB sollte im Autocommit-Modus oder außerhalb einer großen Transaktion sein!

CALL process_payment(1, 30.00);   -- Erfolgreiche Zahlung
CALL process_payment(2, 100.00);  -- Nicht genug Guthaben
CALL process_payment(999, 50.00); -- Kunde existiert nicht

Ergebnisse prüfen

• Guthaben des Kunden ändert sich nur, wenn die Zahlung durchgeht.
• Tabelle payments — Eintrag nur bei erfolgreicher Abbuchung.
• logs — Historie aller Versuche (und Fehler).

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

Echter Einsatz

Prozeduren für Transaktionsverarbeitung sind ein zentrales Element in Fintech, E-Commerce und sogar Gaming-Plattformen. Stell dir einen Online-Shop vor, der das Guthaben von Geschenkgutscheinen verwalten und beim Einkauf abbuchen muss, oder ein Banksystem mit tausenden Transaktionen pro Sekunde.

Dieses Wissen hilft dir in der Praxis, schützt die Daten deiner Kunden und verhindert katastrophale Fehler bei der Zahlungsabwicklung.