別セッションから一時表の内容を定義を変更せずに確認する方法 #MySQL

概要

データベースの一時表（一時テーブル、temporary table）は、使いこなすととても便利な機能なのですが、唯一扱いづらいところは、別セッションからレコードが覗けないというところです。

この難点のためだけに一時表を使わないのは勿体無いのでどうにかしたいと思います。

ビューは使わない

タイトルにある「定義を変更せず」というのは、一時表の代わりに実テーブルとビューを組み合わせて一時表モドキにするという手法は用いないということです。

やり方

それでは手順を説明します。サンプルのSQLはOracleで実行できる形式とします。
（MySQLやPostgreSQLでも応用可能です）

サンプルテーブル

今回の説明用のサンプルテーブル定義は以下の通りです。

CREATE GLOBAL TEMPORARY TABLE USER1.TMPTBL (
  "ID"   NUMBER(3,0) NOT NULL ENABLE,
  "NAME" CHAR(128)   NOT NULL ENABLE,
   CONSTRAINT TMPTBL_IDX_00 PRIMARY KEY ("ID")
) ON COMMIT PRESERVE ROWS

ログテーブルの作成

一時表の内容は当然他のセッションでは見れませんので、その内容のコピーを記録するログテーブルを作成します。

CREATE TABLE USER1.TMPTBL_LOG (
  "SESSIONID" NUMBER DEFAULT SYS_CONTEXT('USERENV', 'SESSIONID') NOT NULL ENABLE,
  "ID"        CHAR(3)   NOT NULL ENABLE,
  "NAME"      CHAR(128) NOT NULL ENABLE,
  CONSTRAINT TMPTBL_LOG_IDX_00 PRIMARY KEY ("SESSIONID", "ID")
);

一時表と同じテーブル定義にSESSIONID列を追加します。
名前からわかる通りセッションIDを格納します。セッションIDは同時には同じものが存在しない事が保証されますが、時間があくと同じIDを振られる可能性があるので、長時間保存しておく必要がある場合はさらに更新時刻も主キーに追加する必要があります。
（特にPostgreSQLはセッションIDの代わりにサーバプロセスIDを使用するので更新時刻は必須です）

トリガーの追加

一時表（USER1.TMPTBL）にトリガーを追加します。

CREATE OR REPLACE TRIGGER USER1.TMPTBL_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON USER1.TMPTBL FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO USER1.TMPTBL_LOG(ID,NAME) VALUES(:NEW.ID,:NEW.NAME);
  ELSIF UPDATING THEN
    UPDATE USER1.TMPTBL_LOG SET
      ID = :NEW.ID
      , NAME = :NEW.NAME
    WHERE
      SESSIONID = SYS_CONTEXT('USERENV', 'SESSIONID')
      AND ID = :OLD.ID;
  ELSE
    DELETE FROM
      USER1.TMPTBL_LOG
    WHERE
      SESSIONID = SYS_CONTEXT('USERENV', 'SESSIONID')
      AND ID = :NEW.ID;
  END IF;
END;

これで一時表にINSERT、UPDATE、DELETEが発生した際にログテーブルに内容が書き込まれるので、別セッションから内容を確認できる様になりました。

他のデータベースの場合

MySQL

MySQLの場合は、SYS_CONTEXT('USERENV', 'SESSIONID')をCONNECTION_ID()に変更してセッションIDを取得します。

PostgreSQL

PostgreSQLの場合は、SYS_CONTEXT('USERENV', 'SESSIONID')をpg_backend_pid()に変更してサーバプロセスIDを取得します。
前述の注意通りサーバプロセスIDはそこそこ同じIDが割り当てられる可能性があるので更新時刻も主キーに含めて一意制約違反が発生しない様に注意が必要です。