目次
中国地方DB勉強会っていう控えめに言っても最高の勉強会があるんだけどそこで排他制約について教えてもらいました。
排他制約って雑に説明すると重なりを拒否する制約です。
僕は使った事なかったのですが勉強会の中で事例紹介を受けて、めっちゃ便利だったのでここでご紹介します。
どんなときに使うの？
実際にはどんなときに重なりを制御したいかというとよく使うのは次の2つ。
- 図面の重なり
- 時間の重なり
1つ目は幾何学的な図面を表現するときです。
実際にPostgreSQLは円や四角をSQLで表現できます。例えば地図上で特定の座標から半径100メートルの円を書き、その中に特定の円(場所)があればErrorにするような制約が書けます。
そもそもSQLで位置計算もめっちゃ便利なので是非使ってみてください。
そして2つ目が時間の範囲での重なりの表現です。
PostgreSQLには範囲型というのがあります。
これは時間や数値の範囲を1つのカラムで表現できます。
例えば有効期限を表現する時、一般的にはstart_atとend_atのそれぞれのカラムを作成するのではないでしょうか。
この場合、 今日が有効期限に含まれるかどうか の点と線の比較は簡単ですが 特定の期間が有効期限に含まれるかどうか という検索は非常に面倒です。
しかし範囲型を使うと 5 <@ int4range(1,7) のように <@ で範囲と範囲の含有が簡単に検索できます。このように色んな演算子を用意しているので簡単に表現できます。
実際の使い方
さて、ここまで読んで範囲型めっちゃ便利！！と思っていただけたと思います。
そこで例えば会議室の予約システムを作る時に範囲型を使うと次のように表現できます。
CREATE TABLE schedule
(
schedule_id SERIAL PRIMARY KEY NOT NULL,
room_name TEXT NOT NULL,
reservation_time tsrange NOT NULL
);
demo=# SELECT * FROM schedule;
schedule_id | room_name | reservation_time
-------------+-------------+-----------------------------------------------
1 | soudai_room | ["2017-04-16 11:30:00","2017-04-16 12:00:00")
4 | soudai_room | ["2017-04-16 12:00:00","2017-04-16 12:30:00")
5 | soudai_room | ("2017-04-16 12:30:00","2017-04-16 12:40:00")
8 | soudai_room | ["2017-04-16 14:30:00","2017-04-16 16:00:00")
(4 行)
demo=# SELECT * FROM schedule
WHERE reservation_time @> '2017-04-16 15:30:00'::timestamp;
schedule_id | room_name | reservation_time
-------------+-------------+-----------------------------------------------
8 | soudai_room | ["2017-04-16 14:30:00","2017-04-16 16:00:00")
(1 行)
demo=# SELECT * FROM schedule
WHERE
reservation_time && '[2017-04-16 11:45:00, 2017-04-16 12:10:00]'::tsrange;
schedule_id | room_name | reservation_time
-------------+-------------+-----------------------------------------------
1 | soudai_room | ["2017-04-16 11:30:00","2017-04-16 12:00:00")
4 | soudai_room | ["2017-04-16 12:00:00","2017-04-16 12:30:00")
(2 行)しかしここで大きな課題として会議室は1つしかないので会議室の予約時間(reservation_time)が被ったらErrorになって欲しいですよね。
そこでお待たせしました排他制約の出番です。
EATE TABLE schedule
(
schedule_id SERIAL PRIMARY KEY NOT NULL,
room_name TEXT NOT NULL,
reservation_time tsrange NOT NULL,
EXCLUDE USING GIST (reservation_time WITH &&) ←排他制約を追加
);
demo=# SELECT * FROM schedule;
schedule_id | room_name | reservation_time
-------------+-------------+-----------------------------------------------
1 | soudai_room | ["2017-04-16 11:30:00","2017-04-16 12:00:00")
4 | soudai_room | ["2017-04-16 12:00:00","2017-04-16 12:30:00")
5 | soudai_room | ("2017-04-16 12:30:00","2017-04-16 12:40:00")
8 | soudai_room | ["2017-04-16 14:30:00","2017-04-16 16:00:00")
(4 行)
demo=# INSERT INTO schedule
(room_name, reservation_time)
VALUES
('soudai_room', '[2017-04-16 15:30, 2017-04-16 17:00)');
ERROR: conflicting key value violates exclusion constraint "schedule_reservation_time_excl"
DETAIL: Key (reservation_time)=(["2017-04-16 15:30:00","2017-04-16 17:00:00")) conflicts with existing key (reservation_time)=(["2017-04-16 14:30:00","2017-04-16 16:00:00")).
demo=# INSERT INTO schedule
(room_name, reservation_time)
VALUES
('soudai_room', '[2017-04-16 17:30, 2017-04-16 18:00)');
INSERT 0 1
ちゃんと弾いてくれてますね！
これでUPDATEの設計にしてロックがアプリケーションパフォーマンスのボトルネックになったり、INSERTで表現する際のファントムーリード起因のbugとかを防ぐ事が出来ます。
まとめ
RDBに入っているデータは常に正しいといえる状態は精神衛生上もアプリケーションのロジック的にも平和なので排他制約使えるシーンは色々とあるな！と考えています。
とは言え僕も今回知ったばかりでまだまだ実務で使っていないので面白い使い方を模索したいと思います。
投稿日：April 16th 2017
元記事：http://soudai.hatenablog.com/entry/2017/04/16/152905