PostgreSQLでグローバル一時表（みたいなもの）を実装する

PostgreSQLでグローバル一時表を使いたかったのでその実装のまとめです

PostgreSQLにおける一時表

• PostgreSQLの一時表はセッション内だけに存在し、セッション終了後に削除される
• CREATE GLOBAL TEMP TABLE を実行してもエラーにならないが、GLOBAL指定は無視される

実際に試してみます

db=> CREATE GLOBAL TEMP TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);
WARNING:  GLOBAL is deprecated in temporary table creation
LINE 1: CREATE GLOBAL TEMP TABLE films (
               ^
CREATE TABLE
db=> select * from films;
 code | title | did | date_prod | kind | len 
------+-------+-----+-----------+------+-----
(0 rows)

GLOBAL指定でWarningが出ますが作成はできました
接続しなおして確認するとやはり一時表は存在していませんでした

db=> select * from films;
ERROR:  relation "films" does not exist
LINE 1: select * from films;
                      ^

準備

一時表の元となるテーブルを作成します
このテーブルには実際に書き込むことはないため、UNLOGGEDを指定します

CREATE UNLOGGED TABLE films_backend (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

上記のテーブルを元表としてVIEWを作成します
これがグローバル一時表（みたいなもの）の実態となります

CREATE OR REPLACE VIEW films AS
    SELECT * FROM films_backend
;

INSERTできるようにする

VIEWのままでは書き込みができないため、INSTEAD OF TRIGGER で書き込みを行えるようにします

CREATE OR REPLACE FUNCTION films_insert()
    RETURNS TRIGGER AS $$
        BEGIN
            INSERT INTO films_temp VALUES (NEW.*);
            RETURN NEW;
            EXCEPTION WHEN undefined_table THEN
                CREATE TEMP TABLE films_temp
                    (LIKE films_backend INCLUDING ALL)
                    INHERITS (films_backend)
                    ON COMMIT PRESERVE ROWS;
                INSERT INTO films_temp VALUES (NEW.*);
                RETURN NEW;
        END;
    $$ LANGUAGE plpgsql
;

CREATE TRIGGER films_insert INSTEAD OF INSERT ON films
    FOR EACH ROW EXECUTE PROCEDURE films_insert()
;

INSTEAD OF TRIGGERとは

INSTEAD OF TRIGGERとはVIEWに対してのみ関連付けられるトリガーで、対象は行のみとなります
このトリガーでVIEWへの書き込みをfilms_backendを元表としたローカル一時表films_tempへ書き込みます
また、films_tempが未作成の場合はCREATE TEMP TABLE実行後にINSERTします

INHERITSとは

特定のテーブルを継承して新たにテーブルを作成し、継承した子テーブルは親テーブルのカラムを全て継承するというものです

ローカル一時表films_tempはfilms_backendの継承として作成します
このようにすることでfilmsビューでINSERTしたレコードが参照できるようになります

db=> insert into films  (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
INSERT 0 2
db=> select * from films;
 code  |      title      | did | date_prod  |  kind  | len 
-------+-----------------+-----+------------+--------+-----
 B6717 | Tampopo         | 110 | 1985-02-10 | Comedy | 
 HG120 | The Dinner Game | 140 |            | Comedy | 
(2 rows)

EXPLAINで見てみると、films_backendへのSELECTは子テーブルfilms_tempとのUNIONとの動きとなり、INSERTしたレコードはfilms_tempにあるがレコードが参照できるということのようです

db=> explain select * from films;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Append  (cost=0.00..15.71 rows=381 width=184)
   ->  Seq Scan on films_backend films_backend_1  (cost=0.00..0.00 rows=1 width=184)
   ->  Seq Scan on films_temp films_backend_2  (cost=0.00..13.80 rows=380 width=184)
(3 rows)

接続しなおして確認するとINSERTしたレコードは消えています
EXPLAINで見てると実際にレコードが存在していたfilms_tempも消えているのでレコードが消え、グローバル一時表っぽい動きになりました

db=> select * from films;
 code | title | did | date_prod | kind | len 
------+-------+-----+-----------+------+-----
(0 rows)

db=> explain select * from films;
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on films_backend  (cost=0.00..0.00 rows=1 width=184)
(1 row)

UPDATE,DELETEもできるようにするには

UPDATEやDELETEの実装はINSERTと同様にINSTEAD OF TRIGGERを追加することで可能ですが、INSTEAD OF TRIGGERは行のみとなるため、ローカル一時表への主キーが必要となります
（INHERITSでは主キーは継承されないのでトリガーから実行されるFUNCTION内のCREATE TEMP TABLEでの指定が必要）

最後に

挙動としてはグローバル一時表と同じようなものが実装できましたが、トリガーが行単位となるので大量のINSERT（INSERT INTO table SELECT * FROM table のような）には向いてないですね

参考ページ