実行環境
- 仮想マシンのゲスト側：Portable VirtualBox 4.3.6 + Scientific Linux 7 + PostgreSQL 9.5 Alpha 2
- ホスト側：Windows7 32bit + ConEmu Build 150513 + SSH、psql
この項は一昨日・昨日のほぼコピペです。Scientific Linux 7へPostgreSQL 9.5 Alpha 2をインストールした件は8月18日の記事を参照。その時と同様、仮想マシンをVirtualBoxのウィンドウ非表示モード（Headless）で起動し、インストール&クエリをホスト側Windowsのコンソールで行いました。
直接関係ありませんがゲスト－ホスト間のネットワークはNAT + ポートフォワーディングで行い、二つ目の画像のようにホスト側の5438番ポートをゲスト側5432に転送。これは自分の備忘録です。どの仮想マシンに何番を割り当てたか忘れるので。
yumでreleaseverを指定してインストール
ここからCentOSの時と少し事情が変わります。8月18日の記事に書いたとおり、PostgreSQL 9.5 Alpha 2を入れる際、レポジトリデータのURLの一部（Scientific Linuxのバージョン表記）が間違っていたので手動でファイルを書き換えました。本来はyumコマンドのオプションreleaseverを指定するべきところ。
とりあえずyum listすると、レポジトリデータがキャッシュされているせいか昨日と同様にPostgreSQL 9.5用のplv8パッケージが表示されました。
yum list | grep postgresql95
yum list | grep plv8
ところがreleasever指定なくインストールすると、途中で読みに行くレポジトリデータのURLが間違ったままで失敗。8月18日のPostgreSQL本体のインストールとは違う状況。
# yum -y install plv8_95
(.....)
http://yum.postgresql.org/9.5/redhat/rhel-7.0-x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
Trying other mirror.
(.....)
Error downloading packages:
plv8_95-1.4.4-1.rhel7.x86_64: [Errno 256] No more mirrors to try.
改めて正攻法でオプション「--releasever=7」を指定すると、普通にインストール完了。この「7」は、元の「7.0」の訂正版です。いったんPostgreSQLサーバを再起動（必須か分からないけど念のため）。
# yum -y --releasever=7 install plv8_95
(.....)
Running transaction
Installing : 1:v8-3.14.5.10-17.el7.x86_64 1/2
Installing : plv8_95-1.4.4-1.rhel7.x86_64 2/2
Verifying : plv8_95-1.4.4-1.rhel7.x86_64 1/2
Verifying : 1:v8-3.14.5.10-17.el7.x86_64 2/2
Installed:
plv8_95.x86_64 0:1.4.4-1.rhel7
Dependency Installed:
v8.x86_64 1:3.14.5.10-17.el7
Complete!
# service postgresql-9.5 restart
Redirecting to /bin/systemctl restart postgresql-9.5.service
テスト用データベースでCREATE EXTENSION
この項はWindows版を含めてほとんど同じ内容。psqlで接続してテスト用データベースを作り、そのDBに再接続しCREATE EXTENSION一文で簡単にインストール。pg_extensionでバージョンを確認すると1.4.4でした。そう言えば、PostgreSQLをインストールしてデータベースクラスタを初期化した際、デフォルトの文字コードがSQL_ASCIIになってしまったので、このDBは明示的にUTF-8指定して作成。
psql -U postgres -p 5438
CREATE DATABASE plv8_test
TEMPLATE template0
ENCODING 'UTF-8'
LC_COLLATE 'C'
LC_CTYPE 'C';
\c plv8_test
CREATE EXTENSION plv8;
SELECT extname, extversion FROM pg_extension;
extname | extversion
---------+------------
plpgsql | 1.0
plv8 | 1.4.4
(2 行)
PL/v8ならではのウィンドウ関数自作（Window function API）
23日と24日にテストしたTyped arrayと並んで、PL/v8ならではの機能にウィンドウ関数を自作できることがあります。通常、PostgreSQLでウィンドウ関数を書ける言語はCのみですが（公式ドキュメントにそう書いてある）、CのAPIを呼び出すオブジェクトがPL/v8に用意されてます。素晴らしい。
オブジェクトの詳細は下記ドキュメントを参照。合わせてPostgreSQL文書でのCREATE FUNCTIONのページと、PL/v8以外でウィンドウ関数を作れるPL/Rの当該ドキュメントも挙げておきます。ポスグレ組み込み言語で「本体に近い」PL/Perl、PL/Pythonはウィンドウ関数を作れず、サードパーティ的なPL/v8、PL/Rで作れるというのは何か面白い。
↓ テスト的に作ってみたウィンドウ関数。基本は整数を配列に加えていきますが、一定の値未満だったら空にしてリセット。積算気温の計算とかで「○℃以上が連続した区間だけを対象にする」準備のような。この種のフレームorパーティションをクエリでやるのは面倒で、関数化できると便利だな～と思ってました。
CREATE OR REPLACE FUNCTION partial_accum( v1 int, v2 int )
RETURNS int[] LANGUAGE plv8 IMMUTABLE WINDOW AS $$
var w = plv8.get_window_object(),
obj = w.get_partition_local();
if (v1 < v2) {
w.set_partition_local({ "val": [] });
return null;
} else {
if (w.get_current_position() === 0 || obj[ "val" ].length === 0) {
obj = { "val": [ v1 ] };
} else {
obj[ "val" ].push( v1 );
}
w.set_partition_local( obj );
return obj[ "val" ];
}
$$;
関数を定義したのに続き、テスト用のダミーテーブルを作成。
CREATE TABLE dummy AS
SELECT round(random() * 30) AS num
FROM generate_series(1, 20);
SELECT num FROM dummy;
num
-----
28
1
24
21
14
4
3
4
6
11
25
1
12
18
28
30
5
5
17
8
(20 行)
上のテーブルに、自作ウィンドウ関数を適用した例。第二引数の境界値は任意で、この値未満の行があると結果の配列が空にリセットされます。
SELECT num, partial_accum(num :: int, 10) over()
FROM dummy;
num | partial_accum
-----+---------------
28 | {28}
1 |
24 | {24}
21 | {24,21}
14 | {24,21,14}
4 |
3 |
4 |
6 |
11 | {11}
25 | {11,25}
1 |
12 | {12}
18 | {12,18}
28 | {12,18,28}
30 | {12,18,28,30}
5 |
5 |
17 | {17}
8 |
(20 行)
↓ 境界値を増やすとリセットされる箇所（行）が減ります。時系列の積算などで対象範囲を変えながら繰り返す場合に使えるかも。今回は結果を配列にしましたが、任意の計算をして結果をスカラにする等も関数定義で自由に対応できます。
SELECT num, partial_accum(num :: int, 5) over()
FROM dummy;
num | partial_accum
-----+------------------------
28 | {28}
1 |
24 | {24}
21 | {24,21}
14 | {24,21,14}
4 |
3 |
4 |
6 | {6}
11 | {6,11}
25 | {6,11,25}
1 |
12 | {12}
18 | {12,18}
28 | {12,18,28}
30 | {12,18,28,30}
5 | {12,18,28,30,5}
5 | {12,18,28,30,5,5}
17 | {12,18,28,30,5,5,17}
8 | {12,18,28,30,5,5,17,8}
(20 行)
↓ 特に意味ありませんがテストで、値の昇順での実行。境界値以上になるまでは結果が空です。
SELECT num, partial_accum(num :: int, 5) over(ORDER BY num)
FROM dummy;
num | partial_accum
-----+--------------------------------------------
1 |
1 |
3 |
4 |
4 |
5 | {5}
5 | {5,5}
6 | {5,5,6}
8 | {5,5,6,8}
11 | {5,5,6,8,11}
12 | {5,5,6,8,11,12}
14 | {5,5,6,8,11,12,14}
17 | {5,5,6,8,11,12,14,17}
18 | {5,5,6,8,11,12,14,17,18}
21 | {5,5,6,8,11,12,14,17,18,21}
24 | {5,5,6,8,11,12,14,17,18,21,24}
25 | {5,5,6,8,11,12,14,17,18,21,24,25}
28 | {5,5,6,8,11,12,14,17,18,21,24,25,28}
28 | {5,5,6,8,11,12,14,17,18,21,24,25,28,28}
30 | {5,5,6,8,11,12,14,17,18,21,24,25,28,28,30}
(20 行)
↓ 同じくテストで、値の降順での実行。境界値未満になったら結果が空にリセットされます。
SELECT num, partial_accum(num :: int, 5) over(ORDER BY num DESC)
FROM dummy;
num | partial_accum
-----+--------------------------------------------
30 | {30}
28 | {30,28}
28 | {30,28,28}
25 | {30,28,28,25}
24 | {30,28,28,25,24}
21 | {30,28,28,25,24,21}
18 | {30,28,28,25,24,21,18}
17 | {30,28,28,25,24,21,18,17}
14 | {30,28,28,25,24,21,18,17,14}
12 | {30,28,28,25,24,21,18,17,14,12}
11 | {30,28,28,25,24,21,18,17,14,12,11}
8 | {30,28,28,25,24,21,18,17,14,12,11,8}
6 | {30,28,28,25,24,21,18,17,14,12,11,8,6}
5 | {30,28,28,25,24,21,18,17,14,12,11,8,6,5}
5 | {30,28,28,25,24,21,18,17,14,12,11,8,6,5,5}
4 |
4 |
3 |
1 |
1 |
(20 行)
PL/Rでウィンドウ関数を作れることは知っていたけど、今まで何となく試さずでした。今回、PL/v8のテストがてら初めてウィンドウ関数を作る機会になってよかった。APIの使い方はまだ理解不十分で、少しずつ調べて何か実用的な関数ができるといいなぁ。