クリスマスに備えて テーブルマジックを 身につけよう(仮)

このエントリは、PostgreSQL Advent Calendar 2013の4日目の記事にゃ。

なんともう世の中は12月！

ということで、あと数週間でクリスマス、そして年末年始ですね。
この時期、パーティーや飲み会も多いことでしょう。
そんな時に、ちょっとした余興芸があると何かと捗るかもしれません。
そう、例えばテーブルマジックとかね。
とはいっても、今回紹介するのはPostgreSQLのテーブルを使うものですがｗ

実演

こんなテーブルがあります。5件ほどレコードが入ってますね。

magic=# SELECT * FROM test ORDER BY id;
 id |  data  |         ts          
----+--------+---------------------
  1 | まどか | 2013-12-22 19:00:00
  2 | ほむら | 2013-12-23 05:00:00
  3 | マミ   | 2013-12-23 07:30:00
  4 | さやか | 2013-12-23 21:00:00
  5 | 杏子   | 2013-12-24 09:00:00
(5 rows)

マミさんを削除します。

magic=# DELETE FROM test WHERE id = 3;
DELETE 1
magic=# SELECT * FROM test ORDER BY id;
 id |  data  |         ts          
----+--------+---------------------
  1 | まどか | 2013-12-22 19:00:00
  2 | ほむら | 2013-12-23 05:00:00
  4 | さやか | 2013-12-23 21:00:00
  5 | 杏子   | 2013-12-24 09:00:00
(4 rows)

マミさん、消えちゃいました。(´・ω・`)
でも、もう一回DELETEすると

magic=# DELETE FROM test WHERE id = 3;
DELETE 0
magic=# SELECT * FROM test ORDER BY id;
 id |  data  |         ts          
----+--------+---------------------
  1 | まどか | 2013-12-22 19:00:00
  2 | ほむら | 2013-12-23 05:00:00
  3 | マミ   | 2013-12-23 07:30:00
  4 | さやか | 2013-12-23 21:00:00
  5 | 杏子   | 2013-12-24 09:00:00
(5 rows)

なぜか復活しています。謎です。
別に映画版があったから復活したわけじゃないです(いみふ

次。DELETEじゃなくてTRUNCATEしてみます。

magic=# TRUNCATE TABLE test;
TRUNCATE TABLE
magic=# SELECT * FROM test ORDER BY id;
 id |  data  |         ts          
----+--------+---------------------
  1 | まどか | 2013-12-22 19:00:00
  2 | ほむら | 2013-12-23 05:00:00
  3 | マミ   | 2013-12-23 07:30:00
  4 | さやか | 2013-12-23 21:00:00
  5 | 杏子   | 2013-12-24 09:00:00
(5 rows)

TRUNCATEしま・・・してませんｗ

最後、テーブルごとDROPしてみます。

magic=# DROP TABLE test;
DROP TABLE
magic=# SELECT * FROM test ORDER BY id;
 id |  data  |         ts          
----+--------+---------------------
  1 | まどか | 2013-12-22 19:00:00
  2 | ほむら | 2013-12-23 05:00:00
  3 | マミ   | 2013-12-23 07:30:00
  4 | さやか | 2013-12-23 21:00:00
  5 | 杏子   | 2013-12-24 09:00:00
(5 rows)

テーブルごと削除・・・できていませんｗ

これでPostgreSQL女子にもモテモテ(死語)

たねあかし

ポルなんとかさんがぽろっと漏らしているように、これはトリガを仕掛けに使っています。
まずは、簡単なほうから。

TRUNCATEの場合

PostgreSQL 9.1からTRUNCATE契機でもトリガが設定できるようになりました。
http://postgresql.jp/document/9.1/html/trigger-definition.html
なので、こんな感じでTRUNCATEトリガを設定しておきます。

--
-- truncate trigger functions
--
CREATE OR REPLACE FUNCTION truncate_before_trig_func() RETURNS trigger AS $$
DECLARE
BEGIN
  COPY test TO '/tmp/test.data';
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION truncate_after_trig_func() RETURNS trigger AS $$
BEGIN
  COPY test FROM '/tmp/test.data';
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER truncate_before_trig BEFORE truncate ON test FOR STATEMENT
EXECUTE PROCEDURE truncate_before_trig_func();

CREATE TRIGGER truncate_after_trig AFTER truncate ON test FOR STATEMENT
EXECUTE PROCEDURE truncate_after_trig_func();

やってることは、BEFORE文トリガでテーブルの内容をCOPY TOでエクスポートして、TRUNCATEが成功した後、AFTER文トリガで(空化された)テーブルに、COPY FROMで削除前のデータをコピーしているだけです。
実にチープですね。

DROP TABLEの場合

次はDROP TABLEのときの対応。
PostgreSQL 9.3からDDL実行時にトリガが設定できる、イベントトリガがサポートされました。
これを使って、こんな感じでトリガを仕掛けておきます。

--
-- drop event trigger functions
--
CREATE OR REPLACE FUNCTION drop_before_trig_func() RETURNS event_trigger AS $$
BEGIN
  IF ( tg_tag = 'DROP TABLE' ) THEN
    COPY test TO '/tmp/test_drop.data';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION drop_after_trig_func() RETURNS event_trigger AS $$
BEGIN
  IF ( tg_tag = 'DROP TABLE' ) THEN
    CREATE TABLE test (id int, data text, ts timestamp);
    COPY test FROM '/tmp/test_drop.data';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER drop_before_trig ON ddl_command_start
   WHEN TAG IN ('DROP TABLE')
   EXECUTE PROCEDURE drop_before_trig_func();

CREATE EVENT TRIGGER drop_after_trig ON ddl_command_end
   WHEN TAG IN ('DROP TABLE')
   EXECUTE PROCEDURE drop_after_trig_func();

基本的にやってることはTRUNCATEトリガとあんまり変わりません。
その処理の前にDDLトリガ固有の処理と、CREATE TABLEで元のテーブルと同じ構造のテーブルを作成しているだけです。
"IF ( tg_tag = 'DROP TABLE' ) THEN"の文ですが、これをい置かないと、CREATE〜文や、ALTER〜文でもDDLトリガは容赦なく起動して下手をするとコケるまで無限にDDLトリガが起動しちゃうためです。

DELETEの場合

実は白状するとこれが一番苦労した＆妥協の産物です。
本当は、TRUNCATE/DROP TABLEと同じように、削除したように見えるけどレコードは削除されていない、というのを文トリガと行トリガの組み合わせでやってみたかったのですが、どうにもうまくいかない。
(もちろん、DELETEでもTRUNCATEと同じやりかたでやれば、見かけ上は同じことは出来るのだけど)

ということで、2回DELETEしたら1回めのDELETEしたレコードが追加されるという、ちょっと変化球っぽいトリガを設定してみた。

--
-- delete trigger functions
--
CREATE OR REPLACE FUNCTION delete_after_statement_trig_func() RETURNS trigger AS $$
BEGIN
  RAISE DEBUG 'delete_before_statement_trig_func start';
  COPY test_tmp TO '/tmp/test.data';
  COPY test FROM '/tmp/test.data';
  TRUNCATE TABLE test_tmp;
  RAISE DEBUG 'delete_before_statement_trig_func end';
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delete_before_row_trig_func() RETURNS trigger AS $$
BEGIN
   INSERT INTO test_tmp VALUES ( OLD.* );
   RETURN OLD;
END;
$$ LANGUAGE plpgsql;

簡単に説明すると、BEFORE行トリガで削除対象のレコードを、削除対象と同じ構造を持つ別のテーブル(test_tmp)にINSERTする。
そして文AFTERトリガでtest_tmpテーブルから、一旦COPY TOでエクスポートし、そしてエクスポートしたファイルをtestテーブルにCOPY FROMで挿入しているというもの。
え？なんでいちいちCOPY TO/COPY FROMをしているか？
それは、PL/pgSQLでINSERT INTOコマンドが使えないからだッ！
(PL/pgSQLではINTOキーワードはレコード変数に代入するための指定と認識されてしまう・・・)
ということで、こういう回りくどいことになる。

で、そもそも何で最初のDELETE終了後に復活しないのかというと、BEFORE行トリガで実行される関数内でINSERT文でテーブルを入力しても、それはコミットされていないので、AFTER文トリガ時点で、そのテーブルを参照しても挿入された行は見えないから。
まあ、当たり前といえばそうなんだが・・・(´・ω・｀)
最初のDELETEがコミットされた時点で初めてtest_tmpに挿入されたレコードは可視となる。
なので、2回目のDELETE時点でtest_tmpテーブルのレコードはtestテーブルににCOPY TO/COPY FROMされるから、2回目のDELETEで復活したように見える、というわけです。

補足

え、DELETEのときにはBEFORE行トリガでNULLを返却するようにすればいいだけじゃないの？
というツッコミがあるとは思うけど、そうしたら

magic=# SELECT * FROM test ORDER BY id;
 id |  data  |         ts          
----+--------+---------------------
  1 | まどか | 2013-12-22 19:00:00
  2 | ほむら | 2013-12-23 05:00:00
  3 | マミ   | 2013-12-23 07:30:00
  4 | さやか | 2013-12-23 21:00:00
  5 | 杏子   | 2013-12-24 09:00:00
(5 rows)

magic=# DELETE FROM test WHERE id = 3;
DELETE 0
magic=# SELECT * FROM test ORDER BY id;
 id |  data  |         ts          
----+--------+---------------------
  1 | まどか | 2013-12-22 19:00:00
  2 | ほむら | 2013-12-23 05:00:00
  3 | マミ   | 2013-12-23 07:30:00
  4 | さやか | 2013-12-23 21:00:00
  5 | 杏子   | 2013-12-24 09:00:00
(5 rows)

最初のDELETEが"DELETE 0"になっちゃって、つまんないでしょー！
ここで"DELETE 2"を出すために無駄に頑張ったのに。

ちなみにTRUNCTAEの場合は文トリガなんで、NULL返却するしかない。
なので、何もしないとフツーにTRUNCATEされたままになるはず。

おわりに

他の方々が真面目に、かつ為になるエントリを書いているのに俺ときたら・・・
まあ、いつものぬこクオリティなので許してつかーさい。