Diferente manejo de errores predeterminados en Oracle y PostgreSQL

Question

Comparo el comportamiento predeterminado de Oracle y PostgreSQL después de encontrar un error en un código PL / SQL (PL / pgSQL). Para este propósito, escribí un código análogo de Oracle y PostgreSQL que se muestra a continuación.

Código de 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: En PostgreSQL también ejecuto la BEGIN TRANSACTIONdeclaración para deshabilitar la confirmación automática, porque Oracle no tiene confirmación automática y quiero que ambos códigos sean análogos.

El resultado de la SELECT * FROM table1consulta es una fila en Oracle y ninguna fila en PostgreSQL.

Como puede ver, el código análogo en Oracle y PostgreSQL da resultados diferentes. ¿Cuál es la razón de esta diferencia en el manejo de errores predeterminado?

Answer 1

De hecho, Oracle y PostgreSQL se comportan de manera diferente aquí.

Oracle tiene algo que yo llamaría "reversión a nivel de declaración": si una declaración que se ejecuta dentro de una transacción provoca un error, solo se revierten los efectos de esa declaración y la transacción continúa.

En PostgreSQL, cualquier error dentro de una transacción aborta toda la transacción, por lo que solo puede revertir la transacción y no tiene ningún efecto. Esto es más en el espíritu de "todo o nada", pero por lo que puedo ver, el estándar SQL no es específico sobre esto, por lo que ambos comportamientos pueden ser discutidos.

Sin embargo, puede utilizar puntos de guardado conformes estándar en PostgreSQL para "recuperarse" de un error en una transacción:

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;

Pero tenga en cuenta que no usa demasiados puntos de guardado (no más de 64) por transacción, de lo contrario, el rendimiento puede verse afectado .

Answer 2

En Oracle, está utilizando dos transacciones separadas, la primera tiene éxito pero la segunda falla. En PostgreSQL, le está diciendo explícitamente que solo use una transacción y maneje las declaraciones juntas.

En Oracle, si usa un bloque anónimo PL / SQL para agrupar las declaraciones en una sola transacción:

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

Y, de manera equivalente en PostgreSQL:

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

Entonces no habrá filas en la tabla ya que la excepción del procedimiento revertirá la transacción completa.

---

O, en Oracle, puede hacer:

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;
/

Lo que tendría el mismo efecto de retroceder ambas transacciones hasta la última confirmación.

db <> violín Oracle PostgreSQL