Gestion des erreurs par défaut différente dans Oracle et PostgreSQL

Dec 17 2020

Je compare le comportement par défaut d'Oracle et de PostgreSQL après avoir rencontré une erreur dans un code PL / SQL (PL / pgSQL). À cette fin, j'ai écrit un code Oracle et PostgreSQL analogue ci-dessous.

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

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

Remarque: Dans PostgreSQL, j'exécute également l' BEGIN TRANSACTIONinstruction pour désactiver la validation automatique, car Oracle n'a pas de validation automatique et je veux que les deux codes soient analogues.

Le résultat de la SELECT * FROM table1requête est une ligne dans Oracle et aucune ligne dans PostgreSQL.

Comme vous pouvez le voir, le code analogue dans Oracle et PostgreSQL donne des résultats différents. Quelle est la raison de cette différence dans la gestion des erreurs par défaut?

Réponses

2 LaurenzAlbe Dec 17 2020 at 21:40

Oracle et PostgreSQL se comportent en effet différemment ici.

Oracle a quelque chose que j'appellerais «rollback au niveau de l'instruction»: si une instruction exécutée à l'intérieur d'une transaction provoque une erreur, seuls les effets de cette instruction sont annulés et la transaction continue.

Dans PostgreSQL, toute erreur dans une transaction annule toute la transaction, vous ne pouvez donc que revenir en arrière, et cela n'a aucun effet. C'est plus dans l'esprit du «tout ou rien», mais pour autant que je sache, le standard SQL n'est pas spécifique à ce sujet, donc les deux comportements peuvent être discutés.

Vous pouvez cependant utiliser des points de sauvegarde conformes standard dans PostgreSQL pour «récupérer» une erreur dans une transaction:

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;

Mais sachez que vous n'utilisez pas trop de points de sauvegarde (pas plus de 64) par transaction, sinon les performances peuvent en souffrir.

1 MT0 Dec 17 2020 at 21:24

Dans Oracle, vous utilisez deux transactions distinctes, la première réussit mais la seconde échoue. Dans PostgreSQL, vous lui dites explicitement de n'utiliser qu'une seule transaction et de gérer les instructions ensemble.

Dans Oracle, si vous utilisez un bloc anonyme PL / SQL pour regrouper les instructions en une seule transaction:

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

Et, de manière équivalente dans PostgreSQL:

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

Il n'y aura alors aucune ligne dans la table car l'exception de la procédure annulera la transaction entière.


Ou, dans Oracle, vous pouvez faire:

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

Ce qui aurait le même effet de restaurer les deux transactions au dernier commit.

db <> violon d' Oracle PostgreSQL