Управление транзакциями

В процедурах, вызываемых командой CALL, а также в блоках анонимного кода (команда DO), можно завершать транзакции с помощью команд COMMIT и ROLLBACK. После завершения транзакции с этими командами новая транзакция запускается автоматически, поэтому отдельной команды START TRANSACTION нет. (Обратите внимание, что в PL/pgSQL BEGIN и END имеют другой смысл.)

Простой пример:

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END
$$;

CALL transaction_test1();

Новая транзакция начинается с характеристик по умолчанию, таких как уровень изоляции. В случаях, когда транзакции фиксируются в цикле, может быть удобнее автоматически начинать новую транзакцию с теми же характеристиками, что и предыдущая. Это реализуется командами COMMIT AND CHAIN и ROLLBACK AND CHAIN.

Управление транзакциями возможно только в вызовах CALL или DO из верхнего уровня или во вложенных CALL или DO без какой-либо промежуточной команды. Например, если стеком вызовов является CALL proc1() → CALL proc2() → CALL proc3(), то вторая и третья процедуры могут выполнять действия по управлению транзакциями. Но если стеком вызовов является CALL proc1() → SELECT func2() → CALL proc3(), то последняя процедура не может осуществлять управление транзакциями из-за промежуточного SELECT.

Циклы с курсорами требуют особого внимания. Рассмотрим этот пример:

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        COMMIT;
    END LOOP;
END;
$$;

CALL transaction_test2();

Обычно курсоры автоматически закрываются при фиксации транзакции. Однако курсор, созданный как часть цикла, подобного этому, автоматически преобразуется в удерживаемый курсор с помощью первой COMMIT или ROLLBACK. Это означает, что курсор полностью вычисляется в первой COMMIT или ROLLBACK, а не построчно. Курсор по-прежнему автоматически удаляется после цикла, поэтому в большинстве случаев невидим для пользователя.

Команды транзакций не допускаются в циклах с курсорами, управляемых командами, которые предназначены не только для чтения (например, UPDATE ... RETURNING).

Транзакция не может завершаться внутри блока с обработчиками исключений.