PostgreSQL 9.6 - beta1/beta2 Release Note diff
今朝、魅蛙さんのツイートを見かけて、PostgreSQL 9.6 beta2リリースが近いことを知る。
で、pgsql-commiters MLを見てみたら、pgsql: Stamp 9.6beta2.というアナウンスーンも出ていたので、どうやら今週後半にbeta2がリリースされるみたいだ。
ということは、beta2のリリースノートもFixしたはず。
なので、この機会にbeat1とbeta2のリリースノートの差分を見てみることにした。
- beta1: https://www.postgresql.org/docs/9.6/static/release-9-6.html (Current as of 2016-05-08 (commit 6f69b9639))
- beta2: https://www.postgresql.org/docs/devel/static/release-9-6.html (Current as of 2016-06-18 (commit 100340e2d))
上記のリンクはあくまで、このエントリを書いている時点でのものなので要注意。
beta1とbeta2の機能的な差分
beta1とbeta2の機能的な差分は、リリースノートの差分から判断する限りは
- パラレルスキャン関連のGUCの変更
- max_parallel_workers_per_gather が max_parallel_degree の代替？
- VACUUM コマンドに DISABLE_PAGE_SKIPPING オップションが追加
だけのように見える。
一応、各セクション毎に差分をざっと列挙した。意外と少ない。
| セクション | 差分 |
| E.1.3.1. Server | なし |
| E.1.3.1.1. Parallel Queries | パラレルスキャン関連のGUCの変更(max_parallel_degree 削除, max_parallel_workers_per_gather 追加, min_parallel_relation_size 追加) |
| E.1.3.1.3. General Performance | VACUUMコマンドにDISABLE_PAGE_SKIPPING オップションが追加, FK性能改善の説明修正 |
| E.1.3.1.4. Monitoring | なし |
| E.1.3.1.5. Authentication | なし |
| E.1.3.1.6. Server Configuration | なし |
| E.1.3.1.7. Reliability | なし |
| E.1.3.2. Replication and Recovery | なし |
| E.1.3.3. Queries | なし |
| E.1.3.4. Utility Commands | なし |
| E.1.3.5. Permissions Management | なし |
| E.1.3.6. Data Types | なし |
| E.1.3.7. Functions | なし |
| E.1.3.8. Server-Side Languages | なし |
| E.1.3.9. Client Interfaces | なし |
| E.1.3.10. Client Applications | なし |
| E.1.3.10.1. psql | \watch command 改善の説明修正 |
| E.1.3.10.2. pgbench | なし |
| E.1.3.11. Server Applications | なし |
| E.1.3.12. Source Code | なし |
| E.1.3.13. Additional Modules | なし |
PostgreSQL 9.6 - VACUUM Progress Reporting
そういえば、9.6のVACUUM Progress Reportingをきちんと試してなかった。
VACUUM Progress Reporting
この機能は名前のとおり、VACUUMの進捗状況をレポートしてくれるというもの。
進捗状況は pg_stat_progress_vacuum ビューに書き込まれる。
なお、このビューに表示されるレコードはVACUUM処理中のみ存在する。VACUUMが完了すると消えてしまう。
なので、VACUUMがすぐに終わっちゃうと、進捗を見ようとしても見れないというもどかしさｗ
かといって、うちのようなpoorな環境では巨大なテーブルを作るわけにもいかない・・・。
vacuum_cost系パラメータを使う
ということで、小さいテーブルでもVACUUM Progress Reportingを見たい場合には、VACUUMの動作を遅くすればいいのですよ。
このために、Cost-based Vacuum Delay系のパラメータを調整する。
パラメータは数種類あるけど、まあ vacuum_cost_delay をちょっと調整すれば十分かと。このパラメータはデフォルト値が 0 つまり遅延なく行う設定になっているけど、これを 10 (ms) とかにしていれば、pgbench_accounts (scale=10)程度のテーブルでもそれなりにVACUUM処理に時間がかかるようになる。
psql コマンドも併用する
あとはVACUUMを実行させて、別ターミナルから pg_stat_progress_vacuum ビューを定期的に検索するようにすれば良い。
たとえば、以下の様なSQLを発行する。
で、これを繰り返し実行する。そういうときに \watch コマンドを使う。
\watch 0.1
こうすると、0.1秒間隔で直前のコマンドを実行してくれる。
ヘッダ部分が邪魔なら \t コマンドで結果行だけ表示するようにすれば良い。
さらに、結果を書き込むように、 \o filename で出力ファイルを指定しておくと、後から参照するのも楽になる。
結果
テキトーにUPDATEして汚した scale factor=10 の pgbench_accounts テーブルを含む、データベース全体にVACUUMをかけて、以下のSQLでVACUUM Progress Reportingを表示させてみた。
SELECT p.relname, v.phase, v.heap_blks_total, v.heap_blks_scanned, v.heap_blks_vacuumed, v.index_vacuum_count FROM pg_stat_progress_vacuum as v JOIN pg_class as p ON (v.relid = p.oid);
第1カラムはテーブル名、第2カラムがVacuumのステータスになる。
第3カラムは総ヒープブロック数、第4カラムはスキャンしたブロック数、第5カラムがバキュームされたブロック数・・・のような感じ。
pgbench_accounts | scanning heap | 18033 | 506 | 0 | 0 pgbench_accounts | scanning heap | 18033 | 1260 | 0 | 0 pgbench_accounts | scanning heap | 18033 | 17067 | 0 | 0 pgbench_accounts | scanning heap | 18033 | 17157 | 0 | 0 pgbench_accounts | scanning heap | 18033 | 17467 | 0 | 0 pgbench_accounts | vacuuming indexes | 18033 | 18033 | 0 | 0 (中略) pgbench_accounts | vacuuming indexes | 18033 | 18033 | 0 | 0 pgbench_accounts | vacuuming indexes | 18033 | 18033 | 0 | 0 pgbench_accounts | vacuuming heap | 18033 | 18033 | 16767 | 1 pgbench_accounts | vacuuming heap | 18033 | 18033 | 17267 | 1 pgbench_accounts | vacuuming heap | 18033 | 18033 | 17997 | 1 pg_language | cleaning up indexes | 1 | 1 | 1 | 0 pg_tablespace | scanning heap | 1 | 0 | 0 | 0
ステータスが、scanning heap → vacuuming indexes → vacuuming heap という具合に変更していくのが見える。
postgres_fdwで異バージョン接続/外部表のカスケード
PostgreSQL 9.6/9.5のpostgres_fdwの差異を調べていて、ふと気になったことを調べてみた。
異バージョン間でFDW
PostgreSQL 9.6/9.5のpostgres_fdwの差異を調べていて、ふと気になったのだけど、異なるバージョン間をpostgres_fdwで接続ってできたっけ。
ということでやってみた。
ローカルサーバが9.6、リモートサーバが9.5の場合。
bench=# EXPLAIN ANALYZE VERBOSE
SELECT AVG(a.data1), AVG(a.data2)
FROM table_a a JOIN table_b b ON (a.id = b.id)
WHERE a.data1 = 10000 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=342.59..342.60 rows=1 width=64) (actual time=2.176..2.176 rows=1 loops=1)
Output: avg(a.data1), avg(a.data2)
-> Foreign Scan (cost=100.00..341.78 rows=161 width=8) (actual time=2.151..2.153 rows=11 loops=1)
Output: a.data1, a.data2
Relations: (public.table_a a) INNER JOIN (public.table_b b)
Remote SQL: SELECT r1.data1, r1.data2 FROM (public.table_a r1 INNER JOIN public.table_b r2 ON (((r1.id = r2.id)) AND ((r1.data1 = 10000))))
Planning time: 1.714 ms
Execution time: 11.359 ms
(8 rows)おお、フツーに結合SQLを9.5のリモートサーバに投げているな。
ローカルサーバが9.5、リモートサーバが9.6
bench=# EXPLAIN ANALYZE VERBOSE
SELECT AVG(a.data1), AVG(a.data2)
FROM table_a a JOIN table_b b ON (a.id = b.id)
WHERE a.data1 = 10000 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=349.93..349.94 rows=1 width=8) (actual time=1152.246..1152.246 rows=1 loops=1)
Output: avg(a.data1), avg(a.data2)
-> Hash Join (cost=238.80..349.12 rows=161 width=8) (actual time=48.058..1152.208 rows=10 loops=1)
Output: a.data1, a.data2
Hash Cond: (b.id = a.id)
-> Foreign Scan on public.table_b b (cost=100.00..197.75 rows=2925 width=4) (actual time=0.349..1065.369 rows=1000000 loops=1)
Output: b.id, b.data1, b.data2
Remote SQL: SELECT id FROM public.table_b
-> Hash (cost=138.66..138.66 rows=11 width=12) (actual time=0.284..0.284 rows=10 loops=1)
Output: a.data1, a.data2, a.id
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Foreign Scan on public.table_a a (cost=100.00..138.66 rows=11 width=12) (actual time=0.274..0.277 rows=10 loops=1)
Output: a.data1, a.data2, a.id
Remote SQL: SELECT id, data1, data2 FROM public.table_a WHERE ((data1 = 10000))
Planning time: 0.128 ms
Execution time: 1152.649 ms
(16 rows)ローカル側は9.5なので、当然ながらJoin-pushdownの実行計画が存在しない。
なので、リモートの9.6に対して、結合のないクエリを複数回発行して、その結果をローカル側で結合する。
まあ、当たり前の結果ではあるけど、一応試してみたかったので。
postgres_fdw のカスケード
ていうか、postgres_fdwのソースって実表だけなんだっけ？と疑問に思ったので、ソースとしてビュー、マテリアライズド・ビュー、そして外部表を試してみることにした。
構成はこんな感じ。
(´-`).oO (IMPORT FOREIGN SCHEMAがサポートされて、こういう検証が本当に楽になったよ・・・)
さて、この構成でサーバ 96_l の外部テーブルに対してクエリを投げてみる。
詳細は省略するけど、table_a, table_b, table_a_v, table_a_v, table_c, table_d 全て外部テーブル経由で検索はできた。
もちろん、更新不可能なビューや、マテリアライズド・ビューをソースとする外部表を更新しようとするとエラーになる。
bench=# UPDATE table_a_v SET data1 = 50000; ERROR: cannot update view "table_a_v" DETAIL: Views containing TABLESAMPLE are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule. CONTEXT: Remote SQL command: UPDATE public.table_a_v SET data1 = 50000 STATEMENT: UPDATE table_a_v SET data1 = 50000;
bench=# UPDATE table_a_mv SET data1 = 50000; ERROR: cannot change materialized view "table_a_mv" CONTEXT: Remote SQL command: UPDATE public.table_a_mv SET data1 = 50000 STATEMENT: UPDATE table_a_mv SET data1 = 50000;
で、せっかく9.6を使っているので、Join pushdownが効くのかどうか試してみた。
table_a, table_bを結合したクエリは当然結合クエリが 96_r にpushdownされてます。
bench=# EXPLAIN ANALYZE VERBOSE
SELECT AVG(a.data1), AVG(a.data2)
FROM table_a_v a JOIN table_b b ON (a.id = b.id)
WHERE a.data1 = 1000 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=342.59..342.60 rows=1 width=64) (actual time=6.023..6.023 rows=1 loops=1)
Output: avg(a.data1), avg(a.data2)
-> Foreign Scan (cost=100.00..341.78 rows=161 width=8) (actual time=6.016..6.016 rows=0 loops=1)
Output: a.data1, a.data2
Relations: (public.table_a_v a) INNER JOIN (public.table_b b)
Remote SQL: SELECT r1.data1, r1.data2 FROM (public.table_a_v r1 INNER JOIN public.table_b r2 ON (((r1.id = r2.id)) AND ((r1.data1 = 1000))))
Planning time: 0.248 ms
Execution time: 7.353 ms
(8 rows)では、96_rr → 96_r → 96_l のように外部表をカスケードした場合はどうなるのか。
お、96_rr サーバまで結合クエリがきちんとpushdownされてますね。
bench=# EXPLAIN ANALYZE VERBOSE
SELECT AVG(c.data1), AVG(c.data2)
FROM table_c c JOIN table_d d ON (c.id = c.id)
WHERE c.data1 = 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=294.93..294.94 rows=1 width=64) (actual time=1.105..1.105 rows=1 loops=1)
Output: avg(c.data1), avg(c.data2)
-> Foreign Scan (cost=100.00..277.86 rows=3413 width=8) (actual time=1.101..1.101 rows=0 loops=1)
Output: c.data1, c.data2
Relations: (public.table_c c) INNER JOIN (public.table_d d)
Remote SQL: SELECT r1.data1, r1.data2 FROM (public.table_c r1 INNER JOIN public.table_d r2 ON (((r1.id = r1.id)) AND ((r1.data1 = 1000))))
Planning time: 0.140 ms
Execution time: 1.787 ms
(8 rows)96_r, 96_rr サーバのデータベースに contrib/auto_explain を入れて、実際にどんなSQLが発行されてみるか確認してみる。
96_r サーバで実行されたSQL
LOG: duration: 0.392 ms plan: Query Text: DECLARE c1 CURSOR FOR SELECT r1.data1, r1.data2 FROM (public.table_c r1 INNER JOIN public.table_d r2 ON (((r1.id = r1.id)) AND ((r1.data1 = 1000)))) Foreign Scan (cost=100.00..277.86 rows=3413 width=8) Relations: (public.table_c r1) INNER JOIN (public.table_d r2)
96_rr サーバで実行されたSQL
LOG: duration: 0.018 ms plan: Query Text: DECLARE c1 CURSOR FOR SELECT r1.data1, r1.data2 FROM (public.table_c r1 INNER JOIN public.table_d r2 ON (((r1.id = r1.id)) AND ((r1.data1 = 1000)))) Nested Loop (cost=0.29..2553.33 rows=100000 width=8) -> Index Scan using c_data1 on table_c r1 (cost=0.29..12.33 rows=1 width=8) Index Cond: (data1 = 1000) Filter: (id = id) -> Seq Scan on table_d r2 (cost=0.00..1541.00 rows=100000 width=0)
うん、きちんと結合条件がpushdownされてますね。
知ってる人には当たり前かもしれないけど、自分で確認して納得できたので良かった。
PostgreSQL 9.6 全文検索 演算子/tsquery()を使わず全文検索
ぬこは激怒した。
かのPostgreSQL 9.6 beta1文書 全文検索用演算子の <-> の使用例の記述を改善せねばと決意した。
<-> 演算子を使うとどうなるの？
tsquery_phrase()のsyntax suggerであるのは想像できるのだが、distanceをどう設定しているのか文書を読んでも今ひとつ良くわからぬ。
結局、動かすかソースを見るしかないのだろうか。
とりあえず、<-> 演算子を使ってみる。
tsearch=# SELECT
to_tsquery('english', 'cat') <-> to_tsquery('english', 'dog');
?column?
-----------------
'cat' <-> 'dog'
(1 row)はあ。という結果なんだけど・・・
tsquery <-> tsquery の演算子が適用された結果は、やっぱりtsquery型になる。
上の例は、TEXT型としての 'cat' <-> 'dog' ではなく、
tsquery型の外部表現である 'cat' <-> 'dog' であることに注意。
次にtsquery_phrase()だけを動かしてみた。
まず、tsquery_phrase の第3引数に10を設定して動かしてみる。
tsearch=# SELECT tsquery_phrase(
to_tsquery('english', 'cat'), to_tsquery('english', 'dog'), 10);
tsquery_phrase
------------------
'cat' <10> 'dog'
(1 row)ふむ。それにしても、<10> というのは演算子なのか？
ただ、CREATE OPERATORを見るとわかるように、PostgreSQLの演算子として使用可能な文字は、
以下の文字に限定されているはず。
The operator name is a sequence of up to NAMEDATALEN-1 (63 by default) characters from the following list:
- - * / < > = ~ ! @ # % ^ & | ` ?
なので、<10> のように間に数字がはいるものは、「SQLの演算子」としては認められない。<10> のような演算子はあくまでも、tsquery_phrase() が生成した、tsquery型の記法として使われているだけだ。
次はdistanceに2を設定。
tsearch=# SELECT tsquery_phrase(
to_tsquery('english', 'cat'), to_tsquery('english', 'dog'), 2);
tsquery_phrase
-----------------
'cat' <2> 'dog'
(1 row)うむ。じゃあ、distanceに1を設定したら・・・？
tsearch=# SELECT tsquery_phrase(
to_tsquery('english', 'cat'), to_tsquery('english', 'dog'), 1);
tsquery_phrase
-----------------
'cat' <-> 'dog'
(1 row)！！！
なんと、1を設定したときには、 <1> ではなく <-> と解釈された。
つまり、
tsquery <-> tsquery
というのは、
tsquery_phrase(tsquery, tsquery, 1);
と同じ意味になる。
tsquery_phraseのdistanceが1のときには、実質上引数1に指定したキーワードと引数2に指定したキーワードが連続したという意味になる。
なので、<-> 演算子を使うとこーなる。
tsearch=# SELECT
to_tsvector('Lion is a big cat. Cat is a small lion.')
@@
(to_tsquery('cat') <-> to_tsquery('lion'));
?column?
----------
f
(1 row)cat と lion の間に別の語(small)が入っているために、一致しない(f)とみなされる。
tsearch=# SELECT
to_tsvector('Lion is a big cat. Cat is a small lion.')
@@
(to_tsquery('small') <-> to_tsquery('lion'));
?column?
----------
t
(1 row)この場合、small と lion は間に別の語が入らず連結しているので一致する(t)とみなされる。
to_tsquery()を使わずに全文検索する。
で、この検証をやってるときに
tsearch=# SELECT tsquery_phrase(
to_tsquery('english', 'cat'), to_tsquery('english', 'dog'), 2);
tsquery_phrase
-----------------
'cat' <2> 'dog'
(1 row)の結果を見て、ふと思った。
これ、tsqueryの外部表現を文字列として渡したら、to_tsquery()を使わなくても全文検索できるってことか？
やってみた。
tsearch=# SELECT
to_tsvector('I like cats and dogs.')
@@
'''cat'' <2> ''dog''';
?column?
----------
t
(1 row)お、想定どおりの結果が返ってきたｗ
実際右辺の文字列は暗黙のキャストによって、tsquery型に変換されているはず。
まあ、面倒くさいだけなので、フツーに to_tsquery() や tsquery_phrase() を使ったほうがいいとは思うけど。
PostgreSQL 9.6 + textsearch_ja
ぬこは激怒した。
未だに9.0以降公式に対応されていない textsearch_ja をなんとかせねばと考えた。
ということで、9.6 beta1も出てきたことだし、そろそろ textsearch_ja を試してみようかと思ったわけです。
PostgreSQL 9.6 全文検索
久々にPostgreSQLの全文検索(full text search)に新機能が入ったみたい。
今回入った機能はフレーズ検索。簡単に言えば語順を意識した検索だ。
例えば、以下のようなテキストがあるとする。
tsearch=# SELECT data FROM animal;
data
--------------------------------------------------
I love cats.
I like cats and dogs.
In my bed, four dogs and five cats are sleeping.
In my room, rats and cats are fighting.
Rabbit is cute. However, cats are more cute.
Lion is a big cat. Cat is a small lion.
Miss Mery's sheep is very cute.
Miss Magee's dog is very strong.
Oscar's doll is very experimental.
(9 rows)これを以下のような全文検索関数を使って検索してみる。
tsearch=# SELECT
data
FROM animal
WHERE to_tsvector('english', data) @@ to_tsquery('english', 'dog');
data
--------------------------------------------------
I like cats and dogs.
In my bed, four dogs and five cats are sleeping.
Miss Magee's dog is very strong.
(3 rows)はい。dogを含むテキストが検索されましたね。
マギーさんの犬は本当は犬じゃない気もしますが気にしないこと。
では次に、dogとcatを含むテキストを検索してみます。
tsearch=# SELECT
data
FROM animal
WHERE to_tsvector('english', data) @@ to_tsquery('english', 'dog|cat');
data
--------------------------------------------------
I love cats.
I like cats and dogs.
In my bed, four dogs and five cats are sleeping.
In my room, rats and cats are fighting.
Rabbit is cute. However, cats are more cute.
Lion is a big cat. Cat is a small lion.
Miss Magee's dog is very strong.
(7 rows)dog または cat を含むテキストが検索されました。
ただ、結果を見てもわかるように、dogとcatの語順は関係なく、単にどちらかを含むテキストが検索されています。
で、9.6から追加されたフレーズ検索は、語順を明示的に指定できるっぽい。
フレーズ検索は tsquery_phrase() という関数を使います。先行する語、後行する語、そして語間を数値で指定する。
こんな感じ。まずは dog, cat の順で。
tsearch=# SELECT
data
FROM animal
WHERE
to_tsvector('japanese', data) @@
tsquery_phrase(
to_tsquery('japanese', 'cat'), to_tsquery('japanese', 'dog'), 10);
data
-----------------------
I like cats and dogs.
(1 row)次に、 cat, dog の順で。
tsearch=# SELECT
data
FROM animal
WHERE
to_tsvector('japanese', data) @@
tsquery_phrase(
to_tsquery('japanese', 'dog'), to_tsquery('japanese', 'cat'), 10);
data
--------------------------------------------------
In my bed, four dogs and five cats are sleeping.
(1 row)なお、上記の検索結果だと dog と cat の語間は2つ空いている。
なので、3番目の引数を2以下にするとヒットしなくなる。
tsearch=# SELECT
data
FROM animal
WHERE
to_tsvector('japanese', data) @@
tsquery_phrase(
to_tsquery('japanese', 'dog'), to_tsquery('japanese', 'cat'), 3);
data
--------------------------------------------------
In my bed, four dogs and five cats are sleeping.
(1 row)
tsearch=# SELECT
data
FROM animal
WHERE
to_tsvector('japanese', data) @@
tsquery_phrase(
to_tsquery('japanese', 'dog'), to_tsquery('japanese', 'cat'), 2);
data
------
(0 rows)しかし英語で検索なんて、普段俺はしないしなー。やっぱり日本語で検索したくなる。
そこで textsearch_ja ですよ。
textsearch_ja のインストール
さて、textsearch_ja なんですが、残念なことに、公式にはPostgreSQL 9.0 対応以降、更新されてません。
textsearch_jaや依存ソフトウェアのMecabのインストールについては下記参照。ということで割愛。
なお、いつものように、公式サイトからダウンロードしたものだと、そのままではビルド出来ないので
- textsearch_ja.c #includeの追加
- textsearch_ja.sql のLANGUAGE 'C' を'c' または c に修正
をやってビルド。
補足：ヘッダファイルの追加。これをtextsearch_ja.cに追加する。
#include "access/htup_details.h" /* add */
一応これでビルドとインストールはできた。
本当は、インストールスクリプトをCREATE EXTENSION対応しないとなー、と思いつつ、どうせ自分しか使わないので、いま一つモチベーションが起きずｗ
ということで、いつものように「走れメロス」をサンプルにします。
基本機能を動かしてみた
まずはメロス君を検索してみます。
tsearch=# SELECT
ts_headline('japanese', data,
to_tsquery('japanese', 'メロス'))
FROM meros
WHERE to_tsvector('japanese', data) @@ to_tsquery('japanese', 'メロス') LIMIT 10;
ts_headline
-----------------------------------------------------------------------------------------------------------------------------------------------
走れ<b>メロス</b>
<b>メロス</b>は激怒した。必ず、かの邪智暴虐じゃちぼうぎゃくの王を除かなければならぬと決意した。<b>メロス</b>には政治
<b>メロス</b>は村を出発し、野を越え山越え、十里はなれた此このシラクスの市にやって来
<b>メロス</b>には竹馬の友があった。セリヌンティウスである。今は此のシラクスの市で、石工をしている。その友を、これから
聞いて、<b>メロス</b>は激怒した。「呆あきれた王だ。生かして置けぬ。」
<b>メロス</b>は、単純な男であった。買い物を、背負ったままで、のそのそ王城にはいって行った。たちまち彼は、巡邏じゅんらの警吏に捕縛された。調べ
「市を暴君の手から救うのだ。」と<b>メロス</b>は悪びれずに答えた。
<b>メロス</b>は、いきり立って反駁はんばくした。「人の心を疑うのは、最も恥ずべき悪徳だ。王は、民の忠誠をさえ疑っ
「なんの為の平和だ。自分の地位を守る為か。」こんどは<b>メロス</b>が嘲笑した。「罪の無い人を殺して、何が平和だ。」
<b>メロス</b>は足もとに視線を落し瞬時ためらい、「ただ、私に情をかけたいつもりなら、処刑までに三日間の日限を与えて下さい
(10 rows)うん。大丈夫そうですね。
じゃあ、9.6で入ったフレーズ検索が動くかどうか試してみましょうか。
まず、センヌリティウス・・・じゃなくて、セリヌンティウス, メロス の語順で検索してみましょう。
tsearch=#
tsearch=# SELECT
ts_headline(
'japanese',
data,
tsquery_phrase(
to_tsquery('japanese', 'セリヌンティウス'),
to_tsquery('japanese', 'メロス'), 10) )
FROM meros
WHERE to_tsvector('japanese', data) @@ tsquery_phrase(to_tsquery('japanese', 'セリヌンティウス'), to_tsquery('japanese', 'メロス'), 10)
;
ts_headline
---------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------
<b>セリヌンティウス</b>は無言で首肯うなずき、<b>メロス</b>をひしと抱きしめた。友と友の間は、それでよかった。<b>セリヌンティウス</b>は、縄打た
<b>セリヌンティウス</b>様の弟子でございます。」その若い石工も、<b>メロス</b>の後について走りながら叫んだ。「もう、駄目でございます。むだでございます。走るのは
、やめて下さい
<b>セリヌンティウス</b>は、徐々に釣り上げられてゆく。<b>メロス</b>はそれを目撃して最後の勇、先刻、濁流を泳いだように群衆を掻き
<b>セリヌンティウス</b>。」<b>メロス</b>は眼に涙を浮べて言った。「私を殴れ。ちから一ぱいに頬を殴れ。私は、途中で一度、悪い
(4 rows)お、大丈夫そう。
じゃあ、次は メロス セリヌンティウス の順で。
tsearch=# SELECT
ts_headline(
'japanese',
data,
tsquery_phrase(
to_tsquery('japanese', 'メロス'),
to_tsquery('japanese', 'セリヌンティウス'), 10) )
FROM meros
WHERE to_tsvector('japanese', data) @@ tsquery_phrase(to_tsquery('japanese', 'メロス'), to_tsquery('japanese', 'セリヌンティウス'), 10)
;
ts_headline
---------------------------------------------------------------------------------------------------------------------------------------------
<b>メロス</b>には竹馬の友があった。<b>セリヌンティウス</b>である。今は此のシラクスの市で、石工をしている。その友を、これから
<b>メロス</b>は、友に一切の事情を語った。<b>セリヌンティウス</b>は無言で首肯うなずき、<b>メロス</b>をひしと抱きしめた。友と友の間は、それで
<b>メロス</b>は腕に唸うなりをつけて<b>セリヌンティウス</b>の頬を殴った。
(3 rows)きちんと、さっきと違う結果になりましたね。殴られたセリヌンティウスも満足なことでしょう。
ランク算出もとりあえずは大丈夫っぽい。
tsearch=# SELECT
ts_rank_cd(
to_tsvector('japanese', data),
tsquery_phrase(
to_tsquery('japanese', 'セリヌンティウス'),
to_tsquery('japanese', 'メロス'), 10) ),
ts_headline('japanese', data,
tsquery_phrase(
to_tsquery('japanese', 'セリヌンティウス'),
to_tsquery('japanese', 'メロス'), 10) )
FROM meros
WHERE to_tsvector('japanese', data) @@ tsquery_phrase(to_tsquery('japanese', 'セリヌンティウス'), to_tsquery('japanese', 'メロス'), 10)
;
ts_rank_cd | ts_headline
------------+--------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------
0.05 | <b>セリヌンティウス</b>は無言で首肯うなずき、<b>メロス</b>をひしと抱きしめた。友と友の間は、それでよかった。<b>セリヌンティウス</b>は、縄打た
0.0166667 | <b>セリヌンティウス</b>様の弟子でございます。」その若い石工も、<b>メロス</b>の後について走りながら叫んだ。「もう、駄目でございます。むだでござい
す。走るのは、やめて下さい
0.02 | <b>セリヌンティウス</b>は、徐々に釣り上げられてゆく。<b>メロス</b>はそれを目撃して最後の勇、先刻、濁流を泳いだように群衆を掻き
0.1 | <b>セリヌンティウス</b>。」<b>メロス</b>は眼に涙を浮べて言った。「私を殴れ。ちから一ぱいに頬を殴れ。私は、途中で一度、悪い
(4 rows)
残りの課題
あとは、自作のゆるい textsearch_ja 用評価関数の組み込みと、本体へのパッチだな・・・。
9.5でも動いたから、たぶん、9.6でも動くんじゃないかーと期待。
上級国民専用SQL関数をどうやってリストアップすべきか
superuser専用SQL関数
最近、PostgreSQLのsuperuser権限でのみ実行可能な操作ってなんじゃろ？というのを調べていたんだけど、その調べ物の一環として、SQL関数についてもsuperuser権限でのみ実行可能なものが何かを調べようとしていた。
最初、システムカタログ pg_proc あたりを調べればすぐに分かるかな？とか軽く考えていたけど、よくよく思い出してみると、そもそもCREATE FUNCTIONやALTER FUNCTIONにそんなオプションなかったよなあと気づいた。
なので、CREATE FUNCTION背景で更新される pg_proc のメンバにもそんなフラグはない。
pg_proc.proacl には権限情報は設定されるが、上級国民たるsuperuserはこの指定は無視するしなあ。
PostgreSQL文書から調べる
PostgreSQL文書のSQL関数の章に、この関数はスーパーユーザのみ実行可能、みたいな記述がある関数はある。
例えば、サーバシグナル送信関数などは
これらの関数の使用は、注記された例外を除き、大抵の場合スーパーユーザのみに制限されています。
とか書いてある。実際、スーパーユーザ権限をもたない、一般国民一般ユーザ権限のユーザ(foo)で実行すると、
[nuko@localhost src]$ psql postgres -U foo -c "SELECT pg_reload_conf()" ERROR: permission denied for function pg_reload_conf [nuko@localhost src]$ psql postgres -U postgres -c "SELECT pg_reload_conf()" pg_reload_conf ---------------- t (1 row)
許可がないと怒られてしまう。上級国民スーパーユーザは問題なく実行できるのに！
PostgreSQL文書に明記されている関数はいいのだが、問題はそうした明記がないような関数だ。
例えば、pg_ls_dir()という指定したパスのディレクトリの内容を一覧表示するSQL関数があるのだが、この関数の説明には実行にスーパーユーザ権限が必要、とは書かれていない(少なくとも自分にはそう書かれていると理解できなかった)。
実際、この関数はスーパーユーザ権限がないと実行できない。
[nuko@localhost src]$ psql postgres -U postgres -c "SELECT pg_ls_dir('.')"
pg_ls_dir
----------------------
pg_xlog
global
pg_clog
（中略）
postmaster.opts
postmaster.pid
(24 rows)
[nuko@localhost src]$ psql postgres -U foo -c "SELECT pg_ls_dir('.')"
ERROR: must be superuser to get directory listings
[nuko@localhost src]$ データベースクラスタ内のファイルやディレクトリの情報を、一般ユーザに参照させないというのは分からんでもないが、だとしてもPostgreSQL文書には明記して欲しい気もするのよ。
まあ、ちょいとPostgreSQL的な常識を働かせれば、スーパーユーザ権限でないと実行できない関数というのは、
くらいじゃないかと推測できるのかもしれないが、一応確認してみたくはなる。
で、どうやって調べるか。
結局はソースを当たるしかないのかな・・・
PostgreSQLソース内で、カレントユーザがスーパーユーザ権限なのかどうかを判定するC関数 superuser() というのがあるので、それをリストアップして、使っているSQL関数のソースを探り当てるしかないのかな・・・
[nuko@localhost src]$ find . -name "*.c" | xargs egrep -n "if \(.?superuser\(\)\)" | wc
59 177 3188
[nuko@localhost src]$ 9.5のソースだと59箇所あるのか・・・。面倒だのう。
自分でSQL関数を動かして確認するほうが手っ取り早いかなぁ。
Jubatusでラーメンレビューの点数推測 - キー追加
レビュー本文だけでなく、別の要素を学習キーとして追加すれば、推測の精度が上がるのでは？と思い、以下のパターンで再検証してみた。
- ユーザID + レビュー本文
- スープ種別 * レビュー本文
ユーザIDを追加することで、同一人物のスコア付与の癖から特徴を抽出できないか、またスープ種別を追加することで、スープによる採点の特徴を抽出できないか試すことにした。
(例えば、豚骨醤油スープのラーメンにおいては、「こってり」や「濃厚」はポジティブに思われやすいし、逆に淡麗系の塩などでは、「あっさり」がポジティブに思われる、などの特徴があるのではと推測した)
Jubatus起動時に設定するファイルは前回のまま。
(本当はユーザIDとスープ種別のtypeを変えるほうがよりいいんだろうけど、面倒なので一旦これで)
同じデータを元に、
- ユーザID + レビュー本文
- スープ種別 * レビュー本文
を学習させる jubash スクリプトファイルをawkで生成。
同様に、推測対象の classify を使ったスクリプトファイルに、ユーザIDあるいはスープ種別のキーを追加して実行してみた。
| レビュー対象 | 実際の点数 | レビュー本文のみ | レビュー本文＋ユーザID | レビュー本文＋スープ分類 |
| ジャージャー麺 | 70 | 70, 60, 55 | 65, 70, 80 | 70, 80, 65 |
| ラーメン | 70 | 70, 60, 0 | 70, 55, 45 | 70, 55, 80 |
| 皆楽みそラーメン | 80 | 70, 65, 60 | 70, 65, 75 | 70, 65, 80 |
| 五目そば | 65 | 75. 70, 65 | 70, 65, 60 | 65, 70, 60 |
| まぜそば | 75 | 80, 60, 70 | 75, 80, 60 | 75, 80, 85 |
結果を見てみると、スープ種別の追加によって、以前より精度が改善されたような気がしないでもない。
まあ、自分の採点5件だけなので、これだけで判断するのもどうかとは思うが、こういう形でチューニングしていくのかなー。
あとは、レビュー本文からのノイズ(ラーメンの味の評価に関係しないテキスト)をどうやって、自動的に除去すればいいのかなー。ラーメンデータベースユーザの中には、レビュー本文を日記帳と勘違いしているユーザもいるしなあｗ【毒】
さて、次のテーマは何にしようかな。
ユーザのクラスタリングってどう？みたいなコメントがあったので、ユーザの採点傾向から、似たようなユーザのクラスタリングというのは、如何にも機械学習らしいし、面白そうでもある。
