Различная обработка ошибок по умолчанию в Oracle и PostgreSQL

Dec 17 2020

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

Код Oracle ( db <> скрипка ):

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 ( db <> скрипка ):

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 не имеет автоматической фиксации, и я хочу, чтобы оба кода были аналогичными.

Результатом SELECT * FROM table1запроса является одна строка в Oracle и ни одна строка в PostgreSQL.

Как видите, аналогичный код в Oracle и PostgreSQL дает разные результаты. В чем причина такой разницы в обработке ошибок по умолчанию?

Ответы

2 LaurenzAlbe Dec 17 2020 at 21:40

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

В Oracle есть кое-что, что я бы назвал «откатом на уровне оператора»: если оператор, выполняемый внутри транзакции, вызывает ошибку, откатываются только результаты этого оператора, и транзакция продолжается.

В PostgreSQL любая ошибка внутри транзакции прерывает всю транзакцию, поэтому вы можете только откатить транзакцию, и это не имеет никакого эффекта. Это больше в духе «все или ничего», но, насколько я понимаю, стандарт SQL не конкретизирует это, так что оба поведения можно оспаривать.

Однако вы можете использовать стандартные соответствующие точки сохранения в PostgreSQL для «восстановления» после ошибки в транзакции:

START TRANSACTION;

INSERT INTO table1 VALUES (1);

/* set a savepoint we can revert to */
SAVEPOINT x;

CALL raise_error();

ROLLBACK TO SAVEPOINT x;

/* now the INSERT can be committed */
COMMIT;

Но имейте в виду, что вы не используете слишком много точек сохранения (не более 64) на транзакцию, иначе производительность может пострадать.

1 MT0 Dec 17 2020 at 21:24

В Oracle вы используете две отдельные транзакции, первая успешна, а вторая - нет. В PostgreSQL вы явно указываете ему использовать только одну транзакцию и обрабатывать операторы вместе.

В Oracle, если вы используете анонимный блок PL / SQL для группировки операторов в одну транзакцию:

BEGIN
  INSERT INTO table1 VALUES (1);
  raise_error();
END;
/

И то же самое в PostgreSQL:

DO
$$ BEGIN INSERT INTO table1 VALUES (1); CALL raise_error(); END; $$ LANGUAGE plpgsql;

Тогда в таблице не будет строк, так как исключение из процедуры откатит всю транзакцию.


Или в Oracle вы можете сделать:

INSERT INTO table1 VALUES (1);

DECLARE
  divide_by_zero EXCEPTION;
  PRAGMA EXCEPTION_INIT( divide_by_zero, -1476 );
BEGIN
  raise_error();
EXCEPTION
  WHEN DIVIDE_BY_ZERO THEN
    ROLLBACK;
END;
/

Что будет иметь такой же эффект отката обеих транзакций до последней фиксации.

db <> скрипт Oracle PostgreSQL