VIEWに対するCOPY

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の13日目のエントリです。
PostgreSQL 10全部ぬこ Advent Calendar 2017もやっと折り返し。でも大変なのはここからだぞー。

PostgreSQLの更新可能ビュー

PostgreSQL 9.3以降、ビューに対しても更新が可能になった。
これを更新可能ビューと呼んでいる。更新可能ビューを生成するクエリにはシンプルなものという制限はある(詳細はリンク参照)が、便利な機能ではある。
また、更新可能ビューに対して、INSTEAD OFという種別のトリガを設定することも可能である。これにより、ほぼテーブルに対するアクセスと同様のことが可能である。
しかし、PostgreSQL 9.6までは更新可能ビューに対する「COPY」はできなかった。

検証

以下のようなテーブルとビューを用意する。

view=# CREATE TABLE test (id int primary key, data text);
CREATE TABLE
view=# CREATE VIEW test_v AS SELECT * FROM test;
CREATE VIEW
view=# 

PostgreSQL 9.6での実行例

テーブルへもビューへもINSERTはできる。

view=# INSERT INTO test VALUES (1,'insert test');
INSERT 0 1
view=# INSERT INTO test_v VALUES (2,'insert test_v');
INSERT 0 1
view=# TABLE test_v;
 id |     data      
----+---------------
  1 | insert test
  2 | insert test_v
(2 rows)

しかし、test_vビューに対して、以下のようなファイルをCOPYしてみよう。

[nuko@localhost ~]$ cat /tmp/copy_view_without_trigger_from_file.txt 
3	copy view without trigger from file
4	copy view without trigger from file

しかし、残念ながらビューに対するCOPY操作は拒絶されエラーになってしまう。

view=# COPY test_v FROM '/tmp/copy_view_without_trigger_from_file.txt';
ERROR:  cannot copy to view "test_v"

PostgreSQL 9.6まではビューに対するCOPY FROMによるデータのCOPYはできなかった。

PostgreSQL 10での挙動

test_vビューへのINSERTは可能。これはPostgreSQL 9.6までと同様。

view=# INSERT INTO test VALUES (1,'insert test');                  
INSERT 0 1
view=# INSERT INTO test_v VALUES (2,'insert test_v');              
INSERT 0 1
view=# TABLE test_v;
 id |     data      
----+---------------
  1 | insert test
  2 | insert test_v
(2 rows)

では、COPYはどうか。PostgreSQL 9.6の検証時にも使ったファイルをCOPYしてみる。

view=# COPY test_v FROM '/tmp/copy_view_without_trigger_from_file.txt';
ERROR:  cannot copy to view "test_v"
HINT:  To enable copying to a view, provide an INSTEAD OF INSERT trigger.
view=# 

えええええー！やっぱりコピーできないじゃん！
が、HINTをよく読んでみよう。
どうやら、ビューに対して、INSTEAD OF INSERTトリガが設定されていれば動作するようだ。

トリガ関数とトリガ設定の追加

なので、トリガ関数と、test_vビューに対するトリガ設定を追加する。

view=# CREATE OR REPLACE FUNCTION test_trigger_func() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'called trigger func';
  INSERT INTO test VALUES (NEW.*);
  RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE FUNCTION

このトリガ関数test_trigger_func()は実行されると、called trigger funcというメッセージを出力して、ビューの元になってtestテーブルへINSERTするというシンプルなものだ。
(test_vビューに対してINSERTするとトリガが循環してしまう)

で、そのトリガ関数を使ったトリガ定義をtest_vビューに対して設定する。

view=# CREATE TRIGGER test_v_trigger INSTEAD OF INSERT ON test_v
  FOR EACH ROW EXECUTE PROCEDURE test_trigger_func();
CREATE TRIGGER
view=# 

トリガ設定後の挙動

こんどはこのデータを入力してみる・

[nuko@localhost ~]$ cat /tmp/copy_view_with_trigger_from_file.txt 
3	copy view with trigger from file
4	copy view with trigger from file

COPYを実行してみよう。

view=# COPY test_v FROM '/tmp/copy_view_with_trigger_from_file.txt';
NOTICE:  called trigger func
NOTICE:  called trigger func
COPY 2

ROWトリガなので、COPY元ファイルの2行分、トリガが起動されているのがわかる。

で、COPY後の結果はこうなる。

view=# TABLE test_v;
 id |               data               
----+----------------------------------
  1 | insert test
  2 | insert test_v
  3 | copy view with trigger from file
  4 | copy view with trigger from file
(4 rows)

きちんとCOPY元ファイルの内容が反映された。

おわりに

• PostgreSQL 10では INSTEAD INSERT OFトリガが設定されたビューに限定 されるが、COPY文によるロードが可能になった。
• COPY文が通るようになったとはいえ、COPY内容を反映するためには、ROWトリガ内で、ビューの元テーブルに対するINSERT文を発行する必要があるので、性能は良くならないだろう。ちょい微妙な感じ。
• これの用途は、ビュー元のテーブルにデータは反映したくないけど、COPY元のデータを使って、何か別のことをさせたい、というものなんだろうか。

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.4. Utility Commands

• Allow COPY view FROM source on views with INSTEAD INSERT triggers (Haribabu Kommi)