Oracle 自律型トランザクションを PostgreSQL に移行する

Oracle 自律型トランザクションを PostgreSQL に移行する:

データベースを移行し稼働させるには、複雑な手順と細部にわたる計画が必要になります。Amazon RDS for PostgreSQL および Amazon Aurora は PostgreSQL と互換性があり、さまざまなユースケースの取扱いに役立ちます。Oracle から PostgreSQL への移行では、Oracle 自律型トランザクションというものがよく話題になります。このブログ記事では自律型トランザクションと、この機能を PostgreSQL で実現する方法について説明します。

永続データのユースケース

データベースのトランザクションでは、すべてのステートメントがコミットまたはロールバックされるように SQL ステートメントをグループにしてまとめるメカニズムを提供します。たとえば、口座 A から口座 B へ資金を移動する場合は、以下のような手順を踏む必要があります。

• 口座 A から必要な金額を引く
• 口座 B に必要な金額を足す

このシナリオでは、両方の手順が成功しているか、両方が失敗しているかのいずれかを確認する必要があります。これは、データベースのトランザクションで両方の SQL ステートメントを実行することで可能になります。これは非常に役に立つ機能です。また多くのユースケースで必要になります。ただし、アプリケーションによっては、トランザクションが失敗してもデータを保持する機能が必要なケースもあります。

PostgreSQL と Oracle はいずれも同じトランザクション原理に従っていますが、このユースケースに対応するために、Oracle では自律型トランザクションという機能を提供します。PostgreSQL には自律型トランザクションそのままの機能はありませんが、dblink を使用することで同様の結果を得られます。

自律型トランザクションとは何でしょうか。

Oracle は自律型トランザクション機能を提供しています。この機能では、メイントランザクションのコミットやロールバックを実行することなく、サブプログラムによる SQL オペレーションの実行、さらには各 SQL オペレーションのコミットまたはロールバックが可能です。

以下のようなシナリオを想定してください。

1. INSERT トリガーのビジネスロジックに従い、重要な情報を持つ 1 行をテーブルに追加する必要があるとします。
2. SQL の INSERT クエリが実行され、必要な行を追加します。
3. 何らかの理由でトリガーが失敗すると、この INSERT を含むすべてのオペレーションがロールバックされます。

メイントランザクションが成功したかどうかに関係なく、追加を成功させる必要がある場合は、SQL の INSERT ステートメントを自律型トランザクション内でコールできます。以下に Oracle での自律型トランザクションの例を示します。

CREATE OR REPLACE PROCEDURE insert_critical_info(v_critical_info varchar2) 
IS 
PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN 
    INSERT INTO critical_info_table (critical_info) VALUES (v_critical_info); 
    commit; 
END; 
/ 

この変更により、INSERT は独立してコミットされ、メイントランザクションの成功に依存することはなくなります。

上記のようなシナリオ以外に、自律型トランザクションはアクセスの監査にも使用されます。たとえば、自律型トランザクションは、トリガーまたはストアドプロシージャ内から監査情報と共にパラメーターとしてコールされます。自律型トランザクションは、元のトランザクションが成功したかどうかには関係なくこの情報を保存し、コミットします。

PostgreSQL での自律型トランザクション

PostgreSQL は、自律型トランザクションを正式にはサポートしていません。自律型トランザクションを使用する必要がないよう、リファクタリングを行うことをお勧めします。自律型トランザクションの使用が避けられない場合、 PostgreSQL では dblink を使用することになります。PostgreSQL の dblink は、Amazon RDS および Amazon Aurora PostgreSQL でサポートされています。

dblink は新しい接続を開き、この接続を利用してクエリを実行します。dblink 経由で実行されるすべての SQL オペレーションは、メイントランザクションとは無関係に、即座にコミットされます。以下にテーブルに行を追加する例を示します。この追加は、トランザクションのコールがロールバックされてもコミットされます。

CREATE OR REPLACE FUNCTION insert_critical_info(v_critical_info TEXT) 
RETURNS void 
AS 
$BODY$ 
DECLARE 
    v_sql text; 
BEGIN 
    PERFORM dblink_connect('myconn', 
         'dbname=mydbname user=myuser password=mypassword host=127.0.0.1 port=5432'); 
    v_sql := format('INSERT INTO critical_info_table (critical_info)  
         VALUES (%L)', v_critical_info); 
    PERFORM dblink_exec('myconn', v_sql); 
    PERFORM dblink_disconnect('myconn'); 
END; 
$BODY$ 
LANGUAGE  plpgsql; 

上記の関数により dblink 経由で INSERT がコールされ、即座にオペレーションがコミットされます。

この関数の問題点は、dblink_connect のコールを行うごとに毎回データベースの認証情報を指定していることです。これを回避するには、以下のように dblink_fdw を使用してリモートサーバーとユーザーマッピングを作成します。

CREATE SERVER loopback_dblink FOREIGN DATA WRAPPER dblink_fdw  
       OPTIONS (hostaddr '127.0.0.1', dbname 'mydbname'); 
CREATE USER MAPPING FOR my_app_user SERVER loopback_dblink  
       OPTIONS (user 'myuser', password 'mypassword');

外部サーバーとユーザーマッピングが指定されました。これで以下のように dblink_connect 関数をコールできます。

CREATE OR REPLACE FUNCTION insert_critical_info(v_critical_info TEXT) 
RETURNS void 
AS 
$BODY$ 
DECLARE 
    v_sql text; 
BEGIN 
    PERFORM dblink_connect('myconn', 'loopback_dblink'); 
    v_sql := format('INSERT INTO critical_info_table (critical_info)  
                         VALUES (%L)', v_critical_info); 
    PERFORM dblink_exec('myconn', v_sql); 
    PERFORM dblink_disconnect('myconn'); 
END; 
$BODY$ 
LANGUAGE  plpgsql;

欠点

この方法により PostgreSQL で自律型トランザクションを作成できますが、欠点もあります。PostgreSQL の dblink はセッション固有です。あるセッションで開いている dblink は、別のセッションで使用できません。つまり、セッションごとに新しい DB 接続を開く必要があるため、レイテンシーが長くなります。

上記の関数の場合、同じセッションであってもコール時に毎回 1 つの接続を開くことになります。dblink は 1 つのセッション内に存在できるため、最適化の方法としては、 あえて dblink を閉じる命令を入れないことが考えられます。その代わりに、このシナリオの場合、関数で dblink がすでに開いているかどうかを確認します。すでに開いている場合、新しい dblink 接続を開かずにそちらを使用します。以下に示すのが、確認を追加した改訂版の関数です。

CREATE OR REPLACE FUNCTION insert_critical_info(v_critical_info TEXT) 
RETURNS void 
AS 
$BODY$ 
DECLARE 
    v_sql text; 
    v_con_count int; 
BEGIN 
    SELECT count(1) INTO v_con_count FROM dblink_get_connections()  
         WHERE dblink_get_connections@>'{myconn}'; 
    IF v_con_count = 0 THEN 
        PERFORM dblink_connect('myconn', 'loopback_dblink'); 
    END IF; 
     
    v_sql := format('INSERT INTO critical_info_table (critical_info)  
                         VALUES (%L)', v_critical_info); 
    PERFORM dblink_exec('myconn', v_sql); 
END; 
$BODY$ 
LANGUAGE  plpgsql;

dblink 接続は、dblink_disconnect 関数を使用して閉じなくても、セッション終了時に自動的に閉じます。

また、pgBouncer などの接続プーラーを使用して評価を行い、データベース接続数を減らすことができます。

まとめ

自律型トランザクションは役に立つ機能です。ただし、その欠点も考慮する必要があります。コードのリファクタリングを行い、自律型トランザクションなしの構文にできる場合はそちらをお勧めします。それができない場合は、このブログ記事が参考になるかと思います。

PostgreSQL dblink の情報について詳しくは、PostgreSQL ドキュメンテーションウェブサイトをご覧ください。

このブログ記事に質問やご意見がある場合は、コメント欄に自由に記入してください。

---

今回のブログ投稿者について

Yaser Raja は、アマゾン ウェブ サービスでプロフェッショナルサービスチームのシニアコンサルタントを務めています。顧客と協力し、AWS クラウドでスケーラブルかつ可用性と安全性の高いソリューションの構築に取り組んでいます。彼の専門分野は、オンプレミスのデータベースの AWS RDS や Aurora PostgreSQL への同種間または異種間の移行です。