Различная обработка ошибок по умолчанию в Oracle и PostgreSQL
Я сравниваю поведение 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 дает разные результаты. В чем причина такой разницы в обработке ошибок по умолчанию?
Ответы
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) на транзакцию, иначе производительность может пострадать.
В 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