Interception des erreurs

Cette rubrique décrit comment Amazon Redshift gère les erreurs.

Lorsqu’une requête ou une commande dans une procédure stockée provoque une erreur, les requêtes suivantes ne s’exécutent pas et la transaction est annulée. Mais vous pouvez gérer les erreurs à l'aide d'un EXCEPTION bloc.

[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
EXCEPTION
  WHEN OTHERS THEN
    statements
END;   

Lorsqu'une exception se produit et que vous ajoutez un bloc de gestion des exceptions, vous pouvez écrire des RAISE instructions et la plupart des autres instructions SQL PL/Pg. Par exemple, vous pouvez générer une exception avec un message personnalisé ou insérer un enregistrement dans une table de journalisation.

Lorsque vous saisissez le bloc de gestion des exceptions, la transaction actuelle est annulée et une nouvelle transaction est créée pour exécuter les instructions dans le bloc. Si les instructions du bloc s’exécutent sans erreur, la transaction est validée et l’exception est renvoyée. Enfin, la procédure stockée se ferme.

La seule condition prise en charge dans un bloc d'exception est celle qui correspond à tous les types d'erreurOTHERS, à l'exception de l'annulation de la requête. De plus, si une erreur se produit dans un bloc de gestion des exceptions, elle peut être interceptée par un bloc de gestion des exceptions externe.

Lorsqu'une erreur se produit dans la NONATOMIC procédure, elle n'est pas renvoyée si elle est gérée par un bloc d'exceptions. Consultez l'SQLinstruction PL/Pg RAISE pour lancer une exception interceptée par le bloc de gestion des exceptions. Cette déclaration n’est valable que dans les blocs de traitement des exceptions. Pour plus d'informations, voir RAISE.

Contrôler ce qui se passe après une erreur dans une procédure stockée, avec le CONTINUE gestionnaire

Le CONTINUE gestionnaire est un type de gestionnaire d'exceptions qui contrôle le flux d'exécution au sein d'une procédure NONATOMIC stockée. En l’utilisant, vous pouvez intercepter et gérer les exceptions sans mettre fin au bloc d’instructions existant. Normalement, lorsqu’une erreur se produit dans une procédure stockée, le flux est interrompu et l’erreur est renvoyée à l’appelant. Cependant, dans certains cas d’utilisation, la condition d’erreur n’est pas suffisamment grave pour justifier l’interruption du flux. Vous souhaiterez peut-être gérer l’erreur de façon fluide, en utilisant la logique de gestion des erreurs de votre choix dans une transaction distincte, puis continuer à exécuter les instructions qui suivent l’erreur. L’exemple suivant montre la syntaxe.

[ DECLARE
  declarations ]
BEGIN
  statements
EXCEPTION
  [ CONTINUE_HANDLER | EXIT_HANDLER ] WHEN OTHERS THEN
    handler_statements
END;   

Plusieurs tables système sont disponibles pour vous aider à recueillir des informations sur les différents types d’erreurs. Pour plus d’informations, consultez STL_LOAD_ERRORS, STL_ERROR et SYS_STREAM_SCAN_ERRORS. Il existe également des tables système supplémentaires que vous pouvez utiliser pour résoudre les erreurs. Pour plus d’informations, consultez Informations de référence sur les tables et les vues système.

Exemple

L’exemple suivant montre comment écrire des instructions dans le bloc de gestion des exceptions. La procédure stockée utilise le comportement de gestion des transactions par défaut.

CREATE TABLE employee (firstname varchar, lastname varchar);
INSERT INTO employee VALUES ('Tomas','Smith');
CREATE TABLE employee_error_log (message varchar);

CREATE OR REPLACE PROCEDURE update_employee_sp() AS
$$
BEGIN
    UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
    EXECUTE 'select invalid';
EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'An exception occurred.';
    INSERT INTO employee_error_log VALUES ('Error message: ' || SQLERRM);
END;
$$ 
LANGUAGE plpgsql;

CALL update_employee_sp();

INFO:  An exception occurred.
ERROR:  column "invalid" does not exist
CONTEXT:  SQL statement "select invalid"
PL/pgSQL function "update_employee_sp" line 3 at execute statement

Dans cet exemple, si nous appelons update_employee_sp, le message d’information An exception occurred (Une exception s’est produite) est déclenché et le message d’erreur est généré dans le journal employee_error_log de la table de journalisation. L’exception d’origine est renvoyée avant la fin de la procédure stockée. Les requêtes suivantes présentent les enregistrements résultant de l’exécution de l’exemple.

SELECT * from employee;

firstname | lastname 
-----------+----------
 Tomas     | Smith

SELECT * from employee_error_log;

          message                     
------------------------------------------------
 Error message: column "invalid" does not exist

Pour plus d'informationsRAISE, notamment sur l'aide au formatage et une liste de niveaux supplémentaires, consultezInstructions PL/pgSQL prises en charge.

L’exemple suivant montre comment écrire des instructions dans le bloc de gestion des exceptions. La procédure stockée utilise le comportement de gestion des NONATOMIC transactions. Dans cet exemple, aucune erreur n’est renvoyée à l’appelant à la fin de l’appel de la procédure. L'UPDATEinstruction n'est pas annulée en raison d'une erreur dans l'instruction suivante. Le message d’information est affiché et le message d’erreur est inséré dans la table de journalisation.

CREATE TABLE employee (firstname varchar, lastname varchar); 
INSERT INTO employee VALUES ('Tomas','Smith'); 
CREATE TABLE employee_error_log (message varchar);

-- Create the SP in NONATOMIC mode
CREATE OR REPLACE PROCEDURE update_employee_sp_2() NONATOMIC AS
$$
BEGIN
    UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
    EXECUTE 'select invalid';
EXCEPTION WHEN OTHERS THEN
    RAISE INFO 'An exception occurred.';
    INSERT INTO employee_error_log VALUES ('Error message: ' || SQLERRM);
END;
$$ 
LANGUAGE plpgsql;

CALL update_employee_sp_2();
INFO:  An exception occurred.
CALL

SELECT * from employee;

 firstname | lastname 
-----------+----------
 Adam      | Smith
(1 row)

SELECT * from employee_error_log;

                    message                     
------------------------------------------------
 Error message: column "invalid" does not exist
(1 row)

Cet exemple montre comment créer une procédure avec deux sous-blocs. Lorsque la procédure stockée est appelée, l’erreur du premier sous-bloc est traitée par son bloc de gestion des exceptions. Une fois le premier sous-bloc terminé, la procédure continue d’exécuter le deuxième sous-bloc. Vous pouvez voir dans le résultat qu’aucune erreur n’est déclenchée à la fin de l’appel de la procédure. Les employés UPDATE chargés des INSERT opérations sur la table sont engagés. Les messages d’erreur des deux blocs d’exception sont insérés dans la table de journalisation.

CREATE TABLE employee (firstname varchar, lastname varchar); 
INSERT INTO employee VALUES ('Tomas','Smith'); 
CREATE TABLE employee_error_log (message varchar);

CREATE OR REPLACE PROCEDURE update_employee_sp_3() NONATOMIC AS
$$
BEGIN
    BEGIN
        UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
        EXECUTE 'select invalid1';
    EXCEPTION WHEN OTHERS THEN
        RAISE INFO 'An exception occurred in the first block.';
        INSERT INTO employee_error_log VALUES ('Error message: ' || SQLERRM);
    END;
    BEGIN
        INSERT INTO employee VALUES ('Edie','Robertson');
        EXECUTE 'select invalid2';
    EXCEPTION WHEN OTHERS THEN
        RAISE INFO 'An exception occurred in the second block.';
        INSERT INTO employee_error_log VALUES ('Error message: ' || SQLERRM);
    END;
END;
$$ 
LANGUAGE plpgsql;

CALL update_employee_sp_3();
INFO:  An exception occurred in the first block.
INFO:  An exception occurred in the second block.
CALL

SELECT * from employee;

 firstname | lastname  
-----------+-----------
 Adam      | Smith
 Edie      | Robertson
(2 rows)

SELECT * from employee_error_log;

                     message                     
-------------------------------------------------
 Error message: column "invalid1" does not exist
 Error message: column "invalid2" does not exist
(2 rows)

L'exemple suivant montre comment utiliser le gestionnaire d'CONTINUEexceptions. Cet exemple crée deux tables et les utilise dans une procédure stockée. Le CONTINUE gestionnaire contrôle le flux d'exécution dans une procédure stockée avec un comportement de gestion des NONATOMIC transactions.

CREATE TABLE tbl_1 (a int);
CREATE TABLE tbl_error_logging(info varchar, err_state varchar, err_msg varchar);

CREATE OR REPLACE PROCEDURE sp_exc_handling_1() NONATOMIC AS
$$
BEGIN
    INSERT INTO tbl_1 VALUES (1);
    -- Expect an error for the insert statement following, because of the invalid value
    INSERT INTO tbl_1 VALUES ("val");
    INSERT INTO tbl_1 VALUES (2);
EXCEPTION CONTINUE_HANDLER WHEN OTHERS THEN
    INSERT INTO tbl_error_logging VALUES ('Encountered error', SQLSTATE, SQLERRM);
END;
$$ LANGUAGE plpgsql;

Appelez la procédure stockée :

CALL sp_exc_handling_1();

Le flux se déroule comme suit :

Une fois que vous avez appelé l’exemple de procédure stockée, les tables contiennent les enregistrements suivants :

L'exemple supplémentaire de gestion des erreurs suivant utilise à la fois un EXIT gestionnaire et un gestionnaire. CONTINUE Il crée deux tables : une table de données et une table de journalisation. Il crée également une procédure stockée qui illustre la gestion des erreurs :

CREATE TABLE tbl_1 (a int);
CREATE TABLE tbl_error_logging(info varchar, err_state varchar, err_msg varchar);

CREATE OR REPLACE PROCEDURE sp_exc_handling_2() NONATOMIC AS
$$
BEGIN
    INSERT INTO tbl_1 VALUES (1);
    BEGIN
        INSERT INTO tbl_1 VALUES (100);
        -- Expect an error for the insert statement following, because of the invalid value
        INSERT INTO tbl_1 VALUES ("val");
        INSERT INTO tbl_1 VALUES (101);
    EXCEPTION EXIT_HANDLER WHEN OTHERS THEN
        INSERT INTO tbl_error_logging VALUES ('Encountered error', SQLSTATE, SQLERRM);
    END;
    INSERT INTO tbl_1 VALUES (2);
    -- Expect an error for the insert statement following, because of the invalid value
    INSERT INTO tbl_1 VALUES ("val");
    INSERT INTO tbl_1 VALUES (3);
EXCEPTION CONTINUE_HANDLER WHEN OTHERS THEN
    INSERT INTO tbl_error_logging VALUES ('Encountered error', SQLSTATE, SQLERRM);
END;
$$ LANGUAGE plpgsql;

Après avoir créé la procédure stockée, appelez-la comme suit :

CALL sp_exc_handling_2();

Lorsqu'une erreur se produit dans le bloc d'exception interne, qui est placé entre crochets par l'ensemble interne de BEGIN etEND, elle est gérée par le EXIT gestionnaire. Toutes les erreurs survenant dans le bloc extérieur sont gérées par le CONTINUE gestionnaire.

Une fois que vous avez appelé l’exemple de procédure stockée, les tables contiennent les enregistrements suivants :

Si la table tbl_error_logging n’existe pas, elle déclenche une exception.

L'exemple suivant montre comment utiliser le gestionnaire d'CONTINUEexceptions avec la FOR boucle. Cet exemple crée trois tables et les utilise en FOR boucle au sein d'une procédure stockée. La FOR boucle est une variante du jeu de résultats, ce qui signifie qu'elle itère sur les résultats d'une requête :

CREATE TABLE tbl_1 (a int);
INSERT INTO tbl_1 VALUES (1), (2), (3);
CREATE TABLE tbl_2 (a int);
CREATE TABLE tbl_error_logging(info varchar, err_state varchar, err_msg varchar);

CREATE OR REPLACE PROCEDURE sp_exc_handling_loop() NONATOMIC AS
$$
DECLARE
 rec RECORD;
BEGIN
    FOR rec IN SELECT a FROM tbl_1
    LOOP
        IF rec.a = 2 THEN
            -- Expect an error for the insert statement following, because of the invalid value
            INSERT INTO tbl_2 VALUES("val");
        ELSE
            INSERT INTO tbl_2 VALUES (rec.a);
        END IF;
    END LOOP;
EXCEPTION CONTINUE_HANDLER WHEN OTHERS THEN
    INSERT INTO tbl_error_logging VALUES ('Encountered error', SQLSTATE, SQLERRM);
END;
$$ LANGUAGE plpgsql;

Appelez la procédure stockée :

CALL sp_exc_handling_loop();

Une fois que vous avez appelé l’exemple de procédure stockée, les tables contiennent les enregistrements suivants :

Remarques d'utilisation concernant le CONTINUE gestionnaire :