思いつくままプログラミングネタを書き殴ります。PostgreSQLとRubyを組み合わせてブラックリストチェックの速度を測ってみた。とある文章がブラックリストに載っているかどうかを判定しようと思っています。ブラックリストが4000レコードもあって、単純に見つかるまで処理すると遅いだろうと思い色々な方法で試して速度を測ってみました。それぞれ100回回しています。dirty_words返ってきた値に対して、それぞれでRubyの正規表現チェック8秒ということで、PostgreSQL側で一行にまとめて、Rubyで正規表現チェックするのが早いことがわかりました。▲Twitterのcreated_atをPostgreSQLのTIMESTAMP型に変換するto_timestampではタイムゾーンの解釈ができないけど、Twitterは+0000でしかこないので決め打ちで変換します。日本時間で管理したい場合は9時間足しておきます。▲ストアードプロシージャのノウハウ。複数行返すようなストアードプロシージャの時エラーを返したい場合に行で返すのが難しい場合があります。そこで例外を使います。plpgsql;例外を出すにはRAISEを使います。SQLSTATEには例外のコードを、MESSAGEには例外のメッセージが設定できます。例外のコードは5桁の文字列です。先頭の2桁が種類を、残り3桁がエラーの詳細を意味しているそうですが別にコードは何を使ってもかまわないのですし、受け取る側が解釈するだけなので適当でもいいです。PostgreSQLが内部で返すエラーコードが詳細に定義されているのでそれに被らないコードが望ましいです。先頭にUの文字を使っているエラーコードは事前に定義されていないので私はU0001からコードを振っています。メッセージには入力パラメーターの情報など渡すと確認しやすくなります。%を使うことで、いくつも変数をメッセージに付与できます。アプリケーションでは、メッセージをログにはいたり、エラーコードに合わせたユーザ向きのメッセージに変換して画面に表示したりしています。論理削除を削除フラグや削除日時のカラムで実装すると、カーディナリティが低いので検索でのパフォーマンスが良くないです。そこで、削除用のテーブルを作り移動することにします。スキーマーを以下の三つにわけます。source(全てのレコード)public(生きているレコードのみ)garbage(削除されているレコードのみ)sourceスキーマーにテーブルの定義を行いpublicスキーマーとgarbageスキーマーにはテーブル継承したテーブルを置きます。通常はpublicスキーマーを検索すればよいです。削除されているものも含めて欲しい場合、sourceスキーマーを検索できます。gabage.projects;削除される前はsourceとpublicのprojectsで1件の結果が帰ります。削除後はsourceとgarbageで1件の結果が帰ります。▲ストアードプロシージャのノウハウ。リターン編PostgreSQLのストアードプロシージャではRETURNが3種類あります。3種類を使ったサンプルが以下です。'hij'ストアードプロシージャ名を型に持つ関数です。SETOFの指定があるので複数行返せます。DECLAREとBEGINの間で定義しているのはローカル変数で、この関数が返す行を型にしています。QUERYはSELECTやRUTURNINGを使ったINSERT、UPDATEの値を戻せます。例では1行づつ2回呼んでますが、1回で複数行返すこともできます。QUERYは関数が終了しません。関数の終了はRETURNが単独で呼ばれた時だけです。NEXTは行を型にもつ変数を返す時に使用できます。行の各列の値を、色々な方法でセットしてから返したい場合などに便利です。RETURNは関数を終了させます。省略した場合、関数の最後に到達した時に関数が終了します。(12/28)PostgreSQLとRubyを組み合わせてブラックリストチェックの速度を測ってみた。