Umfassendes Debugging und Optimierung einer mehrstufigen Prozedur

Mehrstufige Prozeduren sind das "Schweizer Taschenmesser" für Datenbanken. Sie bestehen oft aus Validierung der Eingabedaten, Durchführung von Änderungen (zum Beispiel Update von Datensätzen, Einfügen von Logs) und manchmal auch aus Analytics. Aber hier ist das Problem: Je komplexer die Prozedur, desto größer die Fehlerwahrscheinlichkeit. Logikfehler, langsame Query, übersehene Details – und schon läuft alles schief.

Umfassendes Debugging umfasst folgende Aspekte:

1. Analyse der Eingabedaten: Sind die Parameter richtig gesetzt? Wurden die richtigen Daten übergeben?
2. Überprüfung der Ausführung der Schlüsselschritte: Werden alle Schritte der Prozedur korrekt ausgeführt?
3. Logging von Zwischenergebnissen: Damit du weißt, was passiert ist, bevor etwas "abstürzt".
4. Optimierung von Performance-Engpässen: Wir verbessern die Schwachstellen, die die Queries "ausbremsen".

Problemstellung: Beispiel einer mehrstufigen Prozedur

Für unser Praxisbeispiel stellen wir uns vor, dass wir mit einer Datenbank eines Online-Shops arbeiten. Wir müssen eine Prozedur zur Bearbeitung einer Bestellung erstellen. Sie wird folgende Schritte ausführen:

1. Überprüfen, ob das Produkt auf Lager ist.
2. Das Produkt reservieren.
3. Status der Bestellung aktualisieren.
4. Ereignisse (z.B. erfolgreiche Reservierung oder Fehler) in die Log-Tabelle schreiben.

Datenbankstruktur-Skript:

-- Tabelle der Produkte
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

-- Tabelle der Bestellungen
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    order_status TEXT NOT NULL
);

-- Tabelle der Logs
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

Schritt 1: Erstellen der mehrstufigen Prozedur

Lass uns eine Basis-Prozedur process_order bauen. Sie nimmt die Bestell-ID und führt alle Bearbeitungsschritte aus.

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    -- 1. Produkt-ID und Bestellstatus holen
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Bestellung % existiert nicht oder product_id fehlt', p_order_id;
    END IF;

    -- 2. Prüfen, ob das Produkt auf Lager ist
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Produkt % ist nicht auf Lager', v_product_id;
    END IF;

    -- 3. Lagerbestand aktualisieren
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Bestellstatus aktualisieren
    UPDATE orders
    SET order_status = 'Verarbeitet'
    WHERE order_id = p_order_id;

    -- 5. Erfolgreiches Ereignis ins Log schreiben
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Bestellung erfolgreich verarbeitet.');
END;
$$;

Schritt 2: Fehler-Logging mit RAISE NOTICE und RAISE EXCEPTION

Jetzt kommt die Magie. Wir fügen Logging für Zwischenschritte hinzu, um Fehler zu catchen und zu verstehen, was in jedem Schritt passiert.

Aktualisierter Code mit Logging:

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    RAISE NOTICE 'Bearbeite Bestellung %...', p_order_id;

    -- 1. Produkt-ID holen
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Bestellung % existiert nicht oder product_id fehlt', p_order_id;
    END IF;
    RAISE NOTICE 'Produkt-ID für Bestellung %: %', p_order_id, v_product_id;

    -- 2. Prüfen, ob das Produkt auf Lager ist
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Produkt % ist nicht auf Lager', v_product_id;
    END IF;
    RAISE NOTICE 'Lagerbestand für Produkt %: %', v_product_id, v_stock_quantity;

    -- 3. Lagerbestand aktualisieren
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Bestellstatus aktualisieren
    UPDATE orders
    SET order_status = 'Verarbeitet'
    WHERE order_id = p_order_id;

    -- 5. Erfolgreiches Logging
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Bestellung erfolgreich verarbeitet.');
    RAISE NOTICE 'Bestellung % erfolgreich verarbeitet.', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- Fehler ins Log schreiben
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Fehler: ' || SQLERRM);
    RAISE;
END;
$$;

Schritt 3: Optimierung mit Indexen

Wenn es viele Produkte oder Bestellungen in der Datenbank gibt, kann die Suche nach den richtigen Zeilen zum Flaschenhals werden. Wir fügen Indexe hinzu, um die Auswahl während der Bearbeitung zu beschleunigen:

-- Index zur Beschleunigung der Suche in der Tabelle orders
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Index zur Beschleunigung der Suche in der Tabelle products
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

Schritt 4: Performance-Analyse mit EXPLAIN ANALYZE

Jetzt checken wir, wie schnell unsere Funktion läuft. Dafür rufen wir sie mit Performance-Analyse auf:

EXPLAIN ANALYZE
SELECT process_order(1);

Das Ergebnis zeigt, wie lange jeder Schritt dauert. So kannst du herausfinden, welcher Schritt am langsamsten ist – das hilft dir, die Prozedur weiter zu optimieren.

Schritt 5: Verbesserung mit Transaktionen

Für mehr Zuverlässigkeit kann man die ganze Prozedur in eine Transaktion packen. Wenn etwas schiefgeht, werden alle Änderungen zurückgerollt.

BEGIN;

-- Funktionsaufruf
SELECT process_order(1);

-- Commit der Transaktion
COMMIT;

Im Funktionscode selbst kannst du SAVEPOINT und ROLLBACK TO SAVEPOINT nutzen, um Teilfehler zu behandeln.

Praxisaufgabe: Massenverarbeitung von Bestellungen

Lass uns die Vorlesung mit einem Beispiel abschließen, wie man mehrere Bestellungen auf einmal verarbeitet. Wir erstellen eine Funktion, die alle Bestellungen mit Status Ausstehend verarbeitet:

CREATE OR REPLACE FUNCTION process_all_orders()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    FOR v_order_id IN
        SELECT order_id
        FROM orders
        WHERE order_status = 'Ausstehend'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Bestellung % konnte nicht verarbeitet werden: %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

Wenn du diese Funktion aufrufst, werden alle Bestellungen mit Status Ausstehend verarbeitet, und alle Fehler werden nur geloggt.

So haben wir gezeigt, wie man komplexe Prozeduren debuggt und optimiert, um ihre Zuverlässigkeit, Performance und Lesbarkeit zu verbessern. Dieses Wissen wird dir in echten Projekten helfen, wo die Qualität der Prozeduren über den Erfolg der Anwendung entscheidet.