Unterschiedliche Standardfehlerbehandlung in Oracle und PostgreSQL
Ich vergleiche das Standardverhalten von Oracle und PostgreSQL, nachdem ein Fehler in einem PL / SQL-Code (PL / pgSQL) aufgetreten ist. Zu diesem Zweck habe ich einen analogen Oracle- und PostgreSQL-Code geschrieben, der unten gezeigt wird.
Oracle-Code ( db <> Geige ):
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;
PostgreSQL-Code ( 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;
Hinweis: In PostgreSQL BEGIN TRANSACTION
führe ich zusätzlich die Anweisung aus, um das automatische Festschreiben zu deaktivieren, da Oracle kein automatisches Festschreiben hat und ich möchte, dass beide Codes analog sind.
Das Ergebnis der SELECT * FROM table1
Abfrage ist eine Zeile in Oracle und keine Zeilen in PostgreSQL.
Wie Sie sehen können, liefert der analoge Code in Oracle und PostgreSQL unterschiedliche Ergebnisse. Was ist der Grund für diesen Unterschied in der Standardfehlerbehandlung?
Antworten
Oracle und PostgreSQL verhalten sich hier tatsächlich unterschiedlich.
Oracle hat etwas, das ich als "Rollback auf Anweisungsebene" bezeichnen würde: Wenn eine Anweisung, die in einer Transaktion ausgeführt wird, einen Fehler verursacht, werden nur die Auswirkungen dieser Anweisung zurückgesetzt, und die Transaktion wird fortgesetzt.
In PostgreSQL bricht jeder Fehler in einer Transaktion die gesamte Transaktion ab, sodass Sie die Transaktion nur zurücksetzen können und dies überhaupt keine Auswirkungen hat. Dies ist eher im Sinne von „alles oder nichts“, aber soweit ich sehen kann, ist der SQL-Standard diesbezüglich nicht spezifisch, sodass beide Verhaltensweisen argumentiert werden können.
Sie können jedoch standardkonforme Sicherungspunkte in PostgreSQL verwenden, um einen Fehler in einer Transaktion zu beheben:
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;
Beachten Sie jedoch, dass Sie nicht zu viele Sicherungspunkte (nicht mehr als 64) pro Transaktion verwenden, da sonst die Leistung beeinträchtigt werden kann.
In Oracle verwenden Sie zwei separate Transaktionen. Die erste ist erfolgreich, die zweite schlägt jedoch fehl. In PostgreSQL weisen Sie es ausdrücklich an, nur eine Transaktion zu verwenden und die Anweisungen zusammen zu behandeln.
Wenn Sie in Oracle einen anonymen PL / SQL-Block verwenden, um die Anweisungen in einer einzigen Transaktion zu gruppieren:
BEGIN
INSERT INTO table1 VALUES (1);
raise_error();
END;
/
Und gleichwertig in PostgreSQL:
DO
$$ BEGIN INSERT INTO table1 VALUES (1); CALL raise_error(); END; $$ LANGUAGE plpgsql;
Dann enthält die Tabelle keine Zeilen, da die Ausnahme von der Prozedur die gesamte Transaktion zurücksetzt.
In Oracle können Sie Folgendes tun:
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;
/
Dies hätte den gleichen Effekt, wenn beide Transaktionen auf das letzte Commit zurückgesetzt würden.
db <> fiddle Oracle PostgreSQL