Analiza typowych błędów przy pracy z zagnieżdżonymi transakcjami

Programowanie w Postgresie to niezła przygoda: czasem zamienia się w quest pod tytułem „Znajdź swój błąd”. W tym bloku pogadamy o typowych błędach i pułapkach, które mogą się pojawić przy pracy z zagnieżdżonymi transakcjami. Lecimy!

Nieprawidłowe użycie komend transakcyjnych wewnątrz funkcji i procedur

Błąd: próba użycia COMMIT, ROLLBACK albo SAVEPOINT wewnątrz FUNCTION.

Dlaczego: W PostgreSQL funkcje (CREATE FUNCTION ... LANGUAGE plpgsql) zawsze działają w ramach jednej zewnętrznej transakcji i wszelkie komendy transakcyjne w środku funkcji są zabronione. Próba ich użycia skończy się błędem składni.

Przykład błędu:

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- Błąd: komendy transakcyjne są zabronione
END;
$$ LANGUAGE plpgsql;

Jak poprawnie:

Dla operacji atomowych, które mają być „wszystko albo nic”, używaj funkcji bez jawnych komend transakcyjnych. Jeśli potrzebujesz etapowego zatwierdzania zmian — użyj procedur.

Błąd: próba użycia ROLLBACK TO SAVEPOINT w procedurze w PL/pgSQL.

Dlaczego: w PostgreSQL 17 dozwolone są tylko komendy COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT wewnątrz procedur (CREATE PROCEDURE ... LANGUAGE plpgsql). Ale ROLLBACK TO SAVEPOINT w PL/pgSQL nie wolno używać! Każda taka próba skończy się błędem składni.

Przykład błędu:

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- Błąd! Nie wolno używać
END;
$$;

Jak poprawnie:

Dla “częściowego cofania” używaj bloków BEGIN ... EXCEPTION ... END — one automatycznie tworzą savepoint; jeśli w bloku poleci błąd, wszystkie zmiany się cofną do jego początku.

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- operacje, które mogą rzucić błąd
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Cofnięcie w bloku BEGIN ... EXCEPTION ... END';
    END;
END;
$$;

Zagnieżdżone wywołania procedur: ograniczenia i typowe błędy

Błąd: wywołanie procedury z jawnym COMMIT/ROLLBACK wewnątrz już otwartej transakcji klienckiej.

Dlaczego: procedury z kontrolą transakcji działają poprawnie tylko w trybie autocommit (jedna procedura — jedna transakcja), w innym przypadku próba użycia COMMIT lub ROLLBACK w środku procedury skończy się błędem: transakcja już otwarta po stronie klienta.

Przykład:

# W Pythonie z psycopg2 domyślnie autocommit=False
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- Błąd przy próbie COMMIT w środku my_proc

Jak poprawnie:

• Przed wywołaniem procedur przełącz połączenie w tryb autocommit.
• Nie wywołuj procedur przez funkcje ani przez SELECT.

Błąd: wywołanie procedur z kontrolą transakcji (COMMIT, ROLLBACK) nie działa, jeśli są wywoływane NIE przez CALL (np. przez SELECT).

Dlaczego: Tylko wywołanie przez CALL (albo w anonimowym bloku DO) pozwala zarządzać transakcjami. Wywoływanie z funkcji — nie wolno.

Problemy z blokadami i deadlockami

Blokady to jak nieproszeni goście: najpierw przeszkadzają, potem robią chaos. Deadlock pojawia się, gdy transakcje czekają na siebie w nieskończoność. Typowy przykład:

1. Transakcja A blokuje wiersz w tabeli orders i próbuje zaktualizować wiersz w tabeli products.
2. Transakcja B blokuje wiersz w tabeli products i próbuje zaktualizować wiersz w tabeli orders.

W efekcie żadna z transakcji nie może ruszyć dalej. To jak dwie fury próbujące wjechać w ten sam wąski zakręt naraz — kończy się korkiem.

Przykład:

-- Transakcja A
BEGIN;
UPDATE orders SET status = 'Przetwarzanie' WHERE id = 1;

-- Transakcja B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- Teraz transakcja A próbuje zaktualizować ten sam wiersz w `products`,
-- a transakcja B próbuje zmienić wiersz w `orders`.
-- Deadlock!

Jak tego uniknąć?

1. Zawsze aktualizuj dane w tej samej kolejności. Na przykład najpierw orders, potem products.
2. Unikaj zbyt długich transakcji.
3. Używaj LOCK z głową, ustawiając minimalny poziom blokady.

Nieprawidłowe użycie dynamicznego SQL (EXECUTE)

Dynamiczny SQL, jeśli używasz go bez głowy, może być źródłem niezłego bólu głowy. Najczęstszy błąd — SQL injection. Na przykład:

EXECUTE 'SELECT * FROM orders WHERE id = ' || user_input;

Jeśli user_input zawiera coś w stylu 1; DROP TABLE orders;, to możesz się pożegnać z tabelą orders.

Jak tego uniknąć? Używaj zapytań przygotowanych:

EXECUTE 'SELECT * FROM orders WHERE id = $1' USING user_input;

Taki sposób ochroni Twoją apkę przed SQL injection.

Cofnięcie transakcji po złej obsłudze błędów

Jeśli błędy nie są dobrze obsłużone, transakcja może zostać w nieprawidłowym stanie. Na przykład:

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, 'Oczekujące');

BEGIN;
-- Jakaś operacja, która rzuca błąd
INSERT INTO non_existing_table VALUES (1);
-- Błąd, ale transakcja nie została zakończona

COMMIT; -- Błąd: bieżąca transakcja przerwana

Przez ten błąd cały kod się blokuje.

Jak tego uniknąć? Używaj bloków EXCEPTION do poprawnego cofania:

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, 'Oczekujące');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Wystąpił błąd, transakcja zostanie cofnięta.';
END;

Jak unikać błędów: tipy i rekomendacje

• Pisząc skomplikowaną procedurę, zawsze zacznij od pseudokodu. Rozpisz wszystkie kroki i potencjalne punkty błędów.
• Używaj SAVEPOINT do izolowanego cofania transakcji. Ale pamiętaj, żeby je zwalniać po użyciu.
• Unikaj długich transakcji — im dłuższa transakcja, tym większa szansa na blokady.
• Dla zagnieżdżonych wywołań procedur upewnij się, że zewnętrzny i wewnętrzny kontekst transakcji są dobrze zsynchronizowane.
• Zawsze sprawdzaj wydajność swoich procedur przez EXPLAIN ANALYZE.
• Loguj błędy do tabel albo plików tekstowych — to ułatwi debugowanie.

Przykłady błędów i ich naprawa

Przykład 1: Błąd przy wywołaniu zagnieżdżonej procedury

Kod z błędem:

BEGIN;

CALL process_order(5);

-- W środku process_order poleciał ROLLBACK
-- Cała transakcja staje się nieważna
COMMIT; -- Błąd

Poprawiony kod:

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- Cofnięcie tylko przy błędzie
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

Przykład 2: Problem Deadlock

Kod z błędem:

-- Transakcja A
BEGIN;
UPDATE orders SET status = 'Przetwarzanie' WHERE id = 1;
-- Czeka na `products`

-- Transakcja B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- Czeka na `orders`

Naprawa:

-- Oba zapytania wykonują się w tej samej kolejności:
-- Najpierw `products`, potem `orders`.
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'Przetwarzanie' WHERE id = 1;
COMMIT;

Te błędy pokazują, czemu praca z transakcjami wymaga uwagi i doświadczenia. Ale jak wiadomo — im więcej praktyki, tym mniejsza szansa na ROLLBACK w prawdziwym życiu (i karierze).