Aufspüren von Fehlern

Wenn eine Abfrage oder ein Befehl in einer gespeicherten Prozedur einen Fehler verursacht, werden nachfolgende Abfragen nicht ausgeführt und die Transaktion wird rückgängig gemacht. Sie können Fehler jedoch unter Verwendung eines AUSNAHMEBLOCKS beheben.

Anmerkung

Standardmäßig führt ein Fehler dazu, dass nachfolgende Abfragen nicht ausgeführt werden, auch wenn die gespeicherte Prozedur keine zusätzlichen Bedingungen enthält, die zu Fehlern führen.

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

Wenn eine Ausnahme auftritt und Sie einen Ausnahmebehandlungsblock hinzufügen, können Sie RAISE-Anweisungen und die meisten anderen PL/pgSQL-Anweisungen schreiben. Sie können beispielsweise eine Ausnahme mit einer benutzerdefinierten Nachricht auslösen oder einen Datensatz in eine Protokollierungstabelle einfügen.

Bei der Eingabe des Ausnahmebehandlungsblocks wird ein Rollback für die aktuelle Transaktion ausgeführt und eine neue Transaktion erstellt, um die Anweisungen im Block auszuführen. Wenn die Anweisungen im Block fehlerfrei ausgeführt werden, wird ein Commit der Transaktion ausgeführt und die Ausnahme wird erneut ausgelöst. Zuletzt wird die gespeicherte Prozedur beendet.

Die einige unterstützte Bedingung in einem Ausnahmeblock ist OTHERS, die auf jeden Fehlertyp außer Abfrageabbruch zutrifft. Tritt ein Fehler in einem Ausnahmehandhabungsblock auf, kann er auch von einem äußeren Ausnahmebehandlungsblock erfasst werden.

Ein Fehler innerhalb der NONATOMIC-Prozedur wird nicht erneut ausgelöst, wenn er durch einen Ausnahmeblock behandelt wird. Informationen zum Auslösen einer Ausnahme, die vom Ausnahmebehandlungsblock abgefangen wurde, siehe PL/pgSQL-Anweisung RAISE. Diese Anweisung ist nur in Ausnahmebehandlungsblöcken gültig. Weitere Informationen finden Sie unter RAISE.

Steuern, was nach einem Fehler in einer gespeicherten Prozedur geschieht, mit dem CONTINUE-Handler

Der CONTINUE-Handler ist eine Art Ausnahme-Handler, der den Ausführungsablauf innerhalb einer gespeicherten NONATOMIC-Prozedur steuert. Damit können Sie Ausnahmen erfassen und behandeln, ohne den vorhandenen Anweisungsblock zu beenden. Wenn in einer gespeicherten Prozedur ein Fehler auftritt, wird der Ablauf normalerweise unterbrochen und der Fehler wird an den Aufrufer zurückgegeben. In manchen Anwendungsfällen ist der Fehler jedoch nicht schwerwiegend genug, um eine Unterbrechung des Datenflusses zu rechtfertigen. Möglicherweise möchten Sie den Fehler sorgfältig behandeln, indem Sie eine Fehlerbehandlungslogik Ihrer Wahl in einer separaten Transaktion verwenden und dann weitere Anweisungen ausführen, die auf den Fehler folgen. Nachfolgend wird die Syntax dargestellt.

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

Es sind mehrere Systemtabellen verfügbar, die Informationen über verschiedene Arten von Fehlern bereitstellen. Weitere Informationen finden Sie unter STL_LOAD_ERRORS, STL_ERROR und SYS_STREAM_SCAN_ERRORS. Es gibt auch zusätzliche Systemtabellen, die Sie zur Fehlerbehebung verwenden können. Weitere Informationen dazu finden Sie unter Referenz zu Systemtabellen und Ansichten.

Beispiel

Das folgende Beispiel zeigt, wie Anweisungen im Ausnahmebehandlungsblock geschrieben werden. Die gespeicherte Prozedur verwendet das standardmäßige Transaktionsverwaltungsverhalten.

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

Wenn wir in diesem Beispiel update_employee_sp aufrufen, wird die Informationsmeldung An exception occurred. (Eine Ausnahme ist aufgetreten.) ausgelöst und die Fehlermeldung wird ins Protokoll employee_error_log der Protokollierungstabelle eingefügt. Die ursprüngliche Ausnahme wird erneut ausgelöst, bevor die gespeicherte Prozedur beendet wird. Die folgenden Abfragen zeigen Datensätze, die sich aus der Ausführung des Beispiels ergeben.

SELECT * from employee;

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

SELECT * from employee_error_log;

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

Weitere Informationen über RAISE, einschließlich Formatierungshilfe und einer Liste der zusätzlichen Ebenen, finden Sie unter Unterstützte PL/pgSQL-Anweisungen.

Das folgende Beispiel zeigt, wie Anweisungen im Ausnahmebehandlungsblock geschrieben werden. Die gespeicherte Prozedur verwendet das NONATOMIC-Transaktionsverwaltungsverhalten. In diesem Beispiel wird nach Abschluss des Prozeduraufrufs kein Fehler an den Aufrufer zurückgegeben. Die UPDATE-Anweisung wird aufgrund des Fehlers in der nächsten Anweisung nicht rückgängig gemacht. Die Informationsmeldung wird ausgelöst und die Fehlermeldung wird in die Protokollierungstabelle eingefügt.

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)

Dieses Beispiel veranschaulicht das Erstellen einer Prozedur mit zwei Unterblöcken. Wenn die gespeicherte Prozedur aufgerufen wird, wird der Fehler aus dem ersten Unterblock von ihrem Ausnahmebehandlungsblock behandelt. Nachdem der erste Unterblock abgeschlossen ist, fährt die Prozedur mit der Ausführung des zweiten Unterblocks fort. Aus dem Ergebnis geht vorher, dass nach Abschluss des Prozeduraufrufs kein Fehler ausgelöst wird. Die Operationen UPDATE und INSERT für die Tabelle employee wurden übernommen. Fehlermeldungen aus beiden Ausnahmeblöcken wurden in die Protokollierungstabelle eingefügt.

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)

Das folgende Beispiel demonstriert, wie Sie den CONTINUE-Ausnahme-Handler nutzen. In diesem Beispiel werden zwei Tabellen erstellt und in einer gespeicherten Prozedur verwendet. Der CONTINUE-Handler steuert den Ausführungsablauf in einer gespeicherten Prozedur mit dem Transaktionsverwaltungsverhalten 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;

Rufen Sie die gespeicherte Prozedur auf:

CALL sp_exc_handling_1();

Der Ablauf ist wie folgt:

1. Ein Fehler tritt auf, weil versucht wird, einen inkompatiblen Datentyp in eine Spalte einzufügen. Die Steuerung geht an den EXCEPTION-Block über. Bei der Eingabe des Ausnahmebehandlungsblocks wird ein Rollback für die aktuelle Transaktion ausgeführt und eine neue Transaktion erstellt, um die Anweisungen darin auszuführen.

2. Wenn die Anweisungen in CONTINUE_HANDLER ohne Fehler ausgeführt werden, geht die Steuerung an die Anweisung über, die unmittelbar auf die Anweisung folgt, die die Ausnahme verursacht hat. (Wenn eine Anweisung in CONTINUE_HANDLER eine neue Ausnahme auslöst, können Sie sie mit einer Ausnahmebehandlungsroutine innerhalb des EXCEPTION-Blocks behandeln.)

Nachdem Sie die gespeicherte Beispielprozedur aufgerufen haben, enthalten die Tabellen die folgenden Datensätze:

• Wenn Sie SELECT * FROM tbl_1; ausführen, werden zwei Datensätze zurückgegeben. Diese enthalten die Werte 1 und 2.

• Wenn Sie SELECT * FROM tbl_error_logging; ausführen, wird ein Datensatz mit den folgenden Werten zurückgegeben: Aufgetretener Fehler, 42703, und Spalte „val“ ist in tbl_1 nicht vorhanden.

Das folgende zusätzliche Beispiel zur Fehlerbehandlung verwendet sowohl einen EXIT-Handler als auch einen CONTINUE-Handler. Es erstellt zwei Tabellen: eine Datentabelle und eine Protokollierungstabelle. Außerdem wird eine gespeicherte Prozedur erstellt, die die Fehlerbehandlung demonstriert:

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;

Nachdem Sie die gespeicherte Prozedur erstellt haben, rufen Sie sie wie folgt auf:

CALL sp_exc_handling_2();

Wenn im inneren Ausnahmeblock, der vom inneren Satz von BEGIN und END in Klammern eingeschlossen ist, ein Fehler auftritt, wird er vom EXIT-Handler behandelt. Alle Fehler, die im äußeren Block auftreten, werden vom CONTINUE-Handler behandelt.

Nachdem Sie die gespeicherte Beispielprozedur aufgerufen haben, enthalten die Tabellen die folgenden Datensätze:

• Wenn Sie SELECT * FROM tbl_1; ausführen, werden vier Datensätze mit den Werten 1, 2, 3 und 100 zurückgegeben.

• Wenn Sie SELECT * FROM tbl_error_logging; ausführen, werden zwei Datensätze zurückgegeben. Sie haben die folgenden Werte: Aufgetretener Fehler, 42703, und Spalte „val“ ist in tbl_1 nicht vorhanden.

Wenn die Tabelle tbl_error_logging nicht existiert, wird eine Ausnahme ausgelöst.

Das folgende Beispiel demonstriert, wie Sie den CONTINUE-Ausnahme-Handler mit der FOR-Schleife nutzen. In diesem Beispiel werden zwei Tabellen erstellt und in einer FOR-Schleife in einer gespeicherten Prozedur verwendet. Die FOR-Schleife ist eine Ergebnismengenvariante, was bedeutet, dass sie über die Ergebnisse einer Abfrage iteriert:

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;

Rufen Sie die gespeicherte Prozedur auf:

CALL sp_exc_handling_loop();

Nachdem Sie die gespeicherte Beispielprozedur aufgerufen haben, enthalten die Tabellen die folgenden Datensätze:

• Wenn Sie SELECT * FROM tbl_2; ausführen, werden zwei Datensätze zurückgegeben. Diese enthalten die Werte 1 und 3.

• Wenn Sie SELECT * FROM tbl_error_logging; ausführen, wird ein Datensatz mit den folgenden Werten zurückgegeben: Aufgetretener Fehler, 42703, und Spalte „val“ ist in tbl_2 nicht vorhanden.

Hinweise zur Verwendung des CONTINUE-Handlers:

• Die Schlüsselwörter CONTINUE_HANDLER und EXIT_HANDLER können nur in gespeicherten NONATOMIC-Prozeduren verwendet werden.

• Die Schlüsselwörter CONTINUE_HANDLER und EXIT_HANDLER sind optional. EXIT_HANDLER ist die Standardeinstellung.