iakioの日記 RSSフィード

2010-08-10

PostgreSQLのマニュアルでよく使われている英単語(をPostgreSQLで調べる)その2

02:43 |  PostgreSQLのマニュアルでよく使われている英単語(をPostgreSQLで調べる)その2 - iakioの日記 を含むブックマーク はてなブックマーク -  PostgreSQLのマニュアルでよく使われている英単語(をPostgreSQLで調べる)その2 - iakioの日記

そういえば最近のPostgreSQLにはファイルシステムにアクセスする関数もあるんじゃなかったっけ。あれを使えばプログラムを書かなくてもドキュメントをインポートできるんじゃないのか?とふと思ったので調べてみたら、pg_ls_dir()/pg_read_file()/pg_stat_file()という関数がありました。これはいけるかも!!

=# select * from pg_ls_dir('.');
    pg_ls_dir
-----------------
 PG_VERSION
 pg_twophase
 pg_clog
 postgresql.conf
 pg_hba.conf
 pg_notify
 base
 pg_multixact
 pg_xlog
 pg_tblspc
 postmaster.opts
 pg_subtrans
 global
 pg_ident.conf
 postmaster.pid
 pg_stat_tmp
(16 rows)

=# select * from pg_ls_dir('/home');
ERROR:  absolute path not allowed
=# select * from pg_ls_dir('..');
ERROR:  reference to parent directory ("..") not allowed

ですよねー。そりゃそうだ。でもまあこの3つの関数と再帰クエリーがあれば、ディレクトリを再帰的に辿ってファイルを読み込むことができるはず。

=# select pg_stat_file(pg_ls_dir) from pg_ls_dir('.');
                                     pg_stat_file
---------------------------------------------------------------------------------------
 (4,"2010-08-11 00:38:04+09","2010-08-06 14:30:23+09","2010-08-06 14:30:23+09",,f)
 (4096,"2010-08-11 00:38:04+09","2010-08-06 14:30:23+09","2010-08-06 14:30:23+09",,t)
 (4096,"2010-08-07 04:02:09+09","2010-08-06 14:30:23+09","2010-08-06 14:30:23+09",,t)
...
 (1636,"2010-08-11 00:38:04+09","2010-08-06 14:30:23+09","2010-08-06 14:30:23+09",,f)
 (44,"2010-08-11 00:38:04+09","2010-08-11 00:38:04+09","2010-08-11 00:38:04+09",,f)
 (4096,"2010-08-10 04:02:08+09","2010-08-11 01:09:36+09","2010-08-11 01:09:36+09",,t)
(16 rows)

うーん欲しいのはこうじゃないんだよな。pg_stat_file()をfrom句に書いた時のように、

=# select * from pg_stat_file('.');
 size |         access         |      modification      |         change         | creation | isdir
------+------------------------+------------------------+------------------------+----------+-------
 4096 | 2010-08-11 01:14:49+09 | 2010-08-11 01:14:33+09 | 2010-08-11 01:14:33+09 |          | t
(1 row)

こういうのが欲しい。

=# select (pg_stat_file(pg_ls_dir)).* from pg_ls_dir('.');
 size  |         access         |      modification      |         change         | creation | isdir
-------+------------------------+------------------------+------------------------+----------+-------
     4 | 2010-08-11 00:38:04+09 | 2010-08-06 14:30:23+09 | 2010-08-06 14:30:23+09 |          | f
  4096 | 2010-08-11 00:38:04+09 | 2010-08-06 14:30:23+09 | 2010-08-06 14:30:23+09 |          | t
  4096 | 2010-08-07 04:02:09+09 | 2010-08-06 14:30:23+09 | 2010-08-06 14:30:23+09 |          | t
...
  1636 | 2010-08-11 00:38:04+09 | 2010-08-06 14:30:23+09 | 2010-08-06 14:30:23+09 |          | f
    44 | 2010-08-11 00:38:04+09 | 2010-08-11 00:38:04+09 | 2010-08-11 00:38:04+09 |          | f
  4096 | 2010-08-10 04:02:08+09 | 2010-08-11 01:11:06+09 | 2010-08-11 01:11:06+09 |          | t
(16 rows)

これこれ。なるほど、"(pg_stat_file(pg_ls_dir)).*"でいいのか。じゃあインチキだけどドキュメントを$PGDATAにシンボリックリンク。

$ cd data
$ ln -s path/to/src/postgresql-9.0beta4/doc/src/sgml/html

とここで誤算。PostgreSQLHTMLドキュメントはフラット構造なので再帰クエリー必要無いことに気づきました、、、まあいずれやってみるとして。

=# insert into pg90doc
-# select pg_ls_dir, contents, to_tsvector('english', contents)
-#  from (select pg_ls_dir, pg_read_file('html/' || pg_ls_dir, 0, size) as contents
-#          from (select (pg_stat_file('html/' || pg_ls_dir)).*, pg_ls_dir
-#                  from pg_ls_dir('html')
-#               ) as s1
-#       ) as s2;

なんか酷いけどできました。WITH使った方がいいのかな。

=# insert into pg90doc
-# with
-#  s1 as (select (pg_stat_file('html/' || pg_ls_dir)).*, pg_ls_dir from pg_ls_dir('html')),
-#  s2 as (select pg_ls_dir, pg_read_file('html/' || pg_ls_dir, 0, size) as contents from s1)
-# select pg_ls_dir, contents, to_tsvector('english', contents) from s2;
INSERT 0 1025

おーこの方が全然いいですね。サブクエリの内側に書いてたものを前側に書けた感じで、処理の順番通りになりました。やっぱWITH句強力だわ。モダンPostgreSQL使いの必修スキルですね。

最後に、8.4のドキュメントと9.0のドキュメントの単語の出現数の比較をやってみます。

=# with
 s84 as (select word, nentry from ts_stat('select vec from pg84doc')),
 s90 as (select word, nentry from ts_stat('select vec from pg90doc'))
select word, s90.nentry, s84.nentry, s90.nentry - s84.nentry,
  rank() over (order by s90.nentry - s84.nentry desc)
  from s84 join s90 using (word) limit 30;
    word    | nentry | nentry | ?column? | rank
------------+--------+--------+----------+------
 9.0        |   1077 |      3 |     1074 |    1
 name       |   4907 |   4458 |      449 |    2
 use        |   6218 |   5771 |      447 |    3
 standbi    |    464 |    132 |      332 |    4
 server     |   2467 |   2159 |      308 |    5
 function   |   5489 |   5207 |      282 |    6
 set        |   3083 |   2803 |      280 |    7
 pg         |   3104 |   2851 |      253 |    8
 valu       |   3827 |   3581 |      246 |    9
 paramet    |   2120 |   1888 |      232 |   10
 tabl       |   5089 |   4867 |      222 |   11
 file       |   2210 |   2028 |      182 |   12
 type       |   4608 |   4430 |      178 |   13
 wal        |    568 |    401 |      167 |   14
 default    |   1843 |   1678 |      165 |   15
 column     |   2750 |   2586 |      164 |   16
 row        |   2981 |   2819 |      162 |   17
 new        |   1860 |   1701 |      159 |   18
 allow      |   1563 |   1410 |      153 |   19
 specifi    |   1833 |   1686 |      147 |   20
 postgresql |   3133 |   2992 |      141 |   21
 return     |   2802 |   2661 |      141 |   21
 queri      |   2384 |   2244 |      140 |   23
 databas    |   2981 |   2843 |      138 |   24
 option     |   1752 |   1619 |      133 |   25
 string     |   1736 |   1603 |      133 |   25
 primari    |    396 |    266 |      130 |   27
 45         |    145 |     19 |      126 |   28
 trigger    |   1108 |    983 |      125 |   29
 user       |   2060 |   1936 |      124 |   30
(30 rows)
トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20100810

2010-08-01PostgreSQLのマニュアルでよく使われている英単語(をPostgreSQLで調べる) このエントリーを含むブックマーク

PostgreSQLの標準の全文検索機能に含まれるts_stat()関数を使って、ドキュメントでよく使われている英単語を調べてみます。

とにかくデータベースにドキュメントを入れてさえしまえば、htmlタグを除いたり、大文字小文字や複数形なんかはPostgreSQLが面倒見てくれます。

全文検索機能についてはこちらもご覧下さい。

=# create table pg90doc (path text primary key, contents text, vec tsvector);

テーブルはこんな感じ。実際のところ今回はtsvectorだけあればOK。

<?php
$contents = file_get_contents($argv[1]);
$dbh = new PDO("pgsql:dbname=db1 options=--client_encoding=iso88591");
$stmt = $dbh->prepare("insert into pg90doc values(?, ?, to_tsvector('english', ?))");
$rst = $stmt->execute(array($argv[1], $contents, $contents));

手抜きですが投入するプログラムはこんな感じ。client_encodingがiso88591になってるのと、to_vector()呼んでるあたりがポイントです。で、シェルから実行

$ for x in $(find postgresql-9.0beta3/doc/src/sgml/html -name '*.html'); do php importdoc.php $x; done

投入できたら、こんなSQLで単語の出現回数を調べることができます。

=# select rank() over (order by nentry desc),
 word, nentry from ts_stat('select vec from pg90doc');

 rank |               word                   | nentry
------+--------------------------------------+--------
    1 | use                                  |   6222
    2 | function                             |   5489
    3 | tabl                                 |   5085
    4 | name                                 |   4907
    5 | type                                 |   4605
    6 | valu                                 |   3826
    7 | data                                 |   3350
    8 | postgresql                           |   3131
    9 | pg                                   |   3101
   10 | set                                  |   3083
   11 | creat                                |   3006
   12 | databas                              |   2980
   12 | row                                  |   2980
   14 | return                               |   2802
   15 | column                               |   2750
   16 | index                                |   2651
   17 | oper                                 |   2575
   18 | command                              |   2551
   19 | server                               |   2463
   20 | queri                                |   2385
...

まあわりと普通な結果でした。何でか知らんけど38位にtomというのがいましたがw。

MSDNの上位だったretrieveは568位、specifiyは34位、elementは348位、containは57位、syntaxが214位でした。

他のドキュメントと単語の出現傾向を比較してみると面白いかもしれませんね。

SEAWorksSEAWorks2010/08/19 18:16見つけた。

KipKip2012/07/29 12:55That's 2 cleevr by half and 2x2 clever 4 me. Thanks!

jxwopxvdjxwopxvd2012/07/29 17:15DGLObQ <a href="http://auvcgjkqijse.com/">auvcgjkqijse</a>

ngkvwbtdqpngkvwbtdqp2012/07/30 12:54gMR4um , [url=http://bnzkmjirlzuv.com/]bnzkmjirlzuv[/url], [link=http://xghoiyukuphl.com/]xghoiyukuphl[/link], http://hmuzqwdhmnzd.com/

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20100801

2010-05-27

列の追加くらいすればいいのに

03:03 | 列の追加くらいすればいいのに - iakioの日記 を含むブックマーク はてなブックマーク - 列の追加くらいすればいいのに - iakioの日記

どんな新機能を追加するのにもスキーマの更新が必要だったが、DBが大きくなるにつれて、これは骨の折れる作業になる。1000万行のテーブルに新しい列をひとつ追加するのは、かなり時間がかかる。

InfoQ: Redditで学んだ7つのこと

Redditがどんなデータベースを使ってるか知りませんが、列を追加することが時間がかかるとは限りませんよ。

=# \timing
タイミングは on です。
=# create table r(i int);
CREATE TABLE
時間: 2.949 ms
=# insert into r select * from generate_series(1, 1000000);
INSERT 0 1000000
時間: 24829.790 ms
=# alter table r add t1 text;
ALTER TABLE
時間: 8.197 ms
=# alter table r add t2 text;
ALTER TABLE
時間: 2.422 ms
=# alter table r add t3 text;
ALTER TABLE
時間: 2.295 ms

DEFAULTとかを指定すると時間がかかっちゃいますが。

=# alter table r add t4 text default '';
ALTER TABLE
時間: 22492.818 ms

ats2019ats20192010/05/28 12:36RedditはPostgreSQLを使ってます:-)

http://coreblog.org/ats/japanese-translation-why-reddit-uses-python

default指定して列を追加するなどしてDBに負荷がかかり,サービス全体のクオリティに影響が出るのはイヤーンかもですね

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20100527

2010-02-14

LDD10Winterで発表してきた

14:33 | LDD10Winterで発表してきた - iakioの日記 を含むブックマーク はてなブックマーク - LDD10Winterで発表してきた - iakioの日記

LDD10Winterで「C言語でPostgreSQLを拡張する」というタイトルで発表してきました。

全部伝わらなくてもいいやと思う。データベースの話を一時間して、最も印象に残ったことがエディタの使い方であったとしても、それは聴いた人にとって価値があることだと思う。

人前で話すのが苦手な僕がプレゼンで気をつけていること - iakioの日記

と去年のosc2009-doの時に書いたのですが、ある程度そういう反響をいただいたようで嬉しいです。僕なんかはVim使いとしてはまだまだのレベルですが。手際良く見えるのはもちろんリハを何度かやったからです。

僕自身、他の方がコードを書くところを見るのは大好きです。

Win32API、MinGWでテトリス。コーディングも説明もすごいです。

札幌Ruby会議02の時の須藤さん。「よいプログラマは自生しない」っていいですね。すばらしい手際のコーディングは12分過ぎから。でもこの時Emacsだったけど今回Vimだった気が。

新山さんのPygame実況中継。まったり感がいいです。

1時間テトリスの人くらい上手に説明できればよかったのですが、前半は緊張でガチガチ、後半は完全に独り言いいながらコーディングって感じになってしまいました。でもプレゼンが下手でも見ている人に何か印象を残すことができたんじゃないかと思ってます。

普通、こういう場では多くの人に理解してもらえるように発表するわけですが、そうではなく、たまには「なんだかよくわからん」みたいに思われるのもいいんじゃないかと思ってます。そういうのを見て刺激を受ける人もいると思うので。要するに僕が見たいと思うものをやっているつもりです。

ソースコードはgithubにpushしておきました。質疑応答であったint -> ratのimplicit castも実装したので、

=# SELECT '1/4'::rat * 2;
    ?column?
   ----------
    1/2
   (1 row)

ということもできるようになっています。他にもやりたいことがあったけどまた次の機会に。

発表資料は今回はrst2s5で作りました。同じくgithubに置いてあります。一部本人の許可を得ずに引用していた部分はカットしてあります。

あとid:nazoneさんを軽くネタに使ってすいません。別に若手を潰そうとしてるわけじゃないですよ。むしろもちろん応援してます。

nazonenazone2010/02/14 14:59お疲れ様でした!
ぼくもvimユーザでC言語もわかるので、見てて楽しかったです。
あのくらいのネタ振りは楽しいので歓迎ですよー

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20100214

2010-01-23

SQLで==を使う

| 00:07 | SQLで==を使う - iakioの日記 を含むブックマーク はてなブックマーク - SQLで==を使う - iakioの日記

"SQLで間違って==とか書いちゃうよね"的なことを某所でいわれたので作ってみました。

http://gist.github.com/281672

create operatorでanyelementが使えるなんてやってみて初めて知りましたが、「こんなに短かくできるよ」を強調したかったのでanyelementにしたのであって、本来はそれぞれの型毎に作るべきでしょう。

たとえば8.4だと

=# select oprname, oprcode from pg_operator where oprname = '=';

 oprname |         oprcode
---------+--------------------------
 =       | int48eq
 =       | booleq
 =       | chareq
 =       | nameeq
...
 =       | enum_eq
 =       | tsvector_eq
 =       | tsquery_eq
 =       | record_eq
(59 rows)

と、データ型毎に59個ほど"="演算子が定義されているのでこれらと同じ定義で"=="を定義してあげれば何の問題も無いはずです。

トラックバック - http://postgresql.g.hatena.ne.jp/iakio/20100123