Kompleksowy debugging i optymalizacja wieloetapowej procedury

Wieloetapowe procedury to taki "szwajcarski scyzoryk" dla baz danych. Często składają się z walidacji danych wejściowych, wykonywania zmian (np. update rekordów, insert logów), a czasem też z analityki. Ale tu jest haczyk: im bardziej złożona procedura, tym większa szansa na błąd. Błąd logiczny, wolne zapytanie, pominięty niuans — i wszystko może się posypać.

Kompleksowy debugging obejmuje takie aspekty:

1. Analiza danych wejściowych: czy parametry są dobrze ustawione? Czy przekazane dane są poprawne?
2. Sprawdzanie wykonania kluczowych etapów: czy wszystkie kroki procedury są poprawnie wykonywane?
3. Logowanie wyników pośrednich: żeby wiedzieć, co się stało zanim coś się "wywaliło".
4. Optymalizacja wąskich gardeł wydajności: poprawiamy słabe punkty, które "zamulają" zapytania.

Postawienie zadania: przykład wieloetapowej procedury

Dla naszego praktycznego przykładu wyobraź sobie, że pracujemy z bazą sklepu internetowego. Musimy stworzyć procedurę do obsługi zamówienia. Będzie ona wykonywać takie kroki:

1. Sprawdzać dostępność produktu w magazynie.
2. Rezerwować produkt.
3. Aktualizować status zamówienia.
4. Zapisywać zdarzenia (np. udana rezerwacja albo błąd) do tabeli logów.

Skrypt struktury bazy danych:

-- Tabela produktów
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

-- Tabela zamówień
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    order_status TEXT NOT NULL
);

-- Tabela logów
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

Krok 1: Tworzenie wieloetapowej procedury

Stwórzmy bazową procedurę process_order. Będzie przyjmować identyfikator zamówienia i wykonywać wszystkie etapy obsługi.

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. Pobieramy identyfikator produktu i status zamówienia
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Zamówienie % nie istnieje lub brak product_id', p_order_id;
    END IF;

    -- 2. Sprawdzamy dostępność produktu w magazynie
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Produkt % jest niedostępny w magazynie', v_product_id;
    END IF;

    -- 3. Aktualizujemy ilość w magazynie
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Aktualizujemy status zamówienia
    UPDATE orders
    SET order_status = 'Przetworzone'
    WHERE order_id = p_order_id;

    -- 5. Zapisujemy udane zdarzenie do loga
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Zamówienie przetworzone pomyślnie.');
END;
$$;

Krok 2: Logowanie błędów z RAISE NOTICE i RAISE EXCEPTION

Tu zaczyna się magia. Dodamy logowanie pośrednich kroków, żeby wyłapać błędy i ogarnąć, co się dzieje na każdym etapie.

Zaktualizowany kod z logowaniem:

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 'Przetwarzanie zamówienia %...', p_order_id;

    -- 1. Pobieramy identyfikator produktu
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Zamówienie % nie istnieje lub brak product_id', p_order_id;
    END IF;
    RAISE NOTICE 'Product ID dla zamówienia %: %', p_order_id, v_product_id;

    -- 2. Sprawdzamy dostępność produktu w magazynie
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Produkt % jest niedostępny w magazynie', v_product_id;
    END IF;
    RAISE NOTICE 'Ilość w magazynie dla produktu %: %', v_product_id, v_stock_quantity;

    -- 3. Aktualizujemy ilość w magazynie
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Aktualizujemy status zamówienia
    UPDATE orders
    SET order_status = 'Przetworzone'
    WHERE order_id = p_order_id;

    -- 5. Logujemy udane wykonanie
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Zamówienie przetworzone pomyślnie.');
    RAISE NOTICE 'Zamówienie % przetworzone pomyślnie.', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- Logujemy błąd
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Błąd: ' || SQLERRM);
    RAISE;
END;
$$;

Krok 3: Optymalizacja za pomocą indeksów

Jeśli w bazie jest dużo produktów albo zamówień, szukanie odpowiednich wierszy może być wąskim gardłem. Dodajmy indeksy, żeby przyspieszyć wyszukiwanie podczas obsługi:

-- Indeks dla przyspieszenia wyszukiwania w tabeli orders
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Indeks dla przyspieszenia wyszukiwania w tabeli products
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

Krok 4: Analiza wydajności z EXPLAIN ANALYZE

Teraz sprawdzimy, jak szybko działa nasza funkcja. W tym celu wywołamy ją z analizą wydajności:

EXPLAIN ANALYZE
SELECT process_order(1);

Wynik pokaże, ile czasu zajmuje każdy etap. Będziemy mogli sprawdzić, który krok działa najwolniej — to pomoże nam dalej optymalizować procedurę.

Krok 5: Ulepszenie z użyciem transakcji

Dla większej niezawodności można spakować całą procedurę w transakcję. Dzięki temu, jeśli coś pójdzie nie tak, wszystkie zmiany się wycofają.

BEGIN;

-- Wywołanie funkcji
SELECT process_order(1);

-- Commit transakcji
COMMIT;

W samym kodzie funkcji można użyć SAVEPOINT i ROLLBACK TO SAVEPOINT do obsługi częściowych błędów.

Zadanie praktyczne: obsługa masowych zamówień

Na koniec wykładu przykład obsługi kilku zamówień naraz. Stworzymy funkcję, która obsłuży wszystkie zamówienia ze statusem Oczekujące:

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 = 'Oczekujące'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Nie udało się przetworzyć zamówienia %: %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

Po wywołaniu tej funkcji wszystkie zamówienia ze statusem Oczekujące zostaną obsłużone, a wszelkie błędy będą tylko logowane.

W ten sposób pokazaliśmy, jak debugować i optymalizować złożone procedury, zwiększając ich niezawodność, wydajność i czytelność. Ta wiedza przyda Ci się w realnych projektach, gdzie od jakości procedur zależy sukces aplikacji.