この記事は PostgreSQL Advent Calendaer 2018 の 21 日目です。昨日の記事は @kaigai さんに代わって @snaga さんの「機械学習ライブラリ Apache MADlib で決定木を使って Kaggle の Titanic を解く 」でした。
この記事では、PostgreSQL のマイナーな contrib モジュールについて紹介します。
contrib モジュールとは
contrib モジュール とは、限られたユーザ向けだったり、実験的すぎたりして、PostgreSQL 本体に取り込まれていないモジュールのことです。ちなみに contrib は contribution の略で、ユーザから寄贈されたモジュールであるのに由来するものだからでしょう。
contrib モジュールは、ソースコードでは contrib ディレクトリに格納されています。単に make && make install を実行しただけではインストールされません。インストールするには contrib ディレクトリ内で make && make install を実行するか、make world && make install-world を実行してドキュメントも含めてインストールする必要があります。
RPM パッケージでは contrib サブパッケージ (バージョン 11 であれば postgresql11-contrib パッケージ) として提供されています。
contrib モジュールの歴史
contrib モジュールの歴史は古く、PostgreSQL として最初のバージョンである 6.0 から存在しています。各バージョンに含まれる contrib モジュールと件数は以下のとおりです。contrib ディレクトリ内のディレクトリにつき 1 件として数えています。
6.0 6.1 6.2 6.3 6.4 6.5 7.0 7.1 7.2 7.3 7.4 8.0 8.1 8.2 8.3 8.4 9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11
pginterface *----*----*----*----*----*
datetime *----*----*----*----*----*----*
soundex *----*----*----*----*----*----*----*
array *----*----*----*----*----*----*----*----*----*----*----*
string *----*----*----*----*----*----*----*----*----*----*----*
int8 *----*----*----*----*
linux *----*----*----*----*----*
spi *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
ip_and_mac *
sequence *
unixdate *----*----*----*----*
miscutil *----*----*----*----*----*----*----*----*
userlock *----*----*----*----*----*----*----*----*----*
apache_logging *----*----*
os2client *----*----*----*
findoidjoins *----*----*----*----*----*----*----*
noupdate *----*----*----*----*----*----*----*
fulltextindex *----*----*----*----*----*----*----*----*
isbn_issn *----*----*----*----*----*----*----*----*
mSQL-interface *----*----*----*----*----*----*----*----*
earthdistance *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
lo *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
tools *----*----*----*----*----*----*
vacuumlo *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
bit *
likeplanning *
odbc *
pgbench *----*----*----*----*----*----*----*----*----*----*----*----*----*----*
pg_controldata *----*
pg_resetxlog *----*
retep *----*
pg_logger *----*----*----*
rserv *----*----*----*
ipc_check *----*----*----*----*
mysql *----*----*----*----*
pg_dumplo *----*----*----*----*
mac *----*----*----*----*----*
tips *----*----*----*----*----*
cube *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
intarray *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
pgcrypto *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
seg *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
start-scripts *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
rtree_gist *----*----*----*
tsearch *----*----*----*
xml *----*----*----*
dbase *----*----*----*----*
oracle *----*----*----*----*
chkpass *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
btree_gist *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
dblink *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
fuzzystrmatch *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
oid2name *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
pgstattuple *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
dbsize *----*----*
reindexdb *----*----*
adddepend *----*----*----*
dbmirror *----*----*----*
intagg *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
ltree *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
tablefunc *----*----*----*----*----*----*----*----*----*----*----*----*----*----*----*
pg_autovacuum *----*
tsearch2 *----*----*----*----*----*----*----*----*----*----*----*----*
pg_trgm *----*----*----*----*----*----*----*----*----*----*----*----*----*
xml2 *----*----*----*----*----*----*----*----*----*----*----*----*----*
pg_buffercache *----*----*----*----*----*----*----*----*----*----*----*----*
adminpack *----*----*----*----*----*----*----*----*----*----*----*
hstore *----*----*----*----*----*----*----*----*----*----*----*
isn *----*----*----*----*----*----*----*----*----*----*----*
pg_freespacemap *----*----*----*----*----*----*----*----*----*----*----*
pgrowlocks *----*----*----*----*----*----*----*----*----*----*----*
sslinfo *----*----*----*----*----*----*----*----*----*----*----*
test_parser *----*----*----*----*----*----*
dict_int *----*----*----*----*----*----*----*----*----*----*
dict_xsyn *----*----*----*----*----*----*----*----*----*----*
pageinspect *----*----*----*----*----*----*----*----*----*----*
pg_standby *----*----*----*----*----*----*----*----*----*----*
uuid-ossp *----*----*----*----*----*----*----*----*----*----*
auto_explain *----*----*----*----*----*----*----*----*----*
btree_gin *----*----*----*----*----*----*----*----*----*
citext *----*----*----*----*----*----*----*----*----*
pg_stat_statements *----*----*----*----*----*----*----*----*----*
pg_archivecleanup *----*----*----*----*
pg_upgrade *----*----*----*----*
pg_upgrade_support *----*----*----*----*
passwordcheck *----*----*----*----*----*----*----*----*
unaccent *----*----*----*----*----*----*----*----*
dummy_seclabel *----*----*----*
pg_test_fsync *----*----*----*
auth_delay *----*----*----*----*----*----*----*
file_fdw *----*----*----*----*----*----*----*
sepgsql *----*----*----*----*----*----*----*
pg_test_timing *----*----*
tcn *----*----*----*----*----*----*
pg_xlogdump *----*
worker_spi *----*
postgres_fdw *----*----*----*----*----*
test_shm_mq *
pg_prewarm *----*----*----*----*
test_decoding *----*----*----*----*
hstore_plperl *----*----*----*
hstore_plpython *----*----*----*
ltree_plpython *----*----*----*
tsm_system_rows *----*----*----*
tsm_system_time *----*----*----*
bloom *----*----*
pg_visibility *----*----*
amcheck *----*
jsonb_plperl *
jsonb_plpython *
5 7 8 13 20 22 24 33 42 46 48 48 33 29 35 39 44 49 51 54 57 51 53 53 54
contrib モジュールの件数は基本的にバージョンが上がるごとに増えています。ただ、PostgreSQL 本体に組み込まれたり、ほかのモジュールと統合されたり、外部のプロジェクトとして独立したり、メンテナンスされずに削除されたりして、減ることもあります。
contrib モジュールの件数はバージョン 11 では 54 件、削除されたものも含めるとこれまでに 110 件に上ります。
モジュールの名前だけでは分かりづらいですが、今では当り前に使っているログ取得 (pg_logger) や自動バキューム (pg_autovacuum) の機能も、以前は contrib モジュールだったのが分かります。
マイナーだけど気になる contrib モジュール
メジャーな contrib モジュールは、検索すればいくらでも情報が見つかるので、ここでは、バージョン 11 でも使えて、誰もあまり取り上げない、マイナーな contrib モジュールについて紹介します。
spi モジュール
spi モジュール とは、サーバプログラミングインタフェース (SPI) とトリガを使った C 言語関数の実装例を集めたモジュールです。バージョン 11 では最も古い contrib モジュールで、1997 年にリリースのバージョン 6.2 から存在しています。
spi モジュールには以下のサブモジュールが含まれており、インストールはサブモジュールごとに行います。いずれも実装例とは言っても実際に使えるトリガ関数になっています。
- refint
- 外部キー制約を検査するトリガ関数 (組み込みの外部キー制約と同じ)
- timetravel
- タイムトラベルを実現するトリガ関数
- autoinc
- シーケンスの次の値を格納するトリガ関数 (組み込みの `serial` 型とほぼ同じ)
- insert_username
- 行を最後に更新したユーザを記録するトリガ関数
- moddatetime
- 行を最後に更新した日時を記録するトリガ関数
例えば、timetravel モジュールのタイムトラベル機能とは、テーブルの過去のある時点の状態にアクセスするための機能で、もとはバージョン 6.3 で削除された PostgreSQL 本体の機能をトリガで再現したものです。
timetravel モジュールでは、実際に行を更新、削除する代わりに、トリガで行を挿入、削除した日時を記録して、古い行を残しておきます。そうすることで、検索条件に挿入、削除日時を指定して過去の時点の状態にアクセスできます。ただ、古くからあるモジュールなだけあって、挿入、更新日時を記録するデータ型が abstime という、現在では推奨されないデータ型を使っているので、実際に使うにあたっては修正が必要そうです。
timetravel モジュールの使用例は以下のとおりです。
-
timetravel モジュールをインストールします。
=# CREATE EXTENSION timetravel; CREATE EXTENSION -
挿入、削除日時を記録する
abstime型の列を含めてテーブルを作成します。=# CREATE TABLE fruits ( (# id serial NOT NULL, (# name text NOT NULL, (# price int NOT NULL, (# insert_time abstime NOT NULL, (# delete_time abstime NOT NULL, (# PRIMARY KEY (id, delete_time) (# ); CREATE TABLE -
timetravel関数を実行するトリガを作成します。=# CREATE TRIGGER timetravel -# BEFORE INSERT OR UPDATE OR DELETE ON fruits FOR ROW -# EXECUTE PROCEDURE timetravel(insert_time, delete_time); CREATE TRIGGER -
行を挿入すると、挿入日時にその日時、削除日時に
infinity(ほかの何より未来) が記録されます。=# INSERT INTO fruits (name, price) VALUES ('orange', 100); INSERT 0 1 =# INSERT INTO fruits (name, price) VALUES ('melon', 2500); INSERT 0 1 =# INSERT INTO fruits (name, price) VALUES ('apple', 150); INSERT 0 1 =# SELECT * FROM fruits; id | name | price | insert_time | delete_time ----+--------+-------+------------------------+------------- 1 | orange | 100 | 2018-12-18 12:28:20+09 | infinity 2 | melon | 2500 | 2018-12-18 12:28:30+09 | infinity 3 | apple | 150 | 2018-12-18 12:28:36+09 | infinity (3 行) -
行を更新すると、更新前の行の削除日時にその日時が記録され、更新後の行が挿入されます。
=# UPDATE fruits SET price = price - 10 WHERE id = 1; UPDATE 1 =# SELECT * FROM fruits; id | name | price | insert_time | delete_time ----+--------+-------+------------------------+------------------------ 2 | melon | 2500 | 2018-12-18 12:28:30+09 | infinity 3 | apple | 150 | 2018-12-18 12:28:36+09 | infinity 1 | orange | 100 | 2018-12-18 12:28:20+09 | 2018-12-18 12:30:16+09 1 | orange | 90 | 2018-12-18 12:30:16+09 | infinity (4 行) -
行を削除すると、削除日時にその日時が記録されます。
=# DELETE FROM fruits WHERE id = 1; DELETE 1 =# SELECT * FROM fruits; id | name | price | insert_time | delete_time ----+--------+-------+------------------------+------------------------ 2 | melon | 2500 | 2018-12-18 12:28:30+09 | infinity 3 | apple | 150 | 2018-12-18 12:28:36+09 | infinity 1 | orange | 100 | 2018-12-18 12:28:20+09 | 2018-12-18 12:30:16+09 1 | orange | 90 | 2018-12-18 12:30:16+09 | 2018-12-18 12:30:37+09 (4 行) -
現時点の状態にアクセスするには、削除日時に
infinityを指定して検索します。検索結果は最後にid列が 1 の行を削除した状態で出力されます。=# SELECT * FROM fruits WHERE delete_time = 'infinity'; id | name | price | insert_time | delete_time ----+-------+-------+------------------------+------------- 2 | melon | 2500 | 2018-12-18 12:28:30+09 | infinity 3 | apple | 150 | 2018-12-18 12:28:36+09 | infinity (2 行) -
任意の時点の状態にアクセスするには、挿入日時にその日時以前、削除日時にその日時より後を指定して検索します。
id列が 1 の行を更新後、削除前の時点を指定したので、検索結果はその時点の状態で出力されます。=# SELECT * FROM fruits -# WHERE insert_time <= '2018-12-18 12:30:18' -# AND delete_time > '2018-12-18 12:30:18'; id | name | price | insert_time | delete_time ----+--------+-------+------------------------+------------------------ 2 | melon | 2500 | 2018-12-18 12:28:30+09 | infinity 3 | apple | 150 | 2018-12-18 12:28:36+09 | infinity 1 | orange | 90 | 2018-12-18 12:30:16+09 | 2018-12-18 12:30:37+09 (3 行)
earthdistance モジュール
earthdistance モジュール とは、地球の表面上の 2 地点間の距離を計算するモジュールです。バージョン 11 では spi モジュールに次いで lo モジュールと並んで 2 番目に古い contrib モジュールで、1998 年にリリースのバージョン 6.3 から存在しています。
earthdistance モジュールは地球が完全な球体であるのを前提に計算していますが、実際にはデコボコしていて、赤道付近がふくらんでいるので、もっと高い精度で計算したいなら、PostGIS を使ったほうがいいでしょう。
earthdistance モジュールを使うには cube と earthdistance モジュールをインストールします。cube モジュール とは多次元立方体のデータを扱うための contrib モジュールで、earthdistance モジュールを使うのに必要です。
=# CREATE EXTENSION cube;
CREATE EXTENSION
=# CREATE EXTENSION earthdistance;
CREATE EXTENSION
例えば、東京 (緯度: 35.709026、経度: 139.731992) と毎年 PGCon が開催されるカナダのオタワ (緯度: 45.421530、経度: -75.697193) の距離を計算してみます。
=# SELECT earth_distance(ll_to_earth(35.709026, 139.731992), ll_to_earth(45.421530, -75.697193));
earth_distance
------------------
10329235.7148085
(1 行)
earth_distance 関数が 2 地点間の距離を計算する関数で、2 つの地点を earth 型の引数として渡します。earth 型は cube 型をもとに制約を加えたドメインで、地球の中心からの x、y、z 距離からなる点を表します。緯度、経度から earth 型に変換するには ll_to_earth 関数を使います。ちなみに ll_to_earth 関数の ll は緯度の longitude、経度の latitude を表すのでしょう。earth_distance 関数の戻り値の単位はメートルなので、東京とオタワ間の距離は約 10329 キロメートルだと分かります。
PostgreSQL 組み込みの point 型 でも 2 地点間の距離を計算できます。earth 型と同じく東京とオタワ間の距離を計算してみます。
=# SELECT (point(139.731992, 35.709026) <@> point(-75.697193, 45.421530)) * 1609.344;
?column?
------------------
10317606.1313307
(1 行)
<@> 演算子が point 型による 2 地点間の距離を計算する演算子です。point 型は平面上の x、y 座標からなる点を表します。point 型の x が経度、y が緯度で、緯度、経度の順番が ll_to_earth 関数とは逆になります。<@> 演算子の戻り値の単位はマイルなので、メートルに変換するには 1609.344 をかける必要があります。
最後に
もう 1 つ、2 つ、contrib モジュールを紹介しようと思いましたが、記事もけっこう長くなってしって、締め切りも近づいてきたので、今回はこれにておしまいにします。contib モジュールは PostgreSQL 本体とは独立していて、ソースコードの量もそれほど多くないので、ソースコードを読み始めるのにちょうどいいです。また何か気になる contrib モジュールがあれば紹介したいと思います。