More than 1 year has passed since last update.

PL/SQLとPL/pgSQLのトランザクション制御の違いに関する検証

Posted at 2022-12-04

はじめに

本記事はPL/SQL（DB2（Oracle互換））資材をPL/pgSQL（PostgreSQL）にマイグレーションする際に非常に苦労したPL/SQLとPL/pgSQLのトランザクション制御の違いについて、自分が得た知見を共有する記事となります。

DBMSのマイグレーションに関して、調べてみるといろいろ参考になる資料や事例が見つかるのですが、トランザクション制御に関しては単純に構文を変更する対応ではなく、既存の処理ロジックを修正する可能性もあり、マイグレーションがやや面倒です。

修正内容に関してこれが正解というわけではないかもしれませんが、PL/SQLからPL/pgSQLに改修するとなった際に、少しでもこの記事が参考になれば幸いです。

PL/SQL、PL/pgSQLのDBMSおよびバージョンについて

本記事で取り上げるPL/SQL、PL/pgSQLのDBMSおよびバージョンは以下となります。

PL/SQL

DBMS	バージョン
Oracle	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

PL/pgSQL

DBMS	バージョン
PostgreSQL	12.12

PL/SQLとPL/pgSQLのトランザクション制御の互換性について

トランザクション制御の互換性一覧

PL/SQLとPL/pgSQLのトランザクション制御の互換性について整理すると以下の通りになります。

パターン			PL/SQL		PL/pgSQL
パターン			procedure	function	procedure	function
パターン1	明示的なコミット／ロールバック	EXCEPTION句無し	○	○	○（※1）	×
パターン2	明示的なコミット／ロールバック	EXCEPTION句有り	○	○	×	×
パターン3	明示的なトランザクション（BEGINコマンド内）での明示的なコミット／ロールバック		○	○	×	×
パターン4	Excption発生時の自動ロールバック		×	×	○	○
パターン5	SAVEPOINT		○	○	×	×

※1：PROCEDURE単体での明示的なコミット／ロールバックは可能だが、以下のいずれかの条件を満たす場合は明示的なコミット／ロールバックが不可となる

PROCEDUREの呼び出し元がFUNCTIONの場合
PROCEDUREの呼び出し元でパターン2／パターン3の条件を満たしている場合

PostgreSQLの公式ドキュメントにトランザクション制御が不可となるケースの記載があります。

トランザクション制御は、トップレベル、または、他の干渉するコマンドを伴わない入れ子のCALLまたはDO呼び出しからの、CALLまたはDOによる呼び出しのみで可能です。例えば、呼び出しスタックがCALL proc1() → CALL proc2() → CALL proc3()である場合、二番目と三番目のプロシージャはトランザクション制御を実行できます。しかし、呼び出しスタックがCALL proc1() → SELECT func2() → CALL proc3()である場合、間のSELECTのため、最後のプロシージャはトランザクション制御を実行できません。

比較するとPL/pgSQLのほうがトランザクション制御に関して制限が多いことがわかります。
そのため、トランザクション制御の実装に関してPL/SQLからPL/pgSQLそのまま移植するのは難しいのですが、以降でそれぞれのパターンについて具体的な違いを紹介します。

各パターンの具体例

以降で各パターンの具体例をサンプルコードを用いて紹介しますが、サンプルコードを動かすために必要なTBLの情報は以下となります。
※あくまでトランザクション制御の検証用のためTBLの構造は適当に決めました。

検証用TBL情報

テーブル定義

銀行口座TBL（PostgresSQL）※Oracleは割愛

アクセスログTBL（PostgresSQL）※Oracleは割愛

DDL

検証用DDL（Oracle）

CREATE TABLE ORACLEUSER.ACCESSLOG (
    ID NUMBER(10,0)
    , USERID VARCHAR(10)
    , OPECODE VARCHAR(10) 
    , OPESTS NUMBER(1,0) 
    , LOGMSG VARCHAR(256)
    , ACCESSTIME TIMESTAMP(6)
    , primary key(ID, USERID, OPECODE, OPESTS)
);
COMMENT ON TABLE ORACLEUSER.ACCESSLOG IS 'アクセスログ';
COMMENT ON COLUMN ORACLEUSER.ACCESSLOG.ID IS 'ID';
COMMENT ON COLUMN ORACLEUSER.ACCESSLOG.USERID IS 'ユーザID';
COMMENT ON COLUMN ORACLEUSER.ACCESSLOG.OPECODE IS '取引コード';
COMMENT ON COLUMN ORACLEUSER.ACCESSLOG.OPESTS IS '取引ステータス';
COMMENT ON COLUMN ORACLEUSER.ACCESSLOG.LOGMSG IS 'ログメッセージ';
COMMENT ON COLUMN ORACLEUSER.ACCESSLOG.ACCESSTIME IS 'アクセス日時';

CREATE TABLE ORACLEUSER.BANKACCOUNT (
    USERID VARCHAR(10)
    , ACCNUMBER NUMBER(7,0) 
    , ACCTYPE VARCHAR(30) 
    , NAME VARCHAR(30)
    , BALANCE NUMBER(20,0) 
    , LASTDATE TIMESTAMP(6)
    , primary key(USERID, ACCNUMBER, ACCTYPE)
);
COMMENT ON TABLE ORACLEUSER.BANKACCOUNT IS '銀行口座';
COMMENT ON COLUMN ORACLEUSER.BANKACCOUNT.USERID IS 'ユーザID';
COMMENT ON COLUMN ORACLEUSER.BANKACCOUNT.ACCNUMBER IS '口座番号';
COMMENT ON COLUMN ORACLEUSER.BANKACCOUNT.ACCTYPE IS '口座種別';
COMMENT ON COLUMN ORACLEUSER.BANKACCOUNT.NAME IS '名義';
COMMENT ON COLUMN ORACLEUSER.BANKACCOUNT.BALANCE IS '残高';
COMMENT ON COLUMN ORACLEUSER.BANKACCOUNT.LASTDATE IS '更新日';

検証用DDL（PostgreSQL）

CREATE TABLE TEST_TRANSACTION.ACCESSLOG (
    ID NUMERIC(10,0)
    , USERID VARCHAR(10) 
    , OPECODE VARCHAR(10) 
    , OPESTS NUMERIC(1,0) 
    , LOGMSG VARCHAR(256)
    , ACCESSTIME TIMESTAMP(6)
    , primary key(ID, USERID, OPECODE, OPESTS)
);
COMMENT ON TABLE TEST_TRANSACTION.ACCESSLOG IS 'アクセスログ';
COMMENT ON COLUMN TEST_TRANSACTION.ACCESSLOG.ID IS 'ID';
COMMENT ON COLUMN TEST_TRANSACTION.ACCESSLOG.USERID IS 'ユーザID';
COMMENT ON COLUMN TEST_TRANSACTION.ACCESSLOG.OPECODE IS '取引コード';
COMMENT ON COLUMN TEST_TRANSACTION.ACCESSLOG.OPESTS IS '取引ステータス';
COMMENT ON COLUMN TEST_TRANSACTION.ACCESSLOG.LOGMSG IS 'ログメッセージ';
COMMENT ON COLUMN TEST_TRANSACTION.ACCESSLOG.ACCESSTIME IS 'アクセス日時';

CREATE TABLE TEST_TRANSACTION.BANKACCOUNT (
    USERID VARCHAR(10) 
    , ACCNUMBER NUMERIC(7,0) 
    , ACCTYPE VARCHAR(30)  
    , NAME VARCHAR(30)
    , BALANCE NUMERIC(20,0)
    , LASTDATE TIMESTAMP(6)
    , primary key(USERID, ACCNUMBER, ACCTYPE)
);
COMMENT ON TABLE TEST_TRANSACTION.BANKACCOUNT IS '銀行口座';
COMMENT ON COLUMN TEST_TRANSACTION.BANKACCOUNT.USERID IS 'ユーザID';
COMMENT ON COLUMN TEST_TRANSACTION.BANKACCOUNT.ACCNUMBER IS '口座番号';
COMMENT ON COLUMN TEST_TRANSACTION.BANKACCOUNT.ACCTYPE IS '口座種別';
COMMENT ON COLUMN TEST_TRANSACTION.BANKACCOUNT.NAME IS '名義';
COMMENT ON COLUMN TEST_TRANSACTION.BANKACCOUNT.BALANCE IS '残高';
COMMENT ON COLUMN TEST_TRANSACTION.BANKACCOUNT.LASTDATE IS '更新日';

パターン１　明示的なコミット／ロールバック（EXCEPTION句無し）

サンプルプログラムとしてINSERTを10回行い、偶数回目の場合は明示的なコミット、奇数回目の場合はロールバックを実施する処理を実行することでPL/pgSQLとPL/SQLの動作の違いを検証します。

パターン１　PL/SQL

パターン１（PROCEDURE）

-- > --------------------------
-- > パターン１（PROCEDURE）
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE01_PROC
IS
BEGIN   
    FOR i IN 0..9 LOOP
        -- 銀行口座TBLにレコードを登録
        INSERT INTO ORACLEUSER.BANKACCOUNT 
            (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
        VALUES 
            ('testcase01', i, '普通', 'テストケース０１', 0, CURRENT_TIMESTAMP);
        IF MOD(i, 2) = 0 THEN
            -- 口座番号が偶数の場合のみコミット
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;

パターン１実行結果（PROCEDURE）

SQL> CALL ORACLEUSER.TESTCASE01_PROC();
コールが完了しました。
SQL> SELECT * FROM ORACLEUSER.BANKACCOUNT;
USERID                ACCNUMBER   ACCTYPE NAME                                                            BALANCE LASTDATE
-------------------- ---------- ---------- ------------------------------------------------------------ ---------- ---------------------------------------------------------------------------
testcase01                    0 普通       テストケース０１                                                      0 22-11-05 15:21:48.584000
testcase01                    2 普通       テストケース０１                                                      0 22-11-05 15:21:48.585000
testcase01                    4 普通       テストケース０１                                                      0 22-11-05 15:21:48.591000
testcase01                    6 普通       テストケース０１                                                      0 22-11-05 15:21:48.592000
testcase01                    8 普通       テストケース０１                                                      0 22-11-05 15:21:48.592000

パターン１（FUNCTION）

-- > --------------------------
-- > パターン１（FUNCTION）
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.EXEC_TESTCASE01_FUN 
IS
    i_result INTEGER;
BEGIN
    i_result := TESTCASE01_FUN();
    DBMS_OUTPUT.PUT_LINE(i_result);
END;

CREATE OR REPLACE FUNCTION ORACLEUSER.TESTCASE01_FUN RETURN INTEGER
IS
BEGIN   
    FOR i IN 0..9 LOOP
        -- 銀行口座TBLにレコードを登録
        INSERT INTO ORACLEUSER.BANKACCOUNT 
            (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
        VALUES 
            ('testcase01', i, '普通', 'テストケース０１', 0, CURRENT_TIMESTAMP);
        IF MOD(i, 2) = 0 THEN
            -- 口座番号が偶数の場合のみコミット
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
    RETURN 0;
END;

パターン１実行結果（FUNCTION）

SQL> CALL ORACLEUSER.EXEC_TESTCASE01_FUN();
コールが完了しました。
SQL> SELECT * FROM ORACLEUSER.BANKACCOUNT;
USERID                ACCNUMBER   ACCTYPE NAME                                                            BALANCE LASTDATE
-------------------- ---------- ---------- ------------------------------------------------------------ ---------- ---------------------------------------------------------------------------
testcase01                    2 普通       テストケース０１                                                      0 22-11-05 15:32:28.692000
testcase01                    4 普通       テストケース０１                                                      0 22-11-05 15:32:28.693000
testcase01                    6 普通       テストケース０１                                                      0 22-11-05 15:32:28.693000
testcase01                    8 普通       テストケース０１                                                      0 22-11-05 15:32:28.694000
testcase01                    0 普通       テストケース０１                                                      0 22-11-05 15:32:28.683000

PL/SQLの場合は、PROCEDURE、FUNCTIONともに明示的なコミット／ロールバックが問題なく実行可能であり、偶数回目に登録した銀行口座のみがレコードに存在します。

パターン１　PL/pgSQL

パターン１（PROCEDURE）

-- > --------------------------
-- > パターン１（PROCEDURE）
-- > --------------------------
CREATE OR REPLACE FUNCTION TEST_TRANSACTION.TESTCASE01_PROC() AS $$
DECLARE 
BEGIN   
    FOR i IN 0..9 LOOP
        -- 銀行口座TBLにレコードを登録
        INSERT INTO TEST_TRANSACTION.BANKACCOUNT 
            (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
        VALUES 
            ('testcase01', i, '普通', 'テストケース０１', 0, CURRENT_TIMESTAMP);
        IF MOD(i, 2) = 0 THEN
            -- 口座番号が偶数の場合のみコミット
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;    
$$ LANGUAGE plpgsql;

パターン１実行結果（PROCEDURE）

postgres=# CALL TEST_TRANSACTION.TESTCASE01_PROC();
CALL
postgres=# SELECT * FROM TEST_TRANSACTION.BANKACCOUNT;
   userid   | accnumber | acctype |       name       | balance |          lastdate
------------+-----------+----------+------------------+---------+----------------------------
 testcase01 |         0 | 普通     | テストケース０１ |       0 | 2022-11-02 00:03:41.51322
 testcase01 |         2 | 普通     | テストケース０１ |       0 | 2022-11-02 00:03:41.51984
 testcase01 |         4 | 普通     | テストケース０１ |       0 | 2022-11-02 00:03:41.520246
 testcase01 |         6 | 普通     | テストケース０１ |       0 | 2022-11-02 00:03:41.520593
 testcase01 |         8 | 普通     | テストケース０１ |       0 | 2022-11-02 00:03:41.521016
(5 行)

PL/pgSQLのPROCEDUREの場合はPL/SQLと同様に明示的なコミット／ロールバックが問題なく実行可能です。

パターン１（FUNCTION）

-- > --------------------------
-- > パターン１（FUNCTION）
-- > --------------------------
CREATE OR REPLACE FUNCTION TEST_TRANSACTION.TESTCASE01_FUN() RETURNS INTEGER AS $$
DECLARE 
BEGIN   
    FOR i IN 0..9 LOOP
        -- 銀行口座TBLにレコードを登録
        INSERT INTO TEST_TRANSACTION.BANKACCOUNT 
            (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
        VALUES 
            ('testcase01', i, '普通', 'テストケース０１', 0, CURRENT_TIMESTAMP);
        IF MOD(i, 2) = 0 THEN
            -- 口座番号が偶数の場合のみコミット
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
    RETURN 0;
END;    
$$ LANGUAGE plpgsql;

パターン１実行結果（FUNCTION）

postgres=# SELECT TEST_TRANSACTION.TESTCASE01_FUN();
ERROR:  不正なトランザクション終了
CONTEXT:  PL/pgSQL関数test_transaction.testcase01_fun()の12行目 - COMMIT
postgres=#

PL/pgSQLのFUNCTIONの場合はPL/SQLと異なり、コミット処理時に不正なトランザクション終了というエラーメッセージが出力され、明示的なコミットが失敗します。

FUNCTION内でコミットが失敗する事象に関してはPostgreSQLの公式ドキュメントにも記載はされています。

関数とトリガプロシージャは常に外側の問い合わせで確立されたトランザクションの内側で実行されます。トランザクションの実行させる文脈はありませんので、これらはトランザクションを開始することもコミットすることもできません。しかし、EXCEPTION句を含むブロックは外側のトランザクションに影響しないでロールバックできるサブトランザクションを、実質的に作成できます。これについては42.6.8を参照してください。

公式ドキュメントを確認すると上記の通りFUNCTION内では明示的なコミット／ロールバックができない（という意味合いの記載）が存在します。
トランザクション制御を行いたい場合はPROCEDUREを利用するというのがPL/pgSQLの考え方だと思われますが、その一方でEXCEPTION句を用いることでサブトランザクションを作成することができると記載があります。

今回のような奇数回目のロールバックを発生させるプログラムであれば、ループ処理内にEXCEPTION句を含むBEGINブロックを追加することでCOMMIT／ROLLBACKコマンドを用いずに、FUNCTIONでも同様のレコードを残すことができます。

パターン１（FUNCTION）

CREATE OR REPLACE FUNCTION TEST_TRANSACTION.TESTCASE01_FUN() RETURNS INTEGER AS $$
DECLARE 
BEGIN   
    FOR i IN 0..9 LOOP
+        BEGIN
            -- 銀行口座TBLにレコードを登録
            INSERT INTO TEST_TRANSACTION.BANKACCOUNT 
                (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
            VALUES 
                ('testcase01', i, '普通', 'テストケース０１', 0, CURRENT_TIMESTAMP);
            IF MOD(i, 2) = 0 THEN
                -- 口座番号が偶数の場合のみコミット
-                COMMIT;
            ELSE
-               ROLLBACK;
+               RAISE EXCEPTION 'ロールバック実施'; -- 例外発生時に自動ロールバックが発生
            END IF;
+        EXCEPTION
+            WHEN OTHERS THEN
+                RAISE INFO 'SQLERRM:%', SQLERRM;
+        END;
    END LOOP;
    RETURN 0;
END;    
$$ LANGUAGE plpgsql;

パターン１実行結果（FUNCTION）

postgres=# SELECT TEST_TRANSACTION.TESTCASE01_FUN();
INFO:  SQLERRM:ロールバック実施
INFO:  SQLERRM:ロールバック実施
INFO:  SQLERRM:ロールバック実施
INFO:  SQLERRM:ロールバック実施
INFO:  SQLERRM:ロールバック実施
 testcase01_fun
----------------
              0
(1 行)
postgres=# SELECT * FROM TEST_TRANSACTION.BANKACCOUNT;
   userid   | accnumber | acctype |       name       | balance |          lastdate
------------+-----------+---------+------------------+---------+----------------------------
 testcase01 |         0 | 普通    | テストケース０１ |       0 | 2022-11-27 23:58:50.868144
 testcase01 |         2 | 普通    | テストケース０１ |       0 | 2022-11-27 23:58:50.868144
 testcase01 |         4 | 普通    | テストケース０１ |       0 | 2022-11-27 23:58:50.868144
 testcase01 |         6 | 普通    | テストケース０１ |       0 | 2022-11-27 23:58:50.868144
 testcase01 |         8 | 普通    | テストケース０１ |       0 | 2022-11-27 23:58:50.868144
(5 行)

パターン１　PL/SQL→PL/pgSQLへの修正について

FUNCTION内で明示的なコミット／ロールバックを利用している場合は、PROCEDUREに修正する
- FUNCTIONの戻り値はPROCEDUREにINOUTパラメータを追加することで代用する
- FUNCTIONの呼び出し元の個所をCALL文で実行するように修正する
- ただし、修正したPROCEDUREの呼び出し元がFUNCTION、パターン2／パターン3の条件を満たしている場合は上位の処理も見直す必要有り
EXCEPTION句を含むBEGINブロックを追加することで、FUNCTIONでもPL/SQLで行っていたトランザクション制御を再現することは可能

パターン２　明示的なコミット／ロールバック（EXCEPTION句有り）

パターン１のサンプルプログラムから、EXCEPTION句を追加してEXCEPTION句を含むブロック内で明示的なコミット／ロールバックを実行させます。

パターン２　PL/SQL

パターン２

-- > --------------------------
-- > パターン２（PROCEDURE）
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE02_PROC
IS
BEGIN
    BEGIN     
        FOR i IN 0..9 LOOP
            -- 銀行口座TBLにレコードを登録
            INSERT INTO ORACLEUSER.BANKACCOUNT 
                (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
            VALUES 
                ('testcase02', i, '普通', 'テストケース０２', 0, CURRENT_TIMESTAMP);
            IF MOD(i, 2) = 0 THEN
                -- 口座番号が偶数の場合のみコミット
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLCODE:' || SQLCODE || ' SQLERRM;' || SQLERRM);
    END;
END;

パターン２実行結果

SQL> CALL ORACLEUSER.TESTCASE02_PROC();
コールが完了しました。
SQL> SELECT * FROM ORACLEUSER.BANKACCOUNT;
USERID                ACCNUMBER   ACCTYPE NAME                                                            BALANCE LASTDATE
-------------------- ---------- ---------- ------------------------------------------------------------ ---------- ---------------------------------------------------------------------------
testcase02                    4 普通       テストケース０２                                                      0 22-11-06 16:42:52.255000
testcase02                    6 普通       テストケース０２                                                      0 22-11-06 16:42:52.255000
testcase02                    8 普通       テストケース０２                                                      0 22-11-06 16:42:52.256000
testcase02                    0 普通       テストケース０２                                                      0 22-11-06 16:42:52.243000
testcase02                    2 普通       テストケース０２                                                      0 22-11-06 16:42:52.255000

PL/SQLの場合はこのパターンでもパターン１と同様に明示的なコミット／ロールバックが問題なく実行可能であり、偶数回目に登録した銀行口座のみがレコードに存在します。

パターン２　PL/pgSQL

パターン２

-- > --------------------------
-- > パターン２（PROCEDURE）
-- > --------------------------
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE02_PROC() AS $$
DECLARE 
BEGIN
    BEGIN
        FOR i IN 0..9 LOOP
            -- 銀行口座TBLにレコードを登録
            INSERT INTO TEST_TRANSACTION.BANKACCOUNT 
                (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
            VALUES 
                ('testcase01', i, '普通', 'テストケース０１', 0, CURRENT_TIMESTAMP);
            IF MOD(i, 2) = 0 THEN
                -- 口座番号が偶数の場合のみコミット
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE INFO 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
    END;
END;    
$$ LANGUAGE plpgsql;

パターン２実行結果

postgres=# CALL TEST_TRANSACTION.TESTCASE02_PROC();
INFO:  SQLSTATE:2D000 SQLERRM:サブトランザクションの実行中はコミットできません
CALL
postgres=#

PL/pgSQLの場合は、パターン１で正常終了したPEOCEDUREにEXCEPTION句を追加して実行したところ、PL/SQLと異なりコミット時に例外が発生します。（パターン１とはエラーメッセージが異なりサブトランザクションの実行中はコミットできませんとなっています。）

この部分に関してもPostgreSQLの公式ドキュメントを見る限り記載がありますが、結構あっさりした記載となっています・・・・。

例外ハンドラを伴うブロック内ではトランザクションを終了できません。

なお、リファレンスには「例外ハンドラを伴うブロック内では～」と記載がある一方でエラーメッセージには「サブトランザクションの実行中はコミットできません」と記載されています。
試しにEXCEPTION句を外してBEGINブロックをネストした状態で実行してみると、このパターンでは正常に処理が終了します。

パターン２（EXCPTION句削除）

CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE02_PROC() AS $$
DECLARE 
BEGIN
    BEGIN
        FOR i IN 0..9 LOOP
            -- 銀行口座TBLにレコードを登録
            INSERT INTO TEST_TRANSACTION.BANKACCOUNT 
                (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
            VALUES 
                ('testcase01', i, '普通', 'テストケース０１', 0, CURRENT_TIMESTAMP);
            IF MOD(i, 2) = 0 THEN
                -- 口座番号が偶数の場合のみコミット
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        END LOOP;
-    EXCEPTION
-        WHEN OTHERS THEN
-            RAISE INFO 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
    END;
END;    
$$ LANGUAGE plpgsql;

パターン２（EXCPTION句削除）実行結果

postgres=# CALL TEST_TRANSACTION.TESTCASE02_PROC();
CALL
postgres=#

このことから、以下の表のとおり、EXCPTION句を含むBEGINブロック内のみサブトランザクション状態となっており、
サブトランザクション内では明示的なコミット／ロールバックのようなトランザクション制御がPL/pgSQLでは不可ということがわかります。

パターン	サブトランザクション状態	明示的なコミット／ロールバック
EXCEPTION句を含むBEGINブロック	○	×
EXCEPTION句を含まないBEGINブロック	×	○

EXCEPTION句を含まないBEGINブロックがサブトランザクション状態でないことはPostgreSQLの公式ドキュメントに記載があります。

PL/pgSQLにおける文をまとめるためのBEGIN/ENDとトランザクション制御用の同名のSQLコマンドとを取り違えないようにすることが重要です。 PL/pgSQLのBEGIN/ENDは単にまとめるためのもので、トランザクションを始めたり終わらせたりしません。

パターン２　PL/SQL→PL/pgSQLへの修正について

このケースの場合、EXCPTION句を削除しないと明示的なコミット／ロールバック実行時のエラーが回避できませんが、その場合エラーハンドリングができなくなるため、エラーハンドリング、明示的なコミット／ロールバックの動きを既存と合わせたい場合は小手先の対応は難しく処理の造りを見直す必要があります。

パターン３　明示的なトランザクション（BEGINコマンド内）での明示的なコミット／ロールバック

パターン１のPL/pgSQLで作成した明示的なコミット／ロールバックを行うPROCEDUREに関して、明示的なトランザクション（BEGINコマンド内）で実行すると以下の結果になります。

パターン３　PL/pgSQL

パターン３実行結果

postgres=# BEGIN;
BEGIN
postgres=# CALL TEST_TRANSACTION.TESTCASE01_PROC();
ERROR:  不正なトランザクション終了
CONTEXT:  PL/pgSQL関数test_transaction.testcase01_proc()の12行目 - COMMIT

上記の通り、単体では正常に処理が終了するPROCEDUREに関してもBEGINコマンド実行後にPROCEDUREの処理内で明示的なコミット／ロールバック処理を行う場合は「不正なトランザクション終了」というエラーメッセージが出力されます。

この事象自体はBEGINコマンドを実行せずにPROCEDUREをCALLするだけなのですが、同様（と思われる）ケースがJava側からJDBC経由でPL/pgSQLのPROCEDUREを実行する際に発生します。

Main.java (JDBC経由で明示的なCOMMIT処理を行うプロシージャーを実行するクラス)

package postgresqlTest;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

/**
 * JDBC経由で明示的なCOMMIT処理を行うプロシージャーを実行した時の動きを検証するクラス<br>
 * DBMSはPostgresqlを想定
 * @since 1.0
 * @version 1.0
 */
public class Main {

	private static String URL = "jdbc:postgresql://ホスト名:ポート番号/データベース名";
	private static String USER = "ユーザ名";
	private static String PASSWOED = "パスワード";
	private static Connection con = null;
	/**
	 * プロシージャーを実行するメインメソッド
	 * @param args 利用しない
	 * @throws SQLException
	 */
	public static void main(String[] args) throws SQLException {
		// データベース接続
		con = getConnection();
		// 実行するCALL文
		String callProc = "CALL TEST_PLPGSQL.commitTest(?, ?)";
		// CALL文実行後に登録されたデータを確認するSQL
		String selectSql = "SELECT * FROM TEST_PLPGSQL.TEST";
		System.out.println("-- > ------------------");
		System.out.println("-- > プロシージャー実行処理　開始");
		System.out.println("-- > ------------------");
		try (CallableStatement stmtProc = con.prepareCall(callProc);
				PreparedStatement stmtSql = con.prepareStatement(selectSql);) {
			// パラメータ設定
			stmtProc.setObject(1, 1, Types.INTEGER);
			stmtProc.setObject(2, "コミットテスト", Types.VARCHAR);
			// プロシージャー実行
			stmtProc.executeUpdate();
			con.commit();
			System.out.println("-- > ------------------");
			System.out.println("-- > 実行結果　正常終了");
			System.out.println("-- > ------------------");
			// SQL実行
			ResultSet rs = stmtSql.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getRow() + "レコード目");
				System.out.println("col1 :" + rs.getInt(1));
				System.out.println("col2 :" + rs.getString(2));
			}
		} catch (SQLException e) {
			System.out.println("-- > ------------------");
			System.out.println("-- > 実行結果　異常終了");
			System.out.println("-- > ------------------");
			System.out.println("SQLSTATE:" + e.getSQLState());
			System.out.println("SQLERRM:" + e.getMessage());
		} finally {
			// データベース切断
			closeConnection(con);
		}
		System.out.println("-- > ------------------");
		System.out.println("-- > プロシージャー実行処理　終了");
		System.out.println("-- > ------------------");
	}
	/**
	 * データベース接続処理を行うメソッド
	 * @throws SQLException
	 */
	private static Connection getConnection() throws SQLException {
		Connection con = DriverManager.getConnection(
				URL,
				USER,
				PASSWOED);
		con.setAutoCommit(false);
		return con;
	}
	/**
	 * データベース切断処理を行うメソッド
	 * @param con Connection
	 * @throws SQLException
	 */
	private static void closeConnection(Connection con) throws SQLException {
		if (con != null) {
			con.rollback();
			con.close();
		}
	}
}

パターン３（Java側から呼び出されるPROCEDURE）

CREATE OR REPLACE PROCEDURE TEST_PLPGSQL.commitTest(INTEGER, TEXT) AS $$
DECLARE	
BEGIN 
	TRUNCATE TABLE TEST_PLPGSQL.TEST;
    INSERT INTO TEST_PLPGSQL.TEST (col1, col2) VALUES ($1, $2);
	COMMIT; -- 明示的なコミット（PROCEDUREの処理内のため成功する想定）
	INSERT INTO TEST_PLPGSQL.TEST (col1, col2) VALUES ($1 + 1, $2);
END;	
$$ LANGUAGE plpgsql;

実行結果

-- > ------------------
-- > プロシージャー実行処理　開始
-- > ------------------
-- > ------------------
-- > 実行結果　異常終了
-- > ------------------
SQLSTATE:2D000
SQLERRM:ERROR: invalid transaction termination
  場所: PL/pgSQL function test_plpgsql.committest(integer,text) line 6 at COMMIT
-- > ------------------
-- > プロシージャー実行処理　終了
-- > ------------------

PROCEDURE内でのコミット処理は本来正常終了しますが、Java側から呼び出した場合はコミット実施時に例外が発生します。
推測ではあるのですが、これも該当のPROCEDUREを実行する前にBEGINコマンドが実行されているためだと思われます。

PostgreSQL側のログを確認すると、PROCEDUREを実行する直前にBEGINコマンドを実行している箇所（６行目）が存在します。
※postgresql.confファイル内に定義されているlog_min_messagesの値をDEBUG5に変更しています。

ログ

2022-11-24 23:00:46.965 JST [6563] DEBUG:  parse <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'
2022-11-24 23:00:46.965 JST [6563] DEBUG:  bind <unnamed> to <unnamed>
2022-11-24 23:00:46.965 JST [6563] DEBUG:  parse <unnamed>: COMMIT
2022-11-24 23:00:46.965 JST [6563] DEBUG:  bind <unnamed> to <unnamed>
2022-11-24 23:00:46.965 JST [6563] DEBUG:  CommitTransaction(1) name: unnamed; blockState: END; state: INPROGRESS, xid/subid/cid: 0/1/0
2022-11-24 23:00:47.008 JST [6563] DEBUG:  parse <unnamed>: BEGIN
2022-11-24 23:00:47.008 JST [6563] DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
2022-11-24 23:00:47.008 JST [6563] DEBUG:  bind <unnamed> to <unnamed>
2022-11-24 23:00:47.008 JST [6563] DEBUG:  parse <unnamed>: CALL TEST_PLPGSQL.commitTest($1, $2)
2022-11-24 23:00:47.012 JST [6563] DEBUG:  bind <unnamed> to <unnamed>
2022-11-24 23:00:47.022 JST [6563] DEBUG:  building index "test_pkey" on table "test" serially
2022-11-24 23:00:47.022 JST [6563] CONTEXT:  SQL statement "TRUNCATE TABLE TEST_PLPGSQL.TEST"
	PL/pgSQL function test_plpgsql.committest(integer,text) line 4 at SQL statement
2022-11-24 23:00:47.023 JST [6563] DEBUG:  building index "pg_toast_33205_index" on table "pg_toast_33205" serially
2022-11-24 23:00:47.023 JST [6563] CONTEXT:  SQL statement "TRUNCATE TABLE TEST_PLPGSQL.TEST"
	PL/pgSQL function test_plpgsql.committest(integer,text) line 4 at SQL statement
2022-11-24 23:00:47.024 JST [6563] ERROR:  invalid transaction termination
2022-11-24 23:00:47.024 JST [6563] CONTEXT:  PL/pgSQL function test_plpgsql.committest(integer,text) line 6 at COMMIT
2022-11-24 23:00:47.024 JST [6563] STATEMENT:  CALL TEST_PLPGSQL.commitTest($1, $2)

パターン３　PL/SQL→PL/pgSQLへの修正について

トランザクション部分をPL/SQLと合わせる場合はコミット単位でPROCEDURE処理を分割して、コミット処理はPL/pgSQLではなくJava側で実行するやり方があると思います。

他には、Java側から明示的なコミット／ロールバックを行うPROCEDUREを呼ぶ場合はDBLINKを経由して新規セッションで呼び出すというのも考えられます。

以下のようなdblinkで先ほどのPROCEDUREを呼び出すPROCEDUREを作成し、Java側でこのPROCEDUREを呼び出すように処理を修正すると、正常に処理が終了します。

dblinkを用いたPROCEDURE呼び出し

CREATE OR REPLACE PROCEDURE TEST_PLPGSQL.commitTest_dblink_wrap(INTEGER, TEXT) AS $$
DECLARE	
BEGIN 
	PERFORM DBLINK_CONNECT('dblink_test', 'host=ホスト名 port=ポート dbname=DB名 user=ユーザ名 password=パスワード');
    PERFORM DBLINK_EXEC('dblink_test', FORMAT('CALL TEST_PLPGSQL.commitTest(%L, %L)', $1, $2));
    PERFORM DBLINK_DISCONNECT('dblink_test');
EXCEPTION
    WHEN OTHERS THEN
        PERFORM DBLINK_DISCONNECT('dblink_test');
END;	
$$ LANGUAGE plpgsql;

実行結果

-- > ------------------
-- > プロシージャー実行処理　開始
-- > ------------------
-- > ------------------
-- > 実行結果　正常終了
-- > ------------------
1レコード目
col1 :1
col2 :コミットテスト
2レコード目
col1 :2
col2 :コミットテスト
-- > ------------------
-- > プロシージャー実行処理　終了
-- > ------------------

※dblinkでプロシージャを実行させる方法は以下を参照

パターン４　Excption発生時の自動ロールバック

パターン４では、銀行口座TBLにレコードに10レコード分INSERTを行い、後続の処理で一意制約違反を発生させた場合におけるPL/pgSQLとPL/SQLの動作の違いを検証します。

PL/SQL

パターン４

-- > --------------------------
-- > パターン４（PROCEDURE）
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE04_PROC
IS
BEGIN
    BEGIN
        FOR i IN 0..9 LOOP
            -- 銀行口座TBLにレコードを登録
            INSERT INTO ORACLEUSER.BANKACCOUNT 
                (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
            VALUES 
                ('testcase04', i, '普通', 'テストケース０４', 0, CURRENT_TIMESTAMP);
        END LOOP;
        -- 一意制約違反を発生
        INSERT INTO ORACLEUSER.BANKACCOUNT 
            (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
        VALUES 
            ('testcase04', 0, '普通', 'テストケース０４', 0, CURRENT_TIMESTAMP);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLCODE:' || SQLCODE || ' SQLERRM:' || SQLERRM);
    END;
END;

パターン４実行結果

SQL> CALL ORACLEUSER.TESTCASE04_PROC();
SQLCODE:-1 SQLERRM:ORA-00001: 一意制約(ORACLEUSER.SYS_C006359)に反しています
コールが完了しました。
SQL> SELECT * FROM ORACLEUSER.BANKACCOUNT;
USERID                ACCNUMBER   ACCTYPE NAME                                                            BALANCE LASTDATE
-------------------- ---------- ---------- ------------------------------------------------------------ ---------- ---------------------------------------------------------------------------
testcase04                    0 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    1 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    2 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    3 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    4 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    5 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    6 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    7 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    8 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
testcase04                    9 普通       テストケース０４                                                      0 22-11-06 17:40:42.125000
10行が選択されました。

PL/SQLにおいて、例外が発生した後に銀行口座TBLのレコードを確認すると例外が発生する直前に登録されていたレコードが残っていることが確認できます。

PL/pgSQL

パターン４

-- > --------------------------
-- > パターン４（PROCEDURE）
-- > --------------------------

CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE04_PROC() AS $$
DECLARE 
BEGIN   
    BEGIN
        FOR i IN 0..9 LOOP
            -- 銀行口座TBLにレコードを登録
            INSERT INTO TEST_TRANSACTION.BANKACCOUNT 
                (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
            VALUES 
                ('testcase04', i, '普通', 'テストケース０４', 0, CURRENT_TIMESTAMP);
        END LOOP;
        -- 一意制約違反を発生
        INSERT INTO TEST_TRANSACTION.BANKACCOUNT 
            (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE)
        VALUES 
            ('testcase04', 0, '普通', 'テストケース０４', 0, CURRENT_TIMESTAMP);
    EXCEPTION
        WHEN OTHERS THEN
            RAISE INFO 'SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
    END;
END;    
$$ LANGUAGE plpgsql;

パターン４実行結果

postgres=# CALL TEST_TRANSACTION.TESTCASE04_PROC();
INFO:  SQLSTATE:23505 SQLERRM:重複したキー値は一意性制約"bankaccount_pkey"違反となります
CALL
postgres=# select * from TEST_TRANSACTION.BANKACCOUNT;
 userid | accnumber | acctype | name | balance | lastdate
--------+-----------+----------+------+---------+----------
(0 行)

PL/pgSQLにおいては、PL/SQLと異なり、例外が発生した後は暗黙的にロールバックが実行されるため、銀行口座TBLのレコードを確認すると例外が発生する直前に登録されていたレコードを取得することができずSELECTの結果が0件となっています。

パターン４　PL/SQL→PL/pgSQLへの修正について

PL/SQLのEXCPTION句内にROLLBACKコマンドが存在する場合

PL/pgSQLにおいては不要になる（暗黙的にロールバックされる）ためROLLBACKコマンドを削除する。

PL/SQLのEXCPTION句内にROLLBACKコマンドが存在しない場合

PL/pgSQLに移植しても動作するが、例外発生時のレコードの状態がPL/SQLとPL/pgSQLで異なる。
レコードの状態を完全に再現するのは難しいが、例外発生時に自動ロールバックが発生して問題があれば適切なタイミングでCOMMITコマンドを追加する。

パターン５ SAVEPOINT

トランザクション中に部分的にロールバックを行いたい場合はSAVEPOINTコマンドを利用します。
以下のサンプルプログラムでは、１件目のINSERT処理の直後でSAVEPOINTコマンドを実行して、例外発生時にROLLBACK TO セーブポイント名;を実行することで１件目のレコードのみ残っている状態にしています。

PL/SQL

パターン５

-- > --------------------------
-- > パターン５（PROCEDURE）
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE05_PROC
IS
    numRecCount INTEGER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    DBMS_OUTPUT.PUT_LINE('-- > INSERT処理開始！！！（1件目）');
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    INSERT INTO ORACLEUSER.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 0, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);

    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    DBMS_OUTPUT.PUT_LINE('-- > SAVEPOINT開始！！！');
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    SAVEPOINT SP1;
    
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    DBMS_OUTPUT.PUT_LINE('-- > INSERT処理開始！！！（２件目）');
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    INSERT INTO ORACLEUSER.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 1, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    
    SELECT COUNT(*) INTO numRecCount FROM ORACLEUSER.BANKACCOUNT;
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    DBMS_OUTPUT.PUT_LINE('-- > BANKACCOUNTテーブルのレコード件数:' || numRecCount || '件');
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    DBMS_OUTPUT.PUT_LINE('-- > INSERT処理開始！！！（一意制約違反を発生）');
    DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
    INSERT INTO ORACLEUSER.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 1, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > 例外発生！！！');
        DBMS_OUTPUT.PUT_LINE('-- > SQLCODE:' || SQLCODE || ' SQLERRM:' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > SAVEPOINT開始位置までROLLBACK！！！');
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        ROLLBACK TO SP1;
        
        SELECT COUNT(*) INTO numRecCount FROM ORACLEUSER.BANKACCOUNT;
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > BANKACCOUNTテーブルのレコード件数:' || numRecCount || '件');
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
END;

パターン５実行結果

SQL> CALL ORACLEUSER.TESTCASE05_PROC();
-- > ---------------------------------------
-- > INSERT処理開始！！！（1件目）
-- > ---------------------------------------
-- > ---------------------------------------
-- > SAVEPOINT開始！！！
-- > ---------------------------------------
-- > ---------------------------------------
-- > INSERT処理開始！！！（２件目）
-- > ---------------------------------------
-- > ---------------------------------------
-- > BANKACCOUNTテーブルのレコード件数:2件
-- > ---------------------------------------
-- > ---------------------------------------
-- > INSERT処理開始！！！（一意制約違反を発生）
-- > ---------------------------------------
-- > ---------------------------------------
-- > 例外発生！！！
-- > SQLCODE:-1 SQLERRM:ORA-00001: 一意制約(ORACLEUSER.SYS_C006359)に反しています
-- > ---------------------------------------
-- > ---------------------------------------
-- > SAVEPOINT開始位置までROLLBACK！！！
-- > ---------------------------------------
-- > ---------------------------------------
-- > BANKACCOUNTテーブルのレコード件数:1件
-- > ---------------------------------------
コールが完了しました。
SQL> SELECT * FROM ORACLEUSER.BANKACCOUNT;
USERID                ACCNUMBER   ACCTYPE NAME                                                            BALANCE LASTDATE
-------------------- ---------- ---------- ------------------------------------------------------------ ---------- ---------------------------------------------------------------------------
testcase05                    0 普通       テストケース０５                                                      0 22-11-07 23:26:24.755000
SQL>

PL/SQLの場合は問題なく動作しますが上記のサンプルプログラムをPL/pgSQL実行させると・・・・。

PL/pgSQL

パターン５

CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE05_PROC() AS $$
DECLARE 
    numRecCount INTEGER;
BEGIN
    RAISE INFO '-- > ---------------------------------------' ;
    RAISE INFO '-- > INSERT処理開始！！！（1件目）' ;
    RAISE INFO '-- > ---------------------------------------' ;
    INSERT INTO TEST_TRANSACTION.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 0, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP) ;

    RAISE INFO '-- > ---------------------------------------' ;
    RAISE INFO '-- > SAVEPOINT開始！！！' ;
    RAISE INFO '-- > ---------------------------------------' ;
    SAVEPOINT SP1;
    
    RAISE INFO '-- > ---------------------------------------' ;
    RAISE INFO '-- > INSERT処理開始！！！（２件目）' ;
    RAISE INFO '-- > ---------------------------------------' ;
    INSERT INTO TEST_TRANSACTION.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 1, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP) ;
    
    SELECT COUNT(*) INTO numRecCount FROM TEST_TRANSACTION.BANKACCOUNT;
    RAISE INFO '-- > ---------------------------------------' ;
    RAISE INFO '-- > BANKACCOUNTテーブルのレコード件数:%件', numRecCount;
    RAISE INFO '-- > ---------------------------------------' ;
    
    RAISE INFO '-- > ---------------------------------------' ;
    RAISE INFO '-- > INSERT処理開始！！！（一意制約違反を発生）' ;
    RAISE INFO '-- > ---------------------------------------' ;
    INSERT INTO TEST_TRANSACTION.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 1, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP) ;
EXCEPTION
    WHEN OTHERS THEN
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > 例外発生！！！' ;
        RAISE INFO '-- > SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
        RAISE INFO '-- > ---------------------------------------' ;
        
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > SAVEPOINT開始位置までROLLBACK！！！' ;
        RAISE INFO '-- > ---------------------------------------' ;
        --ROLLBACK TO SP1;
        
        SELECT COUNT(*) INTO numRecCount FROM TEST_TRANSACTION.BANKACCOUNT;
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > BANKACCOUNTテーブルのレコード件数:%件', numRecCount;
        RAISE INFO '-- > ---------------------------------------' ;
END;    
$$ LANGUAGE plpgsql;

パターン５実行結果

postgres=# CALL TEST_TRANSACTION.TESTCASE05_PROC();
INFO:  -- > ---------------------------------------
INFO:  -- > INSERT処理開始！！！（1件目）
INFO:  -- > ---------------------------------------
INFO:  -- > ---------------------------------------
INFO:  -- > SAVEPOINT開始！！！
INFO:  -- > ---------------------------------------
INFO:  -- > ---------------------------------------
INFO:  -- > 例外発生！！！
INFO:  -- > SQLSTATE:0A000 SQLERRM:PL/pgSQL 内ではサポートされないトランザクションコマンド
INFO:  -- > ---------------------------------------
INFO:  -- > ---------------------------------------
INFO:  -- > SAVEPOINT開始位置までROLLBACK！！！
INFO:  -- > ---------------------------------------
INFO:  -- > ---------------------------------------
INFO:  -- > BANKACCOUNTテーブルのレコード件数:0件
INFO:  -- > ---------------------------------------
CALL
postgres=#

SAVEPOINT SP1;コマンドでSAVEPOINTを実行したタイミングで例外が発生してしまいます。
エラーメッセージにはPL/pgSQL 内ではサポートされないトランザクションコマンドと記載がある通り、SAVEPOINTはPL/pgSQLには対応していません。（PostgresSQLのコマンドとしては利用可能です）

エラーメッセージにPL/pgSQL 内ではサポートされないとは記載されていますが、PostgresSQLではSAVEPOINTコマンドは利用可能であるため、PL/SQLからPL/pgSQLにマイグレーションする際はなぜこの箇所で例外が発生するのか、代替手段が存在するか解析に時間がかかりました。（PostgreSQLの公式ドキュメントを見ても明確にSAVEPOINTコマンドがPL/pgSQLで利用できないという記述は存在しないように見えます。）

パターン５　PL/SQL→PL/pgSQLへの修正について

SAVEPOINTコマンドの使い方や呼び出し元、呼び出し先のFUNCTION／PROCEDUREによって修正の仕方が様々ですが、いくつか修正例を取り上げておきます。

修正例１　BEGINブロック（EXCEPTION句有り）を追加することで対応可能なケース

以下のようなPL/SQLのサンプルプログラムであれば、SAVEPOINTの代わりにBEGINブロック（EXCEPTION句有り）を追加することで代用可能です。

PL/SQL

修正例１（PL/SQL）

CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE05_01_PROC
IS
BEGIN
    INSERT INTO ORACLEUSER.ACCESSLOG (ID, USERID, OPECODE, OPESTS, LOGMSG, ACCESSTIME) VALUES  (1, 'testcase05', 'XXXXXXXXXX', 1, NULL, CURRENT_TIMESTAMP);
    SAVEPOINT SP1;
    INSERT INTO ORACLEUSER.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 0, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    INSERT INTO ORACLEUSER.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 0, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SP1;
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > 例外発生！！！');
        DBMS_OUTPUT.PUT_LINE('-- > SQLCODE:' || SQLCODE || ' SQLERRM:' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
END;

実行結果

SQL> CALL ORACLEUSER.TESTCASE05_01_PROC();
-- > ---------------------------------------
-- > 例外発生！！！
-- > SQLCODE:-1 SQLERRM:ORA-00001:
一意制約(ORACLEUSER.SYS_C006392)に反しています
-- > ---------------------------------------
コールが完了しました。
SQL> SELECT * FROM ORACLEUSER.BANKACCOUNT;
レコードが選択されませんでした。
SQL> SELECT * FROM ORACLEUSER.ACCESSLOG;
        ID USERID               OPECODE                  OPESTS
---------- -------------------- -------------------- ----------
LOGMSG
--------------------------------------------------------------------------------
ACCESSTIME
---------------------------------------------------------------------------
         1 testcase05           XXXXXXXXXX                    1

22-12-03 22:12:51.037000

PL/pgSQL

修正例１（PL/pgSQL）

CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE05_01_PROC() AS $$
DECLARE 
BEGIN
    INSERT INTO TEST_TRANSACTION.ACCESSLOG (ID, USERID, OPECODE, OPESTS, LOGMSG, ACCESSTIME) VALUES  (1, 'testcase05', 'XXXXXXXXXX', 1, NULL, CURRENT_TIMESTAMP);
    -- SAVEPOINTコマンド実行箇所でBEGINブロックで囲う
    BEGIN
        INSERT INTO TEST_TRANSACTION.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 0, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
        INSERT INTO TEST_TRANSACTION.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 0, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    EXCEPTION
        WHEN OTHERS THEN
            RAISE INFO '-- > ---------------------------------------' ;
            RAISE INFO '-- > 例外発生！！！' ;
            RAISE INFO '-- > SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
            RAISE INFO '-- > ---------------------------------------' ;
    END;
EXCEPTION
    WHEN OTHERS THEN
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > 例外発生！！！' ;
        RAISE INFO '-- > SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
        RAISE INFO '-- > ---------------------------------------' ;
END;    
$$ LANGUAGE plpgsql;

実行結果

postgres=# CALL TEST_TRANSACTION.TESTCASE05_01_PROC();
INFO:  -- > ---------------------------------------
INFO:  -- > 例外発生！！！
INFO:  -- > SQLSTATE:23505 SQLERRM:重複したキー値は一意性制約"bankaccount_pkey"違反となります
INFO:  -- > ---------------------------------------
CALL
postgres=# select * from TEST_TRANSACTION.BANKACCOUNT;
 userid | accnumber | acctype | name | balance | lastdate
--------+-----------+---------+------+---------+----------
(0 行)
postgres=# select * from TEST_TRANSACTION.ACCESSLOG;
 id |   userid   |  opecode   | opests | logmsg |         accesstime
----+------------+------------+--------+--------+----------------------------
  1 | testcase05 | XXXXXXXXXX |      1 |        | 2022-12-03 22:29:09.029704
(1 行)

修正例２　全体的に処理の修正が必要になるケース

修正例１のようなシンプルな修正であれば対応は楽なのですが、SAVEPOINTの利用の仕方によっては前後のPROCEDURE／FUNCTIONを処理を把握したうえで改修をしないといけないケースがあります。
一例ですが以下のようなケースを紹介します。

PL/SQL

修正例２

-- > --------------------------
-- > 最上位のPROCEDURE
-- > [処理概要]
-- > アクセスログTBL登録処理を行う
-- > SAVPPOINTを実行する（例外が発生した場合はアクセスログTBL登録処理段階までレコードを戻す）
-- > 銀行口座TBLの登録処理を行うプロシージャを実行する
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE05_02_PROC_MAIN
IS
    bResult boolean := true;
BEGIN
    -- アクセスログTBL登録処理
    INSERT INTO ORACLEUSER.ACCESSLOG (ID, USERID, OPECODE, OPESTS, LOGMSG, ACCESSTIME) VALUES  (1, 'testcase05', 'XXXXXXXXXX', 9, 'アクセスログレコード事前登録', CURRENT_TIMESTAMP);
    -- SAVPPOINTを実行する（例外が発生した場合はアクセスログTBL登録処理段階までレコードを戻す）
    SAVEPOINT SP1; 
    -- 銀行口座TBLの登録処理を行うプロシージャを実行
    ORACLEUSER.TESTCASE05_02_PROC(bResult);
    -- 実行結果判定
    IF bResult THEN
        DBMS_OUTPUT.PUT_LINE('正常終了');
    ELSE
        DBMS_OUTPUT.PUT_LINE('異常終了');
    END IF;
END;  
-- > --------------------------
-- > 最上位から呼び出されるPROCEDURE
-- > [処理概要]
-- > 銀行口座TBLの登録処理を行うプロシージャを２つ実行し、
-- > 正常終了した場合はアクセスログTBLの取引ステータスを0に更新し、trueを返却する
-- > 異常終了した場合はアクセスログTBLの取引ステータスを1に更新し、falseを返却する
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE05_02_PROC(pResult IN OUT boolean)
IS
    bResult boolean := true;
BEGIN
    ORACLEUSER.TESTCASE05_02_PROC_SUB01(bResult);
    IF NOT (bResult) THEN
        -- 異常終了の場合
        -- アクセスログTBLの取引ステータスを1(ここではエラー）に更新し、falseを返却
        ORACLEUSER.UPDATE_ACCESSLOG_ERROR('TESTCASE05_02_PROC_SUB01で異常終了');
        pResult := false;
        RETURN;
    END IF;
    ORACLEUSER.TESTCASE05_02_PROC_SUB02(bResult);
    IF NOT (bResult) THEN
        -- 異常終了の場合はfalseを返却
        -- アクセスログTBLの取引ステータスを1(ここではエラー）に更新し、falseを返却
        ORACLEUSER.UPDATE_ACCESSLOG_ERROR('TESTCASE05_02_PROC_SUB02で異常終了');
        pResult := false;
        RETURN;
    END IF;
    -- 正常終了の場合はtrueを返却
    ORACLEUSER.UPDATE_ACCESSLOG_SUCCESS('正常終了！！！！');
    pResult := true;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SP1;
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > 例外発生！！！');
        DBMS_OUTPUT.PUT_LINE('-- > SQLCODE:' || SQLCODE || ' SQLERRM:' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        pResult := false;
        RETURN;
END;  
-- > --------------------------
-- > 銀行口座TBLの登録処理を行うサブPROCEDURE
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE05_02_PROC_SUB01(pResult IN OUT boolean)
IS
    bResult boolean := true;
BEGIN
    INSERT INTO ORACLEUSER.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 0, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    -- 正常終了の場合はtrueを返却
    pResult := true;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SP1;
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > 例外発生！！！');
        DBMS_OUTPUT.PUT_LINE('-- > SQLCODE:' || SQLCODE || ' SQLERRM:' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        pResult := false;
        RETURN;
END;
-- > --------------------------
-- > 銀行口座TBLの登録処理を行うサブPROCEDURE（今回はここで例外を発生させる）
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.TESTCASE05_02_PROC_SUB02(pResult IN OUT boolean)
IS
    bResult boolean := true;
BEGIN
    INSERT INTO ORACLEUSER.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 1, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    INSERT INTO ORACLEUSER.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 1, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    -- 正常終了の場合はtrueを返却
    pResult := true;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SP1;
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > 例外発生！！！');
        DBMS_OUTPUT.PUT_LINE('-- > SQLCODE:' || SQLCODE || ' SQLERRM:' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        pResult := false;
        RETURN;
END;
-- > --------------------------
-- > アクセスログTBL更新PROCEDURE（異常終了のケース）
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.UPDATE_ACCESSLOG_ERROR(ERRMSG IN VARCHAR)
IS
BEGIN
    -- アクセスログTBLの取引ステータスを1(ここではエラー）に更新する
    UPDATE ORACLEUSER.ACCESSLOG SET OPESTS = 1, LOGMSG = ERRMSG WHERE ID = 1 AND USERID = 'testcase05';
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SP1;
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > 例外発生！！！');
        DBMS_OUTPUT.PUT_LINE('-- > SQLCODE:' || SQLCODE || ' SQLERRM:' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
END;
-- > --------------------------
-- > アクセスログTBL更新PROCEDURE（正常終了のケース）
-- > --------------------------
CREATE OR REPLACE PROCEDURE ORACLEUSER.UPDATE_ACCESSLOG_SUCCESS(SUCCESSMSG IN VARCHAR)
IS
BEGIN
    -- アクセスログTBLの取引ステータスを1(ここでは正常）に更新する
    UPDATE ORACLEUSER.ACCESSLOG SET OPESTS = 0, LOGMSG = SUCCESSMSG WHERE ID = 1 AND USERID = 'testcase05';
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO SP1;
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
        DBMS_OUTPUT.PUT_LINE('-- > 例外発生！！！');
        DBMS_OUTPUT.PUT_LINE('-- > SQLCODE:' || SQLCODE || ' SQLERRM:' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('-- > ---------------------------------------');
END;

実行結果

SQL> CALL ORACLEUSER.TESTCASE05_02_PROC_MAIN();
-- > ---------------------------------------
-- > 例外発生！！！
-- > SQLCODE:-1 SQLERRM:ORA-00001: 一意制約(ORACLEUSER.SYS_C006392)に反しています
-- > ---------------------------------------
異常終了
コールが完了しました。
SQL> SELECT * FROM ORACLEUSER.ACCESSLOG;
        ID USERID               OPECODE                  OPESTS
---------- -------------------- -------------------- ----------
LOGMSG
----------------------------------------------------------------------------------------------------
ACCESSTIME
---------------------------------------------------------------------------
         1 testcase05           XXXXXXXXXX                    1
TESTCASE05_02_PROC_SUB02で異常終了
22-12-04 23:35:26.169000
SQL> SELECT * FROM ORACLEUSER.BANKACCOUNT;
レコードが選択されませんでした。

PL/pgSQL

上記のPL/SQLのソースをSAVEPOINT実行に伴うトランザクション制御を極力合わせる前提でPL/pgSQLに移植する場合の修正内容は以下となります。

修正例２

-- > --------------------------
-- > 最上位のPROCEDURE
-- > [処理概要]
-- > アクセスログTBL登録処理を行う
-- > SAVPPOINTを実行する（例外が発生した場合はアクセスログTBL登録処理段階までレコードを戻す）
-- > 銀行口座TBLの登録処理を行うプロシージャを実行する
-- > --------------------------
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE05_02_PROC_MAIN() AS $$
DECLARE
    bResult boolean := true;
BEGIN
    -- アクセスログTBL登録処理
    INSERT INTO TEST_TRANSACTION.ACCESSLOG (ID, USERID, OPECODE, OPESTS, LOGMSG, ACCESSTIME) VALUES  (1, 'testcase05', 'XXXXXXXXXX', 9, 'アクセスログレコード事前登録', CURRENT_TIMESTAMP);
    COMMIT;
    -- SAVPPOINTを実行する（例外が発生した場合はアクセスログTBL登録処理段階までレコードを戻す）
    -- SAVEPOINT SP1; 
    BEGIN
        -- 銀行口座TBLの登録処理を行うプロシージャを実行
        CALL TEST_TRANSACTION.TESTCASE05_02_PROC(bResult);
        -- 実行結果判定
        IF bResult THEN
            RAISE INFO '正常終了';
        ELSE
            RAISE INFO '異常終了';
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE INFO '異常終了';
    END;
END;    
$$ LANGUAGE plpgsql;  
-- > --------------------------
-- > 最上位から呼び出されるPROCEDURE
-- > [処理概要]
-- > 銀行口座TBLの登録処理を行うプロシージャを２つ実行し、
-- > 正常終了した場合はアクセスログTBLの取引ステータスを0に更新し、trueを返却する
-- > 異常終了した場合はアクセスログTBLの取引ステータスを1に更新し、falseを返却する
-- > --------------------------
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE05_02_PROC(pResult IN OUT boolean) AS $$
DECLARE
    bResult boolean := true;
    EXCEPTION_DETAIL TEXT;
BEGIN
    CALL TEST_TRANSACTION.TESTCASE05_02_PROC_SUB01(bResult);
    IF NOT (bResult) THEN
        -- 異常終了の場合
        -- アクセスログTBLの取引ステータスを1(ここではエラー）に更新し、falseを返却
        CALL TEST_TRANSACTION.UPDATE_ACCESSLOG_ERROR('TESTCASE05_02_PROC_SUB01で異常終了');
        pResult := false;
        RETURN;
    END IF;
    CALL TEST_TRANSACTION.TESTCASE05_02_PROC_SUB02(bResult);
    IF NOT (bResult) THEN
        -- 異常終了の場合はfalseを返却
        -- アクセスログTBLの取引ステータスを1(ここではエラー）に更新し、falseを返却
        CALL TEST_TRANSACTION.UPDATE_ACCESSLOG_ERROR('TESTCASE05_02_PROC_SUB02で異常終了');
        pResult := false;
        RETURN;
    END IF;
    -- 正常終了の場合はtrueを返却
    CALL TEST_TRANSACTION.UPDATE_ACCESSLOG_SUCCESS('正常終了！！！！');
    pResult := true;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        -- ROLLBACK TO SP1;
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > 例外発生！！！' ;
        RAISE INFO '-- > SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
        RAISE INFO '-- > ---------------------------------------' ;
        pResult := false;
        -- RETURN;
        GET STACKED DIAGNOSTICS EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
        IF EXCEPTION_DETAIL = 'savepoint-alternative-TESTCASE05_02_PROC_SUB01' THEN
            CALL TEST_TRANSACTION.UPDATE_ACCESSLOG_ERROR_dblink_wrap('TESTCASE05_02_PROC_SUB01で異常終了');
        ELSEIF EXCEPTION_DETAIL = 'savepoint-alternative-TESTCASE05_02_PROC_SUB02' THEN
            CALL TEST_TRANSACTION.UPDATE_ACCESSLOG_ERROR_dblink_wrap('TESTCASE05_02_PROC_SUB02で異常終了');
        END IF;
        RAISE EXCEPTION USING MESSAGE='SQLATATE:' || SQLSTATE || ', SQLERRM:' || SQLERRM;
END;    
$$ LANGUAGE plpgsql;  
-- > --------------------------
-- > 銀行口座TBLの登録処理を行うサブPROCEDURE
-- > --------------------------
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE05_02_PROC_SUB01(pResult IN OUT boolean) AS $$
DECLARE
    bResult boolean := true;
BEGIN
    INSERT INTO TEST_TRANSACTION.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 0, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    -- 正常終了の場合はtrueを返却
    pResult := true;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        -- ROLLBACK TO SP1;
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > 例外発生！！！' ;
        RAISE INFO '-- > SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
        RAISE INFO '-- > ---------------------------------------' ;
        pResult := false;
        -- RETURN;
        RAISE EXCEPTION USING MESSAGE='SQLATATE:' || SQLSTATE || ', SQLERRM:' || SQLERRM, DETAIL='savepoint-alternative-TESTCASE05_02_PROC_SUB01';
END;    
$$ LANGUAGE plpgsql;  
-- > --------------------------
-- > 銀行口座TBLの登録処理を行うサブPROCEDURE（今回はここで例外を発生させる）
-- > --------------------------
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.TESTCASE05_02_PROC_SUB02(pResult IN OUT boolean) AS $$
DECLARE
    bResult boolean := true;
BEGIN
    INSERT INTO TEST_TRANSACTION.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 1, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    INSERT INTO TEST_TRANSACTION.BANKACCOUNT (USERID, ACCNUMBER, ACCTYPE, NAME, BALANCE, LASTDATE) VALUES  ('testcase05', 1, '普通', 'テストケース０５', 0, CURRENT_TIMESTAMP);
    -- 正常終了の場合はtrueを返却
    pResult := true;
    RETURN;
EXCEPTION
    WHEN OTHERS THEN
        -- ROLLBACK TO SP1;
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > 例外発生！！！' ;
        RAISE INFO '-- > SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
        RAISE INFO '-- > ---------------------------------------' ;
        pResult := false;
        -- RETURN;
        RAISE EXCEPTION USING MESSAGE='SQLATATE:' || SQLSTATE || ', SQLERRM:' || SQLERRM, DETAIL='savepoint-alternative-TESTCASE05_02_PROC_SUB02';
END;    
$$ LANGUAGE plpgsql;  
-- > --------------------------
-- > アクセスログTBL更新PROCEDURE（異常終了のケース）
-- > --------------------------
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.UPDATE_ACCESSLOG_ERROR(ERRMSG IN VARCHAR) AS $$
DECLARE
BEGIN
    -- アクセスログTBLの取引ステータスを1(ここではエラー）に更新する
    UPDATE TEST_TRANSACTION.ACCESSLOG SET OPESTS = 1, LOGMSG = ERRMSG WHERE ID = 1 AND USERID = 'testcase05';
EXCEPTION
    WHEN OTHERS THEN
        -- ROLLBACK TO SP1;
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > 例外発生！！！' ;
        RAISE INFO '-- > SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE EXCEPTION USING MESSAGE='SQLATATE:' || SQLSTATE || ', SQLERRM:' || SQLERRM;
END;    
$$ LANGUAGE plpgsql;  
-- > --------------------------
-- > アクセスログTBL更新PROCEDURE（正常終了のケース）
-- > --------------------------
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.UPDATE_ACCESSLOG_SUCCESS(SUCCESSMSG IN VARCHAR) AS $$
DECLARE
BEGIN
    -- アクセスログTBLの取引ステータスを1(ここでは正常）に更新する
    UPDATE TEST_TRANSACTION.ACCESSLOG SET OPESTS = 0, LOGMSG = SUCCESSMSG WHERE ID = 1 AND USERID = 'testcase05';
EXCEPTION
    WHEN OTHERS THEN
        -- ROLLBACK TO SP1;
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE INFO '-- > 例外発生！！！' ;
        RAISE INFO '-- > SQLSTATE:% SQLERRM:%', SQLSTATE, SQLERRM;
        RAISE INFO '-- > ---------------------------------------' ;
        RAISE EXCEPTION USING MESSAGE='SQLATATE:' || SQLSTATE || ', SQLERRM:' || SQLERRM;
END;    
$$ LANGUAGE plpgsql;  

-- > --------------------------
-- > アクセスログTBL更新PROCEDURE（異常終了のケース）※dblink利用
-- > --------------------------
CREATE OR REPLACE PROCEDURE TEST_TRANSACTION.UPDATE_ACCESSLOG_ERROR_dblink_wrap(ERRMSG IN VARCHAR) AS $$
DECLARE	
BEGIN 
	PERFORM DBLINK_CONNECT('dblink_test', 'host=ホスト名 port=ポート番号 dbname=DB名 user=ユーザ名 password=パスワード');
    PERFORM DBLINK_EXEC('dblink_test', FORMAT('CALL TEST_TRANSACTION.UPDATE_ACCESSLOG_ERROR(%L)', $1));
    PERFORM DBLINK_DISCONNECT('dblink_test');
EXCEPTION
    WHEN OTHERS THEN
        RAISE INFO '%', SQLERRM;
        PERFORM DBLINK_DISCONNECT('dblink_test');
END;	
$$ LANGUAGE plpgsql;

実行結果

postgres=# CALL TEST_TRANSACTION.TESTCASE05_02_PROC_MAIN();
INFO:  -- > ---------------------------------------
INFO:  -- > 例外発生！！！
INFO:  -- > SQLSTATE:23505 SQLERRM:重複したキー値は一意性制約"bankaccount_pkey"違反となります
INFO:  -- > ---------------------------------------
INFO:  -- > ---------------------------------------
INFO:  -- > 例外発生！！！
INFO:  -- > SQLSTATE:P0001 SQLERRM:SQLATATE:23505, SQLERRM:重複したキー値は一意性制約"bankaccount_pkey"違反となります
INFO:  -- > ---------------------------------------
INFO:  異常終了
CALL
postgres=# SELECT * FROM TEST_TRANSACTION.ACCESSLOG;
 id |   userid   |  opecode   | opests |               logmsg               |         accesstime
----+------------+------------+--------+------------------------------------+----------------------------
  1 | testcase05 | XXXXXXXXXX |      1 | TESTCASE05_02_PROC_SUB02で異常終了 | 2022-12-05 01:23:48.598754
(1 行)
postgres=# SELECT * FROM TEST_TRANSACTION.BANKACCOUNT;
 userid | accnumber | acctype | name | balance | lastdate
--------+-----------+---------+------+---------+----------
(0 行)

※PL/SQL→PL/pgSQLへの修正内容の詳細は後日追記予定です。

参考サイト

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

PL/SQLとPL/pgSQLのトランザクション制御の違いに関する検証

はじめに

PL/SQL、PL/pgSQLのDBMSおよびバージョンについて

PL/SQL

PL/pgSQL

PL/SQLとPL/pgSQLのトランザクション制御の互換性について

トランザクション制御の互換性一覧

各パターンの具体例

テーブル定義

銀行口座TBL（PostgresSQL）※Oracleは割愛

アクセスログTBL（PostgresSQL）※Oracleは割愛

DDL

パターン１ 明示的なコミット／ロールバック（EXCEPTION句無し）

パターン１ PL/SQL

パターン１ PL/pgSQL

パターン１ PL/SQL→PL/pgSQLへの修正について

パターン２ 明示的なコミット／ロールバック（EXCEPTION句有り）

パターン２ PL/SQL

パターン２ PL/pgSQL

パターン２ PL/SQL→PL/pgSQLへの修正について

パターン３ 明示的なトランザクション（BEGINコマンド内）での明示的なコミット／ロールバック

パターン３ PL/pgSQL

パターン３ PL/SQL→PL/pgSQLへの修正について

パターン４ Excption発生時の自動ロールバック

PL/SQL

PL/pgSQL

パターン４ PL/SQL→PL/pgSQLへの修正について

PL/SQLのEXCPTION句内にROLLBACKコマンドが存在する場合

PL/SQLのEXCPTION句内にROLLBACKコマンドが存在しない場合

パターン５ SAVEPOINT

PL/SQL

PL/pgSQL

パターン５ PL/SQL→PL/pgSQLへの修正について

修正例１ BEGINブロック（EXCEPTION句有り）を追加することで対応可能なケース

PL/SQL

PL/pgSQL

修正例２ 全体的に処理の修正が必要になるケース

PL/SQL

PL/pgSQL

参考サイト

パターン１　明示的なコミット／ロールバック（EXCEPTION句無し）

パターン１　PL/SQL

パターン１　PL/pgSQL

パターン１　PL/SQL→PL/pgSQLへの修正について

パターン２　明示的なコミット／ロールバック（EXCEPTION句有り）

パターン２　PL/SQL

パターン２　PL/pgSQL

パターン２　PL/SQL→PL/pgSQLへの修正について

パターン３　明示的なトランザクション（BEGINコマンド内）での明示的なコミット／ロールバック

パターン３　PL/pgSQL

パターン３　PL/SQL→PL/pgSQLへの修正について

パターン４　Excption発生時の自動ロールバック

パターン４　PL/SQL→PL/pgSQLへの修正について

パターン５　PL/SQL→PL/pgSQLへの修正について

修正例１　BEGINブロック（EXCEPTION句有り）を追加することで対応可能なケース

修正例２　全体的に処理の修正が必要になるケース