エラーのトラップ

このトピックでは、Amazon Redshift がエラーを処理する方法について説明します。

ストアドプロシージャのクエリまたはコマンドでエラーが発生すると、それ以降のクエリが実行されず、トランザクションがロールバックされます。しかし、 EXCEPTION ブロックを使用してエラーに対処できます。

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

例外が発生し、例外処理ブロックを追加すると、REASE ステートメントおよび他のほとんどの PL/pgSQL ステートメントを書き込みできます。たとえば、カスタムメッセージで例外を発生させたり、ロギングテーブルにレコードを挿入したりできます。

例外処理ブロックに入ると、現在のトランザクションがロールバックされ、新しいトランザクションがブロック内のステートメントを実行するために作成されます。ブロック内のステートメントがエラーなしで実行されると、トランザクションはコミットされ、例外が再スローされます。最後に、ストアドプロシージャが終了します。

例外ブロックでサポートされている条件は OTHERS のみです。この条件は、クエリのキャンセルを除いて、すべてのエラータイプに一致します。また、例外処理ブロックでエラーが発生すると、エラーは外部の例外処理ブロックでキャッチできます。

NONATOMIC プロシージャ内でエラーが発生しても、例外ブロックで処理されればエラーは再スローされません。例外処理ブロックでキャッチされた例外をスローする方法については、「PL/pgSQL ステートメント RAISE」を参照してください。このステートメントは例外処理ブロックでのみ有効です。詳細については、「RAISE」を参照してください。

CONTINUE ハンドラを使用して、ストアドプロシージャでエラーが発生した後の処理を制御する

CONTINUE ハンドラは、NONATOMIC ストアドプロシージャ内の実行フローを制御する例外ハンドラの一種です。これを使用すると、既存のステートメントブロックを終了せずに例外をキャッチして処理できます。通常、ストアドプロシージャでエラーが発生すると、フローが中断され、エラーが呼び出し元に返されます。ただし、ユースケースによっては、フローの中断を必要とするほどエラー状態が深刻ではない場合があります。別のトランザクションで選択したエラー処理ロジックを使用してエラーをスムーズに処理し、エラーの後にステートメントを実行し続けることもできます。以下にその構文を示します。

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

さまざまなタイプのエラーに関する情報を収集するのに役立つシステムテーブルがいくつかあります。詳細については、STL_LOAD_ERRORS、STL_ERROR、およびSYS_STREAM_SCAN_ERRORSを参照してください。エラーのトラブルシューティングに使用できるその他のシステムテーブルもあります。これらに関する情報は、「システムテーブルとビューのリファレンス」を参照してください。

例

次の例は、例外処理ブロックでステートメントを書き込みする方法を示しています。ストアドプロシージャはデフォルトのトランザクション管理動作を使用しています。

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

この例では、update_employee_spを呼び出すと、情報メッセージ例外が発生しました。が生成され、エラーメッセージがロギングテーブルのemployee_error_logログに挿入されます。元の例外は、ストアドプロシージャが終了する前に再度スローされます。次のクエリは、この例を実行した結果のレコードを示しています。

SELECT * from employee;

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

SELECT * from employee_error_log;

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

フォーマットのヘルプや追加レベルのリストなど、RAISE の詳細については、「サポートされている PL/pgSQL ステートメント」を参照してください。

次の例は、例外処理ブロックでステートメントを書き込みする方法を示しています。ストアドプロシージャは NONATOMIC トランザクション管理動作を使用しています。この例では、プロシージャコールが完了しても呼び出し元にエラーは返されません。次のステートメントのエラーのため、UPDATE ステートメントはロールバックされません。情報メッセージが表示され、エラーメッセージがロギングテーブルに挿入されます。

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)

この例では、2 つのサブブロックを持つプロシージャを作成する方法を示します。ストアドプロシージャが呼び出されると、最初のサブブロックからのエラーはその例外処理ブロックによって処理されます。最初のサブブロックが完了すると、プロシージャは引き続き 2 番目のサブブロックを実行します。結果から、プロシージャコールが完了してもエラーは発生しないことがわかります。テーブル従業員の UPDATE オペレーションと INSERT オペレーションがコミットされます。両方の例外ブロックからのエラーメッセージがログテーブルに挿入されます。

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)

次の例は、CONTINUE 例外ハンドラの使用方法を示しています。このサンプルでは 2 つのテーブルを作成し、ストアドプロシージャで使用します。CONTINUE ハンドラは、NONATOMIC トランザクション管理動作でストアドプロシージャの実行フローを制御します。

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;

ストアドプロシージャを呼び出します。

CALL sp_exc_handling_1();

フローは以下のように進行します。

サンプルストアドプロシージャを呼び出すと、テーブルに次のレコードが格納されます。

以下の追加のエラー処理例では、EXIT ハンドラと CONTINUE ハンドラの両方を使用しています。データテーブルとロギングテーブルの 2 つのテーブルを作成します。また、エラー処理を示すストアドプロシージャも作成します。

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;

ストアドプロシージャを作成したら、次のように呼び出します。

CALL sp_exc_handling_2();

BEGIN と END の内部セットで囲まれた内部例外ブロックでエラーが発生すると、EXIT ハンドラによって処理されます。外側のブロックで発生したエラーは、CONTINUE ハンドラによって処理されます。

サンプルストアドプロシージャを呼び出すと、テーブルに次のレコードが格納されます。

tbl_error_logging テーブルが存在しない場合、例外が発生します。

次の例は、CONTINUE 例外ハンドラを FOR ループで使用する方法を示しています。このサンプルでは 3 つのテーブルを作成し、ストアドプロシージャ内の FOR ループで使用します。FOR ループは結果セットバリアントであり、クエリの結果を反復処理します。

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;

ストアドプロシージャを呼び出します。

CALL sp_exc_handling_loop();

サンプルストアドプロシージャを呼び出すと、テーブルに次のレコードが格納されます。

CONTINUE ハンドラに関する使用上の注意事項: