はじめに

これは、PostgreSQL Advent Calendar 2020の4日目の記事となります。

2018年からサーバーリプレース作業でデータベースを Oracle 11g から PostgreSQL 9.6 に移行作業をし、コロナ禍で出張が延期になるなど影響がありましたが、1年(月1〜3拠点)かけて全国25箇所の導入作業が2020年9月に完了しました。
OracleからPostgreSQL移行について

下記の問題がまだ解決できていません。どこかの設定値を変えたらいいとかアドバイスがあればよろしくお願いします。
【PostgreSQL】テーブルのインデックスが壊れた話

今回はSELECT内で他のテーブルを更新するという、一般的なデータベース使用者には意味不明かもな記事となります。

自律型トランザクション

ハンディ端末を使用したアプリーケーションがあり、読み込んだバーコードとの照合処理を行う仕組みがあります。マスタテーブルにSQLを埋め込んで動的に照合方法を変更でき、画面に表示させるメッセージもメッセージマスタに登録しておき、マスタテーブルに表示するメッセージコードを指定しています。

Oracle 11gの時にメッセージを動的に変更したいという要望があり試行錯誤した結果、SQLのユーザー関数を作成して自律型トランザクション（PRAGMA AUTONOMOUS_TRANSACTION)を使用して、メッセージマスタの指定コードのメッセージを書き換えることで実現させました。

移行作業をしている際に、PostgreSQLには自律型トランザクションをサポートしていないので、この方法は使えない旨を伝えたところ、その運用は今は使用していないので大丈夫との回答を得たので何もしませんでした。
今月になって別のユーザーからメッセージを動的に変更したいという要望を受け、再度調査し直したところ下記の記事を見つけました。

PostgreSQL は、自律型トランザクションを正式にはサポートしていません。自律型トランザクションを使用する必要がないよう、リファクタリングを行うことをお勧めします。自律型トランザクションの使用が避けられない場合、 PostgreSQL では dblink を使用することになります。(中略) dblink は新しい接続を開き、この接続を利用してクエリを実行します。dblink 経由で実行されるすべての SQL オペレーションは、メイントランザクションとは無関係に、即座にコミットされます。
Oracle 自律型トランザクションを PostgreSQL に移行する

dblink

dblinkは、データベースセッション内から他のPostgreSQLデータベースへの接続をサポートするモジュールとなります。
https://www.postgresql.jp/document/9.6/html/contrib-dblink-function.html

PostgreSQLの標準機能なので下記のコマンドでインストールすることができる。

CREATE EXTENSION dblink;

リモートDBが持つテーブルにローカルライクにアクセスできるが、下記などの制約がある。

リモート表をSELECTする場合、データを全件ローカルに持ってくるためデータ転送が多くなる。
リモートDBのテーブルや行をロックしない。整合性を担保するためには二相コミットの実装が必要。
独特なSQL書式

postgres_fdw

dblinkの後継機能となります。
https://www.postgresql.jp/document/9.6/html/postgres-fdw.html

クエリはオーソドックスな書式で通用するが、結合、ソート、LIMIT、集約などがリモートではなくローカルで行われる。
※WHERE句によるシンプルな絞り込みはリモートで行ってくれる。

PostgreSQLの標準機能なので下記のコマンドでインストールすることができる。

CREATE EXTENSION postgres_fdw；

実装

今回はユーザー関数を作成し、dblinkを使用してメッセージマスタを対象IDのメッセージを更新します。

update_message

CREATE OR REPLACE FUNCTION "db_test"."update_message" (in varchar, in varchar) RETURNS varchar AS
$BODY$
DECLARE
    slink CHARACTER VARYING(1000);
    sSql CHARACTER VARYING(1000); 
BEGIN
    slink := 'host=127.0.0.1 user=fuga password=hoge dbname=db_test';
    sSql := format('update mst_message set message = %L where id=%L', $2, $1);

    EXECUTE format('select * from dblink(%L, %L) tt(updated text)', slink, sSql);
    RETURN $2;
END;
$BODY$
LANGUAGE 'plpgsql'

確認

変更前

select * from mst_message
where id = 'MES0001'

id	message
MES0001	ラベル

実行

SELECT内で他のテーブルを更新する。

select update_message('MES0001','ADR-123')

変更後

select * from mst_message
where id = 'MES0001'

id	message
MES0001	ADR-123

最後に

今回、dblinkやpostgres_fdwの機能を知ることができて良かったです。
他のPostgreSQLデータベースへの接続ができるので、アイディア次第では面白いことができそうです。

PostgreSQLは自律型トランザクションをサポートしていないからと安易に諦めてはいけないですね。