Diversa gestione degli errori predefinita in Oracle e PostgreSQL

Question

Confronto il comportamento predefinito di Oracle e PostgreSQL dopo aver riscontrato un errore in un codice PL / SQL (PL / pgSQL). A tal fine, ho scritto un analogo codice Oracle e PostgreSQL mostrato di seguito.

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

Codice 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: in PostgreSQL eseguo anche l' BEGIN TRANSACTIONistruzione per disabilitare l'auto-commit, perché Oracle non ha l'auto-commit e voglio che entrambi i codici siano analoghi.

Il risultato della SELECT * FROM table1query è una riga in Oracle e nessuna riga in PostgreSQL.

Come puoi vedere, il codice analogo in Oracle e PostgreSQL fornisce risultati diversi. Qual è il motivo di questa differenza nella gestione degli errori predefinita?

Answer 1

Oracle e PostgreSQL si comportano effettivamente in modo diverso qui.

Oracle ha qualcosa che chiamerei "rollback a livello di istruzione": se un'istruzione in esecuzione all'interno di una transazione causa un errore, vengono annullati solo gli effetti di tale istruzione e la transazione continua.

In PostgreSQL, qualsiasi errore all'interno di una transazione interrompe l'intera transazione, quindi puoi solo eseguire il rollback della transazione e non ha alcun effetto. Questo è più nello spirito di "tutto o niente", ma per quanto posso vedere, lo standard SQL non è specifico su questo, quindi entrambi i comportamenti possono essere discussi.

È tuttavia possibile utilizzare punti di salvataggio conformi agli standard in PostgreSQL per "recuperare" da un errore in una transazione:

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;

Ma tieni presente che non usi troppi punti di salvataggio (non più di 64) per transazione, altrimenti le prestazioni potrebbero risentirne.

Answer 2

In Oracle stai utilizzando due transazioni separate, la prima ha esito positivo ma la seconda non riesce. In PostgreSQL, gli dici esplicitamente di usare solo una transazione e di gestire le istruzioni insieme.

In Oracle, se utilizzi un blocco anonimo PL / SQL per raggruppare le istruzioni in una singola transazione:

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

E, equivalentemente in PostgreSQL:

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

Quindi non ci saranno righe nella tabella poiché l'eccezione dalla procedura eseguirà il rollback dell'intera transazione.

---

Oppure, in Oracle, potresti fare:

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

Il che avrebbe lo stesso effetto di ripristinare entrambe le transazioni all'ultimo commit.

db <> violino Oracle PostgreSQL