Различное поведение по умолчанию при обработке исключений в транзакции

Question

Сравниваю поведение Oracle и PostgreSQL по умолчанию после обнаружения ошибки в коде PL/SQL и PL/pgSQL. Для этого написал аналогичный код Oracle и PostgreSQL.

Oracle code (db<>fiddle):

CREATE TABLE table1 (col1 int);

CREATE PROCEDURE raise_error AS
BEGIN
  INSERT INTO table1 VALUES (1/0);
END;
/

INSERT INTO table1 VALUES (1);
CALL raise_error();
COMMIT;
SELECT * FROM table1;

PostgreSQL code (db<>fiddle):

CREATE TABLE table1 (col1 int);

CREATE PROCEDURE raise_error() AS $$
BEGIN
  INSERT INTO table1 VALUES (1/0);
END;
$$ LANGUAGE plpgsql;

BEGIN TRANSACTION; -- disable auto-commit

INSERT INTO table1 VALUES (1);
CALL raise_error();
COMMIT;
SELECT * FROM table1;

Заметьте, в PostgreSQL дополнительно запускаю BEGIN TRANSACTION, чтобы отключить автокоммит, потому что у Oracle нет автокоммита, и этим хочу, чтобы оба кода были аналогичны.

Результатом последнего запроса является одна строка в Oracle, и ни одной строки в PostgreSQL. То есть, аналогичный код в Oracle и PostgreSQL даёт разные результаты.

В чем причина такой разницы в обработке ошибок по умолчанию?

_{Свободный перевод вопроса Different default error handling in Oracle and PostgreSQL от участника @iwis}

Answer 1

Oracle и PostgreSQL действительно ведут себя здесь по-разному.

В Oracle есть Statement-Level Atomicity. Eсли запрос на изменение, запущенный внутри транзакции, приводит к ошибке, то только изменения этого запроса откатываются не затрагивая изменения произведённые ранее, и транзакции продолжается.

В PostgreSQL любая ошибка внутри транзакции откатывает всю транзакцию, так что последующий откат транзакции не имеет никакого эффекта. Это скорее в духе - всё или ничего.

Но, насколько я могу судить, стандарт SQL не является специфическим в отношении обоих моделей поведения.

Однако, можно использовать соответствующие стандарту точки сохранения в PostgreSQL, чтобы откатить только изменения одного запроса с ошибкой в транзакции (на db<>fiddle):

START TRANSACTION;

INSERT INTO table1 VALUES (1);

SAVEPOINT x;

CALL raise_error();

ROLLBACK TO SAVEPOINT x;

COMMIT;

_{Свободный перевод ответа от участника Laurenz Albe}

Answer 2

В Oracle БД атомарность на уровне запроса Statement-Level Atomicity действует для всех DML. Откат в случае ошибки действует только для изменений в текущем DML запросе (statement-level-rollback)

В PL/SQL отдельные запросы на изменение группируются в одну исполняемую единицу. Атомарность на уровне запроса действует для самого внешнего блока BEGIN...END;, то есть до блока, на котором PL/SQL интерпретатор завершает работу.

Eсли во внутреннем блоке будет вызвано исключение и оно не будет обработано в текущем блоке, то оно будет передаваться во внешний блок, и так далее до самого внешнего блока. Если и в самом внешнем блоке исключение не будет обработано, то срабатывает неявный откат всех изменений, которые произошли в этом блоке. Исключение возвращяется клиенту, который решает, что делать с изменениями произведёнными до вызова PL/SQL блока - откат или коммит.

insert into table1 values (1);

1 row created.

begin
    insert into table1 values (2);
    begin 
        insert into table1 values (3);
        raise_error();
    end;
exception when others then
    for r in (select * from table1) loop
        dbms_output.put_line ('table1.col1='||r.col1); 
    end loop;
    raise;
end;
/
table1.col1=1
table1.col1=2
table1.col1=3

begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 11

select * from table1;

      COL1
----------
         1

Обратите внимание, в обработчике самого внешнего блока все изменения ещё видны, пока вызов RAISE не делает текущее исключение вновь необработаным. Затем, видны только изменения произведённые до вызова BEGIN...END;.

Неявный откат PL/SQL блока нельзя путать с явным ROLLBACK внутри него, который завершит текущую транзакцию с откатом всех изменений в ней:

rollback;
insert into table1 values (1);

1 row created.

begin
    insert into table1 values (2);
    begin
        insert into table1 values (3);
        rollback;
    end;
end;
/
PL/SQL procedure successfully completed.

select * from table1;

no rows selected

БЛОГ НА HUSL

Различное поведение по умолчанию при обработке исключений в транзакции

Ответы (2 шт):