Gestion des erreurs par défaut différente dans Oracle et PostgreSQL
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 TRANSACTION
instruction 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 table1
requê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
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.
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