Tratamento de erros padrão diferente no Oracle e PostgreSQL

Dec 17 2020

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 TRANSACTIONinstruçã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 table1consulta é 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

2 LaurenzAlbe Dec 17 2020 at 21:40

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.

1 MT0 Dec 17 2020 at 21:24

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