本エントリーはPostgreSQL Advent Calendar 2021の13日目です。
はじめに
本記事ではPostgreSQLの拡張機能の作成とpgxnでの公開(と導入方法)のためのステップを紹介します。拡張機能の作成と書いてますが、元々pgbenchの機能として使えていた乱数生成の関数をユーザ定義関数にしてみた、という軽いものです。拡張機能作成の作法については、説明は割愛しています。pgxnでの公開については細かく説明しているため、雰囲気を掴めていただけるかと思います。
特定の分布に従った乱数を生成する拡張機能
この拡張機能を作ろうと思ったきっかけは、昨年のSnowflakeのアドベントカレンダーでSnowflake でダミーデータを生成するという記事を見たことです。
SnowflakeでもPostgreSQLのgenerate_series()に相当する集合を返す関数を持っており、これで様々なデータ生成ができます。そんな関数を使いつつ、正規分布やZIP分布に従った乱数を生成する関数を併用し、より実践的なテストデータを生成できることを見て、PostgreSQLでもできたら嬉しいなーと思ったのでした。
とはいえ、PostgreSQLでも元々このような乱数生成の仕組みは備えています。contribモジュールのtable_funcには正規分布に従う乱数生成の関数としてnormal_rand()がありますし、pgbenchのカスタムスクリプトで利用可能な組み込み関数を使うとガウス分布(正規分布)、指数分布、ZIP分布に従う乱数生成が可能です。
そのため、例えば以下のようなpgbench用のカスタムスクリプト使えば目的のデータが用意できます。ガウス分布と指数分布の２パターンを試しています。
-- カスタムスクリプトの中身を確認
$ cat /tmp/rand.sql
-- 正規分布で１〜１０００の範囲、標準偏差2.5で生成
\set r random_gaussian(1,1000,2.5)
BEGIN;
INSERT INTO rand_test SELECT :r;
COMMIT;
$ psql -c "CREATE UNLOGGED TABLE rand_test(c1 bigint);
-- 100万件のデータ生成
$ time pgbench -n -f /tmp/rand.sql -c 10 -t 100000
(省略)
real 1m36.452s
user 0m6.370s
sys 0m9.888s
-- データを40ごとにカウントし、分布を可視化
$ psql -c "SELECT c1 / 40, repeat('*', count(*)::int/2000) FROM rand_test GROUP BY c1 / 40 ORDER BY 1"
?column? | repeat
----------+------------------------------------------
0 | **
1 | ***
2 | *****
3 | ********
4 | ***********
5 | ***************
6 | *******************
7 | ************************
8 | *****************************
9 | *********************************
10 | *************************************
11 | ***************************************
12 | ****************************************
13 | ***************************************
14 | *************************************
15 | *********************************
16 | *****************************
17 | ************************
18 | *******************
19 | ***************
20 | ***********
21 | ********
22 | *****
23 | ***
24 | **
25 |
(26 rows)
-- テーブルを空に
$ psql -c "TRUNCATE rand_test;
-- カスタムスクリプトの中身を確認
$ cat /tmp/rand2.sql
-- 指数分布で１〜１０００の範囲、parameter3.0で生成
\set r random_gaussian(1,1000,3.0)
BEGIN;
INSERT INTO rand_test SELECT :r;
COMMIT;
-- 100万件のデータ生成
$ time pgbench -n -f /tmp/rand2.sql -c 10 -t 100000
(省略)
real 3m25.415s
user 0m12.918s
sys 0m21.379s
-- データを40ごとにカウントし、分布を可視化
$ psql -c "SELECT c1 / 40, repeat('*', count(*)::int/2000) FROM rand_test GROUP BY c1 / 40 ORDER BY 1"
?column? | repeat
----------+------------------------------------------------------------
0 | **********************************************************
1 | ****************************************************
2 | **********************************************
3 | *****************************************
4 | ************************************
5 | ********************************
6 | *****************************
7 | *************************
8 | **********************
9 | ********************
10 | ******************
11 | ***************
12 | **************
13 | ************
14 | ***********
15 | *********
16 | ********
17 | *******
18 | ******
19 | ******
20 | *****
21 | ****
22 | ****
23 | ***
24 | ***
25 |
(26 rows)
しかし、毎回pgbenchのカスタムスクリプトを用意するのも面倒ですし、やはり時間がかかります。(100万件のデータ生成で1分半、3分半は体感的に遅い・・)
ということで、pgbenchの乱数生成部分のコードをそのまま流用し、同じパラメータのSQL関数として呼び出せるようにユーザ定義関数を作成しました。1
pg_rand_ext
作成した関数は指数分布用のrandom_exponential()、ガウス分布用のrandom_gaussian()、ZIP分布用のrandom_zipfian()です。これら拡張機能(EXTENSION)モジュールとしてpg_rand_extとしました。Githubにあるので、興味のある方は使ってみてください。
これを一般的なPostgreSQLの拡張機能と同様にビルドし、導入先のDBでCREATE EXTENSION pg_rand_ext;を行います。これによりrand_extスキーマが生成され、そこに前述の3つの関数が作成されます。pgbenchの時と同様にガウス分布と指数分布の乱数生成を行なってみた結果が以下です。
$ psql
-- pgbenchの時と同様の条件でガウス分布の乱数100万件のデータを投入
=# INSERT INTO rand_test SELECT rand_ext.random_gaussian(1,1000,2.5) FROM generate_series(1,1000000);
INSERT 0 1000000
Time: 2603.115 ms (00:02.603)
=# SELECT c1 / 40, repeat('*', count(*)::int/2000) FROM rand_test GROUP BY c1 / 40 ORDER BY 1;
?column? | repeat
----------+------------------------------------------
0 | **
1 | ***
2 | *****
3 | *******
4 | ***********
5 | ***************
6 | *******************
7 | ************************
8 | *****************************
9 | *********************************
10 | ************************************
11 | ***************************************
12 | ****************************************
13 | ***************************************
14 | *************************************
15 | *********************************
16 | *****************************
17 | ************************
18 | *******************
19 | ***************
20 | ***********
21 | ********
22 | *****
23 | ***
24 | **
25 |
(26 rows)
Time: 212.441 ms
-- テーブルを空にする
=# truncate rand_test ;
TRUNCATE TABLE
Time: 10.211 ms
-- 指数分布の乱数を100万件投入
=# INSERT INTO rand_test SELECT rand_ext.random_exponential(1,1000,3.0) FROM generate_series(1,1000000);
INSERT 0 1000000
Time: 2582.194 ms (00:02.582)
=# SELECT c1 / 40, repeat('*', count(*)::int/2000) FROM rand_test GROUP BY c1 / 40 ORDER BY 1;
?column? | repeat
----------+------------------------------------------------------------
0 | **********************************************************
1 | *****************************************************
2 | **********************************************
3 | *****************************************
4 | ************************************
5 | ********************************
6 | *****************************
7 | *************************
8 | **********************
9 | *******************
10 | *****************
11 | ***************
12 | **************
13 | ************
14 | ***********
15 | *********
16 | ********
17 | *******
18 | ******
19 | ******
20 | *****
21 | ****
22 | ****
23 | ***
24 | ***
25 |
(26 rows)
Time: 201.028 ms
(ロジックが同じなので当然ですが)pgbenchと同様のデータが生成されました。また、やはり速度は良く、pgbenchを介した場合は1分半、3分半かかっていたのが2秒になりました。これなら、数千万〜数億のデータもそれほど時間をかけずに生成できそうです。
pgxnで公開してみる
これだけだと少し寂しいので、pgxnで公開して導入をもっと楽にしてみます。
pgxnとは
pgxnはPostgreSQL Extension Networkのことで、PostgreSQLの拡張機能のダウンロード、ビルド、インストールまでをコマンド1つでできるようにしてくれる仕組みです。perlのCPANやNodeのnpmのようなものです。例えば、PostgreSQLでOracleの関数(ADD_MONTHなど)やデータ型(VARCHAR2など)を利用可能にするorafceという拡張機能があります。これをインストールする場合、以下のコマンドを実行するだけでOKです。
$ pgxn install orafce
あとは、目的のDBでCREATE EXTENSIONすれば、orafceの提供する関数やデータ型が使えるようになります。あるいは
$ pgxn load -d mydb orafce
とするとmydbデータベースで CREATE EXTENSION orafce;とした場合と同じ作用をします。pgxnで導入可能な拡張機能はpgxnのサイトで探すことができます。
pgxnのアカウントを取る
では、公開にむけて進んでいきます。公開の手順はpgxnの管理サイトにあるHow toに詳しく書かれていますので、この記事では最小限のステップを紹介していきます。
まずはpgxnで公開するためアカウントを作成します。pgxnの管理用サイトに行くと以下のようなページがあります。
赤枠のRequest Accountのリンクに行くと以下のページになります。
「The Essentials」に必要な情報を入力します。「Your Plans」には公開したい拡張機能の紹介を簡単で良いので記入します。下部の「Pretty Please!」を押せばリクエストは完了です。登録したメールアドレス宛に「Welcome to PGXN!」という件名でパスワードセット用のリンクが記入されたメールが来ます。なお、手動でアカウント追加などをしているようで、若干のタイムラグがあります。私がリクエストした際は約1日ほどかかりました。
資材を準備する
すでに公開したい拡張機能が完成している前提とします。pgxnで公開するには、いくつか追加で作成、もしくは編集する資材があります。必須なのはMETA.jsonファイル、ケースに応じて追加で考慮が必要となるのはcontrolファイルとMakefileファイルです。
META.json
pgxnでの公開には、META.jsonというファイルを拡張機能に同梱する必要があります。META.jsonはその名の通り拡張機能に関するメタ情報で、拡張機能の概要やバージョン、作成者情報やリポジトリ(githubなど)を記載したjsonファイルです。
記述可能な内容や説明は公式のページを参照してください。ただ全てを記述する必要はなく、最低限必要なものだけ書けば公開できます。おそらくもっともシンプルな例はこちらになります。META.jsonで記入した内容は、pgxn上の拡張機能ごとのページの情報にそのまま直結するので、リポジトリや拡張機能のホームページ、紹介Blogなどがあれば書いておくと良いでしょう。(後ほど説明します)
私はpg_rand_ext用に以下のMETA.jsonを作成しています。2
{
"name": "pg_rand_ext",
"abstract": "Functions that generate random values for PostgreSQL",
"description": "pg_rand_ext is a set of functions that extends the functions for generating random values for PotstgreSQL.",
"version": "1.0.1",
"maintainer": "Tatsuhito Kasahara <kasahara.tatsuhito@gmail.com>",
"license": {
"PostgreSQL": "http://www.postgresql.org/about/licence"
},
"prereqs": {
"runtime": {
"requires": {
"PostgreSQL": "14.0.0"
},
"recommends": {
"PostgreSQL": "14.0.0"
}
}
},
"provides": {
"pg_rand_ext": {
"file": "pg_rand_ext--1.0.sql",
"docfile": "README.md",
"version": "1.0.1",
"abstract": "Functions that generate random values for PostgreSQL"
}
},
"resources": {
"bugtracker": {
"web": "https://github.com/kasaharatt/pg_rand_ext/issues"
},
"repository": {
"url": "https://github.com/kasaharatt/pg_rand_ext.git",
"web": "https://github.com/kasaharatt/pg_rand_ext",
"type": "git"
}
},
"generated_by": "Tatsuhito Kasahara",
"meta-spec": {
"version": "1.0.0",
"url": "http://pgxn.org/meta/spec.txt"
},
"tags": [
"random",
"pgbench"
]
}
META.jsonのバリデーション用に、validate_pgxn_metaというperlモジュールが用意されています。cpan経由で導入できます。公開前にチェックをしておくと良いでしょう。
-- cpanでinstall
$ cpan install PGXN::Meta::Validator
-- 不備があると以下のようになる
$ validate_pgxn_meta META.json
META.json is invalid. Errors:
Field /prereqs/runtime/requires/PostgreSQL: "14.0" is not a valid semantic version [Spec v1.0.0]
-- 以下問題ない場合
$ validate_pgxn_meta META.json
META.json is OK
controlファイル
controlファイルはPostgreSQLの拡張機能をEXTENSIONパッケージとして扱う場合に必要となるファイルです。このファイルはCREATE EXTENSIONのコマンド実行で利用されます。このファイルが無くとも拡張機能として実装しpgxnで公開することは可能ですが、管理が楽になるのでcontrolファイルを作りましょう。controlファイルの仕様に関しては公式のドキュメントを参照してください。大抵の場合は以下の様な内容となります。
comment = 'generate random number extension' # 拡張機能の概要
default_version = '1.0' # バージョン
module_pathname = '$libdir/pg_rand_ext' # モジュールが利用するライブラリのパス。C言語などで作成しなければ、これは不要
relocatable = true # 拡張機能により導入された関数などを他のスキーマに移動可能か？
Makefile ファイル
Cなどで実装したユーザ定義関数を拡張機能として提供する場合にはMakefileが必要となります。Makefileの記述方法に関しては公式のドキュメントを参照してください。用意するのは一般的なPostgreSQLの拡張機能用のMakefileで良いのですが一点だけ注意があります。pgxnでインストールする際、内部的には「/tmpへ資材をダウンロード」し「資材を展開後、シンプルにmake (all) && make install」を行うという処理がされます。そのため、make時にオプションを必要とするようなMakefileにしておくとエラーになりますので、気を付けてください。
資材をアップロードし公開する
資材が準備できたらアップロードに移ります。資材はzipかtar.gzでまとめておきます。pgxnの管理サイトでログインし、「Upload a Distribution」へ移ると、以下のページとなります。
「ファイルを選択」で固めた資材を指定し、「Release It!」を押下すれば完了です。もしユーザ登録時にTwitterアカウントを記入していると、以下のような通知がすぐに来ます。
数分するとpgxnにリリースした拡張機能のページが出来上がります。META.jsonにリポジトリやホームページの情報を記入しておくと、このページのResourcesの部分にリンクが作られます。
これで公開完了の状態になりました。
pgxn clientで拡張機能を導入する
それでは、公開した拡張機能をpgxnで導入してみます。pgxnで公開されている機能を導入するにはpgxn clientをインストールする必要があります。開発はここで行われています。Pythonで実装されており、easy_installやpipで導入することができます。私はとりあえずpipでインストールしてみました。
# pip3 install pgxnclient
なお、pgxnは内部で拡張機能のビルドを行いますが、その際にpg_configコマンドを使ってビルドに必要な諸情報(ヘッダファイルやビルド時のCPPFLAGSなどの情報)を取得します。pgxnを行う前に、当該のユーザのPATHにpg_configへのパスを通しておきましょう。
では早速、公開した機能をローカルの環境に入れてみます。既に紹介したようにpgxn install <モジュール名>でローカルにインストール、pgxn load -d <DB名> <モジュール名>でDBへのCREATE EXTENSIONを実施します。以下はpg_rand_extモジュールの導入結果です。
$ createdb test
$ pgxn install pg_rand_ext
INFO: best version: pg_rand_ext 1.0.1
INFO: saving /tmp/tmpvw9tcqk6/pg_rand_ext-1.0.1.zip
INFO: unpacking: /tmp/tmpvw9tcqk6/pg_rand_ext-1.0.1.zip
INFO: building extension
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -fPIC -I. -I./ -I/usr/local/pgsql141/include/server -I/usr/local/pgsql141/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o pg_rand_ext.o pg_rand_ext.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -fPIC -shared -o pg_rand_ext.so pg_rand_ext.o -L/usr/local/pgsql141/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql141/lib',--enable-new-dtags
INFO: installing extension
/usr/bin/mkdir -p '/usr/local/pgsql141/lib'
/usr/bin/mkdir -p '/usr/local/pgsql141/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql141/share/extension'
/usr/bin/install -c -m 755 pg_rand_ext.so '/usr/local/pgsql141/lib/pg_rand_ext.so'
/usr/bin/install -c -m 644 .//pg_rand_ext.control '/usr/local/pgsql141/share/extension/'
/usr/bin/install -c -m 644 .//pg_rand_ext--1.0.sql '/usr/local/pgsql141/share/extension/'
$ pgxn load -d test pg_rand_ext
INFO: best version: pg_rand_ext 1.0.1
CREATE EXTENSION
$ psql -c "SELECT rand_ext.random_gaussian(1,1000,2.5)" -d test
random_gaussian
-----------------
657
(1 row)
というわけで、数ステップで拡張機能の利用可能な状態に持っていけました。
pgxn clientの注意点
pgxn clientの利用中に、いくつかの問題に当たりました。開発用途などで使う分には問題ないと思いますが、例えば以下のような注意点があります。
-
pgxn searchで期待する検索結果が出てこない。-
pgxn searchはpgxnで公開されている拡張機能の検索を行います。分かち書きの全文検索エンジン(tsearch?)を使っているようですが、検索対象がドキュメント部分になっているらしく、なかなか検索がヒットしませんでした。(自分の拡張機能はpg_rand_extでsearchしてもヒットせず・・) - 拡張機能の検索はpgxnのページの検索機能を使うと良いです。
-
- 拡張機能のビルドに必要なものがインストールされていないと
pgxn installでエラーとなる。- 例えばlibcurlに依存している拡張機能をinstallしようとする場合、予めユーザがローカル環境にlibcurl(-devel)をインストールしておく必要があります。
- 必要な資材は
pgxn installのインストール時のエラー内容から推察する必要があります。
- pgxnで導入した拡張機能をリストなどの一覧で確認することはできない。
- pgxnを利用することでinstallやload、その逆のuninstallやunloadが可能ですが、pgxn経由で導入した拡張機能はユーザが管理(リスト化するなど)する必要があります。
おわりに
本記事ではPostgreSQLの拡張機能の作成とpgxnでの公開(と導入方法)のためのステップを紹介しました。PostgreSQLのメリットの一つは拡張のしやすさ・柔軟性だと思います。ぜひ、ちょっと便利な小さい拡張機能を色々と作って公開してみるのはどうでしょうか？