Tratamento de erros padrão diferente no Oracle e PostgreSQL
Eu comparo o comportamento padrão do Oracle e do PostgreSQL depois de encontrar um erro em um código PL / SQL (PL / pgSQL). Para este propósito, escrevi um código Oracle e PostgreSQL análogo mostrado abaixo.
Código Oracle ( 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;
Código PostgreSQL ( 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;
Nota: No PostgreSQL, adicionalmente, executo a BEGIN TRANSACTION
instrução para desabilitar o auto-commit, porque o Oracle não tem auto-commit, e eu quero que os dois códigos sejam análogos.
O resultado da SELECT * FROM table1
consulta é uma linha no Oracle e nenhuma linha no PostgreSQL.
Como você pode ver, o código análogo no Oracle e no PostgreSQL fornece resultados diferentes. Qual é a razão dessa diferença no tratamento de erros padrão?
Respostas
Oracle e PostgreSQL realmente se comportam de maneira diferente aqui.
O Oracle tem algo que eu chamaria de “reversão no nível da instrução”: se uma instrução em execução dentro de uma transação causar um erro, apenas os efeitos dessa instrução serão revertidos e a transação continuará.
No PostgreSQL, qualquer erro dentro de uma transação aborta toda a transação, então você só pode reverter a transação e não tem nenhum efeito. Isso está mais no espírito de “tudo ou nada”, mas até onde eu posso ver, o padrão SQL não é específico sobre isso, então ambos os comportamentos podem ser discutidos.
Você pode, no entanto, usar pontos de salvamento em conformidade com o padrão no PostgreSQL para “recuperar” de um erro em uma transação:
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;
Mas esteja avisado que você não usa muitos pontos de salvamento (não mais que 64) por transação, caso contrário, o desempenho pode ser prejudicado.
No Oracle, você está usando duas transações separadas, a primeira é bem-sucedida, mas a segunda falha. No PostgreSQL, você está explicitamente dizendo a ele para usar apenas uma transação e tratar as instruções juntas.
No Oracle, se você usar um bloco anônimo PL / SQL para agrupar as instruções em uma única transação:
BEGIN
INSERT INTO table1 VALUES (1);
raise_error();
END;
/
E, de forma equivalente no PostgreSQL:
DO
$$ BEGIN INSERT INTO table1 VALUES (1); CALL raise_error(); END; $$ LANGUAGE plpgsql;
Então, não haverá nenhuma linha na tabela, pois a exceção do procedimento reverterá toda a transação.
Ou, no Oracle, você pode fazer:
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;
/
O que teria o mesmo efeito de reverter ambas as transações para o último commit.
db <> fiddle Oracle PostgreSQL