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* のような）には向いてないですね


##### 参考ページ
https://www.postgresql.jp/docs/14/sql-createtable.html

https://www.enterprisedb.com/postgres-tutorials/how-leverage-local-temporary-table-global-temporary-table-edb-postgres  

https://www.postgresql.jp/document/14/html/sql-createtrigger.html  

https://www.postgresql.jp/document/14/html/ddl-inherit.html