Procedury składowane w PostgreSQL/Transakcje

Każde wejście do procedury oraz bloku kodu z obsługą wyjątków powoduje niejawne utworzenie transakcji. Jednocześnie w kodzie procedury zabronione jest bezpośrednie wykonywanie instrukcji związanych z transakcjami, tj. COMMIT, ROLLBACK, SAVEPOINT itd.

Wyjście z procedury/bloku bez błędów zatwierdza tę transakcję, podniesienie wyjątku jest równoważne wycofaniu.

W środowisku z dużą liczbą użytkowników użycie procedur może powodować spadek wydajności.

Przykłady

Demonstracja tej własności. Zostaną utworzone trzy procedury, które kasują dane z pewnej tabeli.

CREATE OR REPLACE FUNCTION test1() RETURNS void AS $$
    BEGIN
        DELETE FROM test where x % 2 = 0; -- usunięcie połowy wierszy
        DELETE FROM test;
    END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION test2() RETURNS void AS $$
    BEGIN
        DELETE FROM test where x % 2 = 0;
        RAISE 'wyjątek';
        DELETE FROM test;
    END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION test3() RETURNS void AS $$
    BEGIN
        DELETE FROM test where x % 2 = 0;

        <<rollback_to>>
        BEGIN
            DELETE FROM test;
            RAISE 'wyjątek';
        EXCEPTION
            WHEN OTHERS THEN
                -- zignorowanie wyjątku
                NULL;
        END;
    END;
$$ LANGUAGE 'plpgsql';

Dla każdej została wykonana następująca sekwencja instrukcji:

CREATE TABLE test (x integer);

-- wstawienie od test 1000 wierszy
INSERT INTO test (SELECT * FROM generate_series(1, 1000));

SELECT test1(); -- test1, test2, test3
SELECT count(*) FROM test; -- sprawdzenie liczby wierszy

Liczba wierszy po wykonaniu funkcji test1 wynosi 0 - zatwierdzone zostały obie instrukcje DELETE.
Liczba wierszy po wykonaniu funkcji test2 wynosi 1000 - po wystąpieniu wyjątku transakcja została wycofana.
Liczba wierszy po wykonaniu funkcji test3 wynosi 500 - po wystąpieniu wyjątku została wycofana transakcja w bloku rollback_to, natomiast zatwierdzone zostały zmiany po pierwszej instrukcji DELETE.