OracleとPostgreSQLでの異なるデフォルトのエラー処理
PL / SQL（PL / pgSQL）コードでエラーが発生した後、OracleとPostgreSQLのデフォルトの動作を比較します。この目的のために、以下に示す類似のOracleおよびPostgreSQLコードを作成しました。
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;
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;
注：PostgreSQLにはBEGIN TRANSACTION自動コミットを無効にするステートメントを追加で実行します。これは、Oracleには自動コミットがなく、両方のコードを類似させたいためです。
SELECT * FROM table1クエリの結果は、Oracleでは1行であり、PostgreSQLでは行がありません。
ご覧のとおり、OracleとPostgreSQLの類似のコードでは異なる結果が得られます。デフォルトのエラー処理におけるこの違いの理由は何ですか？
回答
OracleとPostgreSQLは、実際、ここでは動作が異なります。
Oracleには、「ステートメントレベルのロールバック」と呼ばれるものがあります。トランザクション内で実行されているステートメントによってエラーが発生した場合、そのステートメントの影響のみがロールバックされ、トランザクションが続行されます。
PostgreSQLでは、トランザクション内のエラーはトランザクション全体を中止するため、トランザクションをロールバックすることしかできず、まったく影響はありません。これは「オールオアナッシング」の精神に基づいていますが、私が見る限り、SQL標準はこれに固有ではないため、両方の動作について議論することができます。
ただし、PostgreSQLの標準準拠のセーブポイントを使用して、トランザクションのエラーから「回復」することができます。
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;
ただし、トランザクションごとに使用するセーブポイントの数が多すぎない（64以下）ことに注意してください。使用すると、パフォーマンスが低下する可能性があります。
Oracleでは、2つの別々のトランザクションを使用しています。最初のトランザクションは成功しますが、2番目のトランザクションは失敗します。PostgreSQLでは、1つのトランザクションのみを使用し、ステートメントを一緒に処理するように明示的に指示しています。
Oracleでは、PL / SQL匿名ブロックを使用してステートメントを単一のトランザクションにグループ化する場合：
BEGIN
INSERT INTO table1 VALUES (1);
raise_error();
END;
/
そして、同等にPostgreSQLでは：
DO
$$ BEGIN INSERT INTO table1 VALUES (1); CALL raise_error(); END; $$ LANGUAGE plpgsql;
その後、プロシージャからの例外がトランザクション全体をロールバックするため、テーブルに行はありません。
または、Oracleでは、次のことができます。
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;
/
これは、両方のトランザクションを最後のコミットにロールバックするのと同じ効果があります。
db <> fiddle Oracle PostgreSQL