« 己の足らざるところを自覚すること | トップページ | Currency型の掘り下げをしてみる »

2014.02.10

パーティショニングでできるこんなこと(履歴化)

パーティショニング技法は物理テーブルを裏側で分割しておくことで、検索性能をあげることが期待できる。
PostgreSQLでのパーティショニングについては例によってLet's Postgresに詳しい記事があるので、ちょっとそこにはないサンプルを実例的に紹介してみたい。

よくあるパーティショニングについてはたとえば注文履歴を月ごとのテーブルに分割しておくとか、顧客IDのグループごとに分割するとか、INSERT処理の段階で行うことが良く事例として出てくる。
それを今回はUPDATEトリガーで業務データの性格が変わったところで履歴子テーブルに移動させる、という形式でのパーティションの実装を試してみた。
そもそものこのテーブルの設計が正しいのかとかいったツッコミはあろうかと思うが、こういうパーティションも実装可能、という例として受け取ってもらえればこれ幸い。

【対象となるもの】
-注文明細テーブル:order_details
-注文明細仕掛テーブル:order_details_process
-注文明細履歴テーブル: order_details_history

【履歴化のタイミング】
order_statusが"9"になったとき。order_details_processテーブルからorder_details_historyテーブルに実データが移動する。
まずはテーブルの準備を行う。

--注文明細テーブル --DROP TABLE order_details_history; --DROP TABLE order_details_process; --DROP TABLE order_details; --DROP SEQUENCE order_details_order_id_seq; CREATE TABLE order_details ( order_id serial NOT NULL , sku_code varchar(20) NOT NULL DEFAULT ' ' , order_qty integer NOT NULL DEFAULT 0 , shipped_qty integer NOT NULL DEFAULT 0 , order_status integer NOT NULL DEFAULT 0 --0:Creared , 1:In Process , 9:Completed , CONSTRAINT order_details_pk PRIMARY KEY (order_id) ); --注文明細仕掛テーブル CREATE TABLE order_details_process() INHERITS (order_details); --注文明細履歴テーブル CREATE TABLE order_details_history( CHECK (order_status = 9) ) INHERITS (order_details);
続いてパーティショニングを行う実体であるトリガー関数を作成する。 まずはデータ作成時には仕掛テーブルに保存する。
CREATE OR REPLACE FUNCTION create_order_details() RETURNS trigger AS $BODY$ BEGIN IF NEW.order_status = 9 THEN INSERT INTO order_details_history VALUES (NEW.*) ; RETURN NULL; ELSE INSERT INTO order_details_process VALUES (NEW.*) ; RETURN NULL; END IF; END; $BODY$ LANGUAGE plpgsql VOLATILE ;
また、order_statusが9になると仕掛テーブルから履歴テーブルへとデータを移動させるトリガー関数を作成。
CREATE OR REPLACE FUNCTION set_order_details()
RETURNS trigger AS
$BODY$
BEGIN
IF (NEW.order_status = 9 AND OLD.order_status <> 9 ) THEN
DELETE FROM order_details_process WHERE order_id = OLD.order_id;
INSERT INTO order_details_history VALUES (NEW.*) ;
RETURN NULL;
ELSIF (NEW.order_status <> 9 AND OLD.order_status = 9 ) THEN
DELETE FROM order_details_history WHERE order_id = OLD.order_id;
INSERT INTO order_details_process VALUES (NEW.*) ;
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
最後にインサートトリガーと更新トリガーを設定する。 この際にインサートトリガーは親テーブルであるorder_details側で良いのだが、更新トリガーは両方の子テーブルに設定しなければいけない。
CREATE TRIGGER ti_order_details
BEFORE INSERT
ON order_details
FOR EACH ROW
EXECUTE PROCEDURE create_order_details()
;
CREATE TRIGGER tu_order_details_pro
BEFORE UPDATE
ON order_details_process
FOR EACH ROW
EXECUTE PROCEDURE set_order_details()
;
CREATE TRIGGER tu_order_details_his
BEFORE UPDATE
ON order_details_history
FOR EACH ROW
EXECUTE PROCEDURE set_order_details()
;
テストデータの登録と両テーブルへのデータ登録状況確認。
INSERT INTO order_details(sku_code,order_qty) VALUES ('ABC', 5); INSERT INTO order_details(sku_code,order_qty) VALUES ('BBB', 1); INSERT INTO order_details(sku_code,order_qty) VALUES ('CCC', 2); INSERT INTO order_details(sku_code,order_qty) VALUES ('DDD', 15); INSERT INTO order_details(sku_code,order_qty) VALUES ('EEE', 4); INSERT INTO order_details(sku_code,order_qty) VALUES ('FFF', 3); INSERT INTO order_details(sku_code,order_qty) VALUES ('ABC', 5); INSERT INTO order_details(sku_code,order_qty) VALUES ('BBBB', 5); INSERT INTO order_details(sku_code,order_qty) VALUES ('BBBB', 5); INSERT INTO order_details(sku_code,order_qty) VALUES ('EEE', 1); INSERT INTO order_details(sku_code,order_qty) VALUES ('FFF', 1); INSERT INTO order_details(sku_code,order_qty,shipped_qty, order_status) VALUES ('FFF',1, 1,9); -- SELECT * FROM order_details; --(12件検索される) SELECT * FROM order_details_process; --(11件検索される) SELECT * FROM order_details_history; --(1件検索される)
ここまでで準備は完了。 では実際に更新処理を実施し、想定どおりに挙動するのか実験してみよう。

まずは履歴に移動しない、仕掛状態での更新結果。


UPDATE order_details
SET shipped_qty = 2, order_status = 2
WHERE order_id = 4;
--
SELECT * FROM order_details; --(12件検索される)
SELECT * FROM order_details_process; --(11件検索される)
SELECT * FROM order_details_history; --(1件検索される)

初期状態とデータの配置状態に変化はない。

続いてこの注文を完了させて、履歴テーブルに実体を移動させてみる。


UPDATE order_details
SET shipped_qty = 15, order_status = 9
WHERE order_id = 4;
--
SELECT * FROM order_details; --(12件検索される)
SELECT * FROM order_details_process; --(10件検索される)
SELECT * FROM order_details_history; --(2件検索される)

実際にデータが履歴テーブルに移動したことが確認できた。

また完了したはずの注文でも諸事情があって、注文仕掛中に戻した場合はどうなるのか?
仕掛テーブルに戻って欲しいのだが……


UPDATE order_details
SET shipped_qty = 5, order_status = 2
WHERE order_id = 4;
--
SELECT * FROM order_details; --(12件検索される)
SELECT * FROM order_details_process; --(11件検索される)
SELECT * FROM order_details_history; --(1件検索される)

無事に仕掛側に戻ることが確認できた。

というわけで、実験成功。

トリガー設定のところは注意が必要である。
親テーブルに更新トリガーを設定しても、親テーブルにはパーティショニングの結果として実はデータ実体がないため、更新トリガーは不発に終わる。
正確には、


  1. 親テーブルへの更新命令発行
  2. パーティショニングにより子テーブルへのデータ操作発生
  3. 親テーブルの更新トリガー起動すると思いきや
  4. 親テーブルの更新データはないため、トリガー不発
  5. 子テーブルの更新処理が確定
  6. 履歴テーブルへの移動は発生しない
という現象が発生する。
更新トリガーを下記のように親テーブルのみにして同じような更新を実行すると、データはずっとorder_details_process内で更新されていることから上記のように推定している。

DROP TRIGGER tu_order_details_pro;

DROP TRIGGER tu_order_details_his;

CREATE TRIGGER tu_order_details

BEFORE UPDATE

ON order_details

FOR EACH ROW

EXECUTE PROCEDURE set_order_details()

;

当然のことながら、せっかくパーティショニングを実現できているので、仕掛中注文データを扱うSQLでは必ずorder_statusを検索条件に入れるようにしたいところである。

|

« 己の足らざるところを自覚すること | トップページ | Currency型の掘り下げをしてみる »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/59074781

この記事へのトラックバック一覧です: パーティショニングでできるこんなこと(履歴化):

« 己の足らざるところを自覚すること | トップページ | Currency型の掘り下げをしてみる »