学習ノート

pg_prewarmについて

hahasoha — Sun, 24 Dec 2017 00:00:00 +0900

無駄に長い前置き

　これは、PostgreSQL Advent Calendar 2017の16日目の記事です。

　昨日はnuko_yokohamaさんの記事でした。

　PostgreSQL10では9.6で実装されたパラレルクエリが強化されており、集計・分析業務への適合性が高まっております。

　また、ある程度増減するとはいえ、CPUやメモリの価格も時の流れとともにどんどん安くなっています。

　数百GBくらいのデータであれば、データを全てメモリに乗せてしまって、マルチコアでパラレルクエリをぶん回してしまえば、大抵の場合はどうにかなってしまうのではないか……と、昔から考えております。

　当然、数十TBやPB級は難しいでしょうし、PostgreSQLはインメモリDBでもなければ列指向型DBでもありませんので、高速に集計をすることを前提に作られたDBMSと比較すると効率は良くないでしょう。

　ただ、記事執筆時点で、例えばDELL社のサイトでサーバを見積もると、CPU物理18コア2ソケットで物理36コア、メモリ32GBを8枚刺しの256GBくらいのマシンであれば、150万円～200万円程度で購入できてしまいます。

　この値段が高いかはケースバイケースではありますが、過去に構築した集計用システムを高速化したいだけであれば、ハードの一新とPostgreSQLのバージョンアップをするだけで十分に高速化する可能性があるわけで、工数を考慮すると、一概に高いとは言えないのではないかと思われます。

　ですが、ここまでのお話は、あくまでもデータがメモリに乗っていることが前提であって、そうでなければストレージがボトルネックになってしまいますので、いくらCPUがマルチコアであってもあまり意味がありません。(超高速SSDも台頭してきておりますが)

　……と、前置きが長くなりましたが、データをメモリに乗せるために、pg_prewarmを使いましょう、というのがこの記事の主題です。

　（思いがつくままに書き連ねたため、大分内容が発散しておりますが、予めご了承ください……）

pg_prerwarmの概要

　pg_prewarmはVer9.4から追加で提供されるようになったモジュールで、ざっくり言ってしまえばPostgreSQLのデータをメモリに乗せるためのモジュールです。公式ドキュメントでも、「プレウォームはキャッシュが主に空のとき、一般的には起動時にもっとも有用です。」と記載されています。

　CREATE EXTENSIONで一発で導入できますし、SELECT pg_prewarm('リレーション名')と実行するだけでメモリにデータが乗ります。

　この時、メモリへのデータの乗せ方を第2引数で「buffer」「prefetch」「read」の3種類から選ぶことができます。

キャッシュされる場所の違い

　「buffer」が指定された場合はPostgreSQLのshared_buffersにキャッシュされ、「prefetch」「read」の場合はOSのバッファキャッシュにデータがロードされます。

　shared_buffersの中身はpg_buffercacheモジュールで確認可能ですし、OSのバッファキャッシュも、Linuxの場合はlinux-ftools等でファイル単位に細かく確認可能です。

　（PostgreSQLのリレーションは$PGDATA/base配下にファイルで管理されますので、oid2name等でリレーションのFilenodeを確認できれば、どのリレーションがどのくらいOSにバッファキャッシュされているかも確認できます）

　データがキャッシュされていると一言にいっても、ざっくり「shared_buffersにだけキャッシュされている」「OSバッファだけにキャッシュされている」「shared_buffersとOSバッファに重複してキャッシュされている」の3パターンが考えられることになります。

　（最後のパターンは明らかに無駄が多いので、検討からは割愛します）

shared_buffersとOSのどちらにキャッシュさせるべきなのか？

　pg_prewarmのオプションで指定可能なことからも察せられるように、用途によって異なるというのが一般解ではあるかと思われます。

　手元の環境で、CPUがボトルネックになるような参照系クエリの処理時間を比較した限りでは、shared_buffersだろうがOSだろうが、どちらのキャッシュに乗っていても明確な処理時間の差は確認できませんでした。

　（OSキャッシュの場合は、shared_buffersにデータをコピーしてから処理をすることになる筈ですので、その分の影響が出るかとも思いましたが、特に遅延は確認されませんでした）

　今回の前置きに書いたように、主に参照系の分析用途にメモリを数百GBも積んで、全データをメモリに乗せてしまおう……という用途であれば、深く検証はできておりませんが、記事執筆時点ではOSのバッファキャッシュの方が「無難」ではないかと考えております。

　shared_buffersが極端に大きい状態ですと、チェックポイント処理が走った場合の負荷量に不安がありますし、ホストマシンのメモリ枯渇時のOOM Killerも考慮する必要が出てきます。ただ、チェックポイント処理もチューニングできますし、PostgreSQL関連プロセスをOOM Killerの対象外にすることも可能ですので、それらの対処コストを上回るメリットがあるのならば、shared_buffersに乗せてしまうのもありかもしれません。

　ただし、「buffer」を指定してprewarmした場合、既にshared_buffersに格納されている分のデータは再読み込みされないようですが、そうでない分のデータは、shared_buffersに乗ると同時に、OSのキャッシュにも同時に乗る模様です。

　「データはなるべくメモリに乗せたい。しかし同一サーバ上でPostgreSQL以外も動作していて、OSのキャッシュにも色々と乗せておきたい」……というようなケースでは、PostgreSQLのデータを重複してメモリに乗せるほど余裕はないのではないかと思われます。

「prefetch」「read」はどちらを使うべきなのか？

　公式ドキュメントにも記載がありますが、「prefetch」が動作するならば「prefetch」の方が処理速度が速いです。

　CentOS7上でltraceを使って確認した限りでは、「prefetch」だと内部的にposix_fadvise関数が、「read」はそのままread関数が呼び出されているようです。

　ストレージから初回読み込みをする際はストレージがボトルネックになりますので、どちらを選んでも速度的に大差はないかと思われますが、cronか何かでメモリに乗せる処理を定期的に実行するような場合は、「prefetch」の恩恵が大きくなるかと思われます。

pg_prewarm_all自作関数

　ここまでツラツラと書いてみて、大して有益な情報を書けていない感が強かったため、自身が接続しているDBのpublicスキーマ全体を問答無用でメモリに乗せようとする関数を書いてみました。（当然、pg_prewarmが使えることが前提です）

　pg_classからリレーションを取得してLOOPでpg_prewarmを回しているだけですので、少しクエリをいじれば条件は変更可能です。

---

CREATE OR REPLACE FUNCTION pg_prewarm_all()

RETURNS text AS $BODY$

DECLARE

sql text;

relnames text;

BEGIN

FOR relnames IN

SELECT relname::text FROM pg_class

WHERE

relnamespace = (

SELECT oid FROM pg_catalog.pg_namespace

WHERE

nspname = 'public'

)

AND

relkind IN('r','i')

LOOP

sql := 'SELECT pg_prewarm(''' || relnames || ''',''buffer'')';

EXECUTE sql;

END LOOP;

RETURN 'OK';

END $BODY$

LANGUAGE plpgsql;

---

駄文でしたが、最後までお読み頂きありがとうございました。

最終日は、soudai1025さんです。

さくらのレンタルサーバライトでContent-Encoding: gzipの無効化について

hahasoha — Tue, 25 Apr 2017 23:04:22 +0900

　このブログがさくらのブログであることからも明らかですが、さくらのレンタルサーバライトを利用して、いくつかのWebサービスを運用しております。

　何年も前に青空文庫形態素解析データ集というサイトを作っておりまして、このサイトもさくらのレンタルサーバ上にコンテンツを配置しております。

　その名の通り、青空文庫で公開されているデータを形態素解析した結果をzipやらgzipでダウンロードできるサイトなのですが、先日gzip圧縮されているファイルをダウンロードしたところ、拡張子はgzなのに、実際は解凍済みのファイルがダウンロードされるという事象が発生しました。

　一括ダウンロードのページでは、わざわざ解凍前と解凍後のサイズを明記しているというのに…

　オペミスか何かで解凍済みのファイルを拡張子をgzとして配置してしまったのかとも思いましたが、サーバに格納されているファイルを確認すると、ちゃんとgzip圧縮されたファイルが配置されていました。

　どうも、Content-Encodingのgzipが有効になっていて、圧縮転送がされているとブラウザ(Chrome)が判断して、自動的に解凍してしまっている模様です。

　はて、Content-Encodingのgzipとは、意図的に設定しないでも勝手につくものだったのかしら…ということも気になりますが、とりあえずはブラウザで解凍されないようにすることが先決ですので、方法を調べてみました。

　で、思ったより苦戦しましたが、とりあえず、.htaccessに以下を追記することで、Chromeでは解凍されずにダウンロードできるようになりました。

AddEncoding identity .gz

　さて、以下は上記に至るまでの経過を示していきます。内容に不備があるようであれば、有識者の方は突っ込みをお願いいたします。

　そもそも、ブラウザがgzファイルを解凍してしまうのは、HTTPレスポンスヘッダのContent-Encodingにgzipが含まれているからではないかと推測し、Wiresharkでパケットを確認した結果、確かに「Content-Encoding: gzip」が含まれていました。

　ちなみに、他のサイトでgzファイルをダウンロードしても自動的に解凍されませんでした（レスポンスにContent-Encodingそのものがなかった）ので、やはりサーバの設定の問題のようです。

　で、ブラウザに解凍をさせたくない場合は、Content-Encodingヘッダそのものをつけないのが正しいようですので、初めはmod_headersを用いて以下のようにしてみましたが、どうにもContent-Encodingヘッダが削除されませんでした。

Header unset Content-Encoding

　ETagヘッダで試してみると問題なく削除できましたので、機能が動作していないということはない筈なのですが…

　あまり自信のない推測ですが、Content-Encodingヘッダはレスポンスヘッダの末尾についておりますので、除去された後にもう一度付与されている…のでしょうか。（私の単なる勘違いだったら申し訳ないですが）

　次は、Content-Encodingを削除することは一旦あきらめて、内容を変更する方法で検討しました。

　AddEncodingで変更できることはわかったのですが、何の文字列に変更すべきかがわからない…

　RFC2616を見る限りでは…以下のような記載がありますので、やはり、identityが一番近いように読めます。

identity The default (identity) encoding; the use of no transformation whatsoever. This content-coding is used only in the Accept- Encoding header, and SHOULD NOT be used in the Content-Encoding header.

　SHOULD NOTという表現は、「しないほうがよい」というレベルで、「してはならない」ではないようですので、ご勘弁を。

（長時間かけてググったわけではないですが、同じ事象で困っている人は見つけられませんでしたので、何か私が根本的な勘違いをしている気もしておりますが…）

BVH WEB VIEWERを公開しました

hahasoha — Sun, 12 Mar 2017 16:31:28 +0900

BVH WEB VIEWERを公開しました。
http://motion.hahasoha.net/

　ひょんなことからVRに触れることになり、関連しそうな情報を色々と調べていたところ、カーネギーメロン大学が公開しているモーションデータをbvh形式に変換して公開しているサイトを見つけました。

　必要なツールをダウンロードしてbvhファイルを読み込めば、色々なモーションが見れてなかなか楽しいです。
　…が、もっと気軽に検索できて、パパッとモーションを閲覧できる方法がないか調べた結果、どうも適合するサイトはなさそうでしたので、冒頭のBVH WEB VIEWERを作成し、公開しました。

　作成したといっても、three.jsのサンプルコードも殆ど流用させてもらっていますし、手抜き感は否めませんが…
　パパッとモーションを検索して、ブラウザで動作を確認してそのままbvhファイルをダウンロードできるようにしましたので、使い勝手は悪くはないかと思います。（手元のスマホで表示をしたらiframeが荒ぶったため、とりあえずPC限定としていますが）

　ただ、実際に動きを見ても、何の動きなのかわからないのも多数ありますが…（Alaskan vacationって何なんでしょう…？）

　3Dコンテンツの作成は完全に門外漢なので、どれくらいの需要があるのか把握できておりませんが、bvhファイルはBlenderではそのまま使えるようですし、ドリームファクトリーのLiveAnimationで変換すればMMDでも使える模様です。

　2016年はVR元年と言われていますが、3D系の素材に対する需要・供給も増加していきそうですね。

Raspberry PiのGPUアンダークロックと消費電力について

hahasoha — Sat, 13 Feb 2016 00:00:00 +0900

前日の記事に引き続き、今度はCPUに加えてGPUもアンダークロックした場合の消費電力について調査しました。

使用したモバイルバッテリや条件は以前の記事をご参照ください。

まず、/boot/config.txt に gpu_freq=100 と追記してPi Zeroを再起動しようとすると、ディスプレイが点滅を繰り返し、正常起動できませんでした。（公式サイトによると、デフォルトは250だそうです）

HDMIを接続して画面表示をしようとしていたので、もしかしたらHDMI接続をしなければ起動可能なのかもしれません。

で、このままだと起動できなくて困ってしまうわけですが、電源投入時にShiftキーを押下してリカバリモードに入れば、/boot/config.txt の内容を編集できるGUIが表示されます。

gpu_freqを100から150に変更したらHDMIを接続した状態での起動に成功しました。

前述の公式サイトに寄るとcore_freq, h264_freq, isp_freq,v3d_freqも一緒にセットしろと書かれているようですので、あわせて150に設定してみました。

結果としては、9時間36分間、起動し続けました。

これまでに確認したZeroの起動時間をまとめると以下のようになります。

　8時間30分　デフォルト状態

　8時間48分　arm_freq 100

　9時間36分　arm_freq 100、gpu系5種 150

繰り返しテストしたわけではありませんので信頼性は高くはありませんが、GPU系のアンダークロックは10%ほど消費電力を抑える効果が見込めそうですね。

Raspberry PiのCPUアンダークロックと消費電力について

hahasoha — Thu, 11 Feb 2016 13:17:52 +0900

Raspberry Piとモバイルバッテリを使った調査をこれまでに色々とやっているわけですが、アンダークロックと消費電力について調査してみました。

Raspberry Piのクロック数はraspi-configや、/boot/config.txtの編集で簡単に変更可能です。

arm_freqを100まで落とし、例の如く、Pi Zeroで以下のモバイルバッテリでどれだけの時間起動できるのか確認してみました。（その他の条件については前回と同様です）

結果としては、8時間48分間、起動し続けました。

arm_freqの値を編集していない状態で8時間30分でしたので、差がないわけではないが･･･といったところです。

アンダークロックと消費電力については公式フォーラムでも過去に議題にあがっており、arm_freqの値を小さくしても、消費電力には殆ど影響が出ない･･･というのが正しい挙動のようです。

ただ、容量1,430mAhのモバイルバッテリで一応は20分弱は起動時間を延長できたわけですから、もっと容量の大きなモバイルバッテリを利用すれば、その分、延長時間も大きくなるものと思われます。

また、前述の公式ドキュメントやフォーラムによると、どうもGPUもアンダークロックできそうですので、次回はgpu_freq,core_freq, h264_freq, isp_freq,v3d_freqの値を小さくして調査してみます。

Raspberry Pi Zeroの体積について

hahasoha — Sun, 07 Feb 2016 13:40:23 +0900

Raspberry Pi Zeroを実際に手にしてみると、その小ささ･･･というより、「薄さ」に驚きます。

上の画像は、A+と並べて撮影したものです。

Zeroの端がA+の下に入っていますが、これはA+をZeroの上に乗せているわけではありません。

A+は裏面にmicroSDカードスロット等のモジュールがありますが、Zeroは裏面には何もなく、より薄くなっているため、触れずに下に差し込めるほどの差があります。

簡単な計測ですが、A+はGPIO部まで含めて大体13～14mm、Zeroは6～7mmといったところです。

上から見た面積的にはZeroはA+の約半分ですが、体積的には約1/4ということになります。

当然、ZeroにもGPIOピンをつければ同じ厚さにはなりますが、個人的には、Zeroの最大のメリットは、この体積の小ささにある･･･と申しますか、このメリットを生かさなければ、Zeroを使う意味があまりないのではないかと考えています。

カバーをつけると厚さが増してしまいますし、カバーがZero本体よりお高くなる･･･なんてことにもなりますので、カバーはつけずに、小さな空間にガンガン組み込んでいき、壊れたら別のZeroに差し替える･･･という使い方が正しい使い方になりそうです。

（現状ではまだ入手困難であるためあまり贅沢な使い方はできませんが）

次の記事では、なるべくお手軽かつ省スペースにZeroを利用する方法について検討してみます。

Raspberry Pi Zeroの待機電力調査

hahasoha — Sun, 07 Feb 2016 12:07:14 +0900

Pimoroniで注文していたRaspberry Pi Zeroが届きました。

これでB2、A+、Zeroと揃いましたので、完全に素の状態での待機中の消費電力を比較調査してみました。

満タン状態のUSBモバイルバッテリを接続し、起動時刻からバッテリ切れでの停止時刻までを測定します。

（cronで毎分現在時刻をテキストに追記し、最初と最後の時刻を比較するだけです）

OSはNOOBS 1.5.0でいれたRaspbianで、cron以外の設定や更新は全く行いません。

microSDカードとUSBモバイルバッテリしかラズパイには接続せず、microSDカードは使い回します。

モバイルバッテリは容量1,430mAh の以下の製品を利用します。

早速、結果です。

Zero、A+、B2、Zero、A+、B2の順番に2回ずつ実施した平均値です。

　8時間30分　Zero

　8時間29分　A+

　3時間52分　B2

一応はZeroが一番長く持ちましたが、A+との差は誤差の範囲ですね。

B2の短さが目立ってはしまいますが、性能に大きな差がありますので、これは当然の結果でしょう。

色々とパーツが少ない分、ZeroがA+より長持ちするかとも思いましたが、CPUやメモリの性能がA+より若干高いため、相殺されているのかもしれません。

（個体差もあるのでしょうが）

USB機器の接続を想定した場合、ZeroはmicroUSBなのに対し、A+は普通のUSBがそのまま刺さります。

普通のUSBをZeroで利用する場合は変換コネクタが必要になりますので、その分、A+より消費電力は不利になるかもしれません。

Zeroの記事なのに、「やるじゃないかA+･･･」という感じになってしまいましたが、次の記事では、Zeroの利点をクローズアップしたいと思います。

Raspberry Pi A+の省スペース設置

hahasoha — Mon, 01 Feb 2016 23:44:39 +0900

Raspberry Pi A+をなるべくスッキリ設置する方法を考えてみました。

モバイルバッテリも魅力的ではありますが、サーバ的に利用するのであれば、やはりコンセントからの給電が現実的です。

で、可能な限りケーブルレスにしたらどのようになるか検討した結果、以下のようになりました。

とりあえずGPIOは全くつけず、無線LANで通信は可能な状態としています。

「壁と水平にUSB差込口がある充電器」と「USB_microUSB変換コネクタ」を併用しています。

この充電器の厚さは17.5mmで、A+のGPIOが僅かに飛び出ますので、実質、壁から20mm分のスペースがあれば設置可能です。

（ラズパイにケースをつける場合は、当然その分の厚さが必要ですが）

ラズパイ本体やUSBケーブルの固定方法について考慮する必要がなく、極めて単純にスッキリ設置できます。

･･･ただ、上記の2商品は送料込みであわせて900円程で、決して高くはない筈なのですが、microUSBの充電器自体は100円ショップでも購入可能ですし、Raspberry Pi Zeroの約2倍か･･･と考えてしまうあたり、毒されているなあと感じます。

Raspberry Pi 2 Model Bの消費電力調査

hahasoha — Sun, 06 Sep 2015 21:09:16 +0900

以前の記事で、Raspberry Pi 2 Model Bの消費電力が900mAであることに触れましたが、このソースを確認できずにおります。

日本語版のWikipediaの記事には「900 mA (4.5 - 5.5 W)」と記載されているのですが、この記事はソースが示されておりません。

また、英語版のWikipediaの記事では「800 mA (4.0 W)」とされており、こちらはソースらしきものも示されています。

で、そもそも公式サイトに示されていないのか調べてみた結果、FAQのPOWERの項目でかなり詳細に記載されていることがわかりました、が･･･

参考になることが大量に書かれてはおりますが、何故かPi2Bの「Typical bare-board active current consumption（典型的な裸基盤での有効消費電流）」のみが空欄となっています。

英文をざっと確認した限りでは、空欄である理由にも触れていないようですが･･･

CPUが4コアという事で、値が大きく上下するので、典型的な値を示していない･･･のかもしれません。

で、先日せっかく電力チェッカを購入したのですから、Pi2Bもいくつかのパターンで電力量を調べてみたいと思います。

(1)	Raspberry Pi 2 B 単体　待機状態	0.19A
(2)	(1)+HDMI+USBキーボード	0.20A
(3)	(1)+有線イーサ　待機状態	0.24A
(4)	(1)+有線イーサ　ファイルDL	0.28A
(5)	(3)+bash無限ループx1	0.26A
(6)	(3)+bash無限ループx2	0.30A
(7)	(3)+bash無限ループx3	0.32A
(8)	(3)+bash無限ループx4	0.35A
(9)	(8)+ファイルDL	0.36A

待機電力こそA+の3倍以上もありますが、CPUをカツカツの状態にしても思ったよりは消費量は上昇しませんでした。

裸基盤の状態であと電力を消費できそうなものといえば、GPUとメモリくらいでしょうか･･･

ただ、USB機器を接続しない状態であれば、500 mAが必要になることすら稀のような･･･

一応は、以前に上げた記事で紹介したモバイルバッテリでも、上記の(8)の状態でしばらく放置しても継続して動作することも確認できました。

いつ停止してファイルシステムが破損するかわかったものではありませんので、全く推奨はできませんが･･･

軽量低容量のモバイルバッテリでも起動自体は可能ということは、把握しておいても損はないかもしれませんね。

Raspberry Pi A+の消費電力について

hahasoha — Tue, 18 Aug 2015 23:17:31 +0900

以前の記事でRaspberry Pi A+の連続運転テストを実施した際は、想定よりもかなり消費電力が少ないであろう結果が出ました。

で、実際の消費電力を確認するために、以下の商品を購入しました。

購入時の価格は、送料込みで217円･･･そこまで安くなくても良いのに･･･

流石に、この価格帯の商品に厳密な値を求めるつもりはありませんが、しばらく使ってみた限りでは、凡そ正しそうな値を表示してくれているようです。

ということで、複数パターンでザッと数値を確認してみました。

(1)	Raspberry Pi A+ 単体　待機状態	0.06A
(2)	(1)+bash無限ループ	0.09A
(3)	(1)+有線USBイーサ　待機状態	0.14A
(4)	(1)+有線USBイーサ　ファイルDL	0.20A
(5)	(1)+無線USBwifi　待機状態	0.14A
(6)	(1)+無線USBwifi　ファイルDL	0.23A
(7)	(5)+bash無限ループ	0.17A

有線USBイーサはこちらを利用しています。

Wifiは以前のテストの際と同じGW-USNano2です。

ファイルDLはhttpのwgetで数百MBのファイルをSDカードに書き出す形でダウンロードしています。

bash無限ループは「while : ; do i=$((i+1)) ; done」でループさせています。

以前のテストの際はCPUは枯渇状態にはしないで、GPIOで温度センサを稼動させていましたので、(6)よりやや重い負荷なのではないかと思われます。

さて、今回購入した電圧チェッカでは瞬間的な値までは取得できませんので、前述の表以上の電力が消費されないことを保証するわけでは全くありませんが、それでも大分少な目の値となっております。

これならば、モバイルバッテリでも、もう少し負荷をかけても大丈夫そうな気がしますね･･･

Raspberry piのファイルシステム破損による起動失敗とリカバリ方法

hahasoha — Wed, 12 Aug 2015 00:00:00 +0900

それまでは問題なく利用していたRaspberry piが、突然起動しなくなってしまいました。

どうやらファイルシステムが破損してしまいrootをマウントできていない模様。

具体的には、コンソールの最終行に「end Kernel panic - not syncing: VFS: Unable to mount root fs on unknown-block(179,6)」と出力されて停止している状態でした。

SDカードリーダが搭載されているWindowsノートPCでリカバリができましたので、手順をまとめておきます。

（NOOBSでRaspbianを入れた環境です）

また、以下の工程では、オペミス等でリカバリに失敗して完全にデータが失われてしまう可能性もあるため、事前にWin32DiskImager等でディスクイメージ毎バックアップを取っておくことをお勧めいたします。

（ファイルシステムが破損する前にバックアップを取得しておけばよいわけですが…）

SDカードを認識してfsckコマンドを実行できる環境の準備

結局はこの副題が全てなわけですが、該当するようなLinuxマシンが手元になかったため、WindowsのノートPC上に仮想環境を構築しました。

VirtualBoxとVMware Playerの両方で試してみましたが、個人的にはVMware Playerの方がSDカードの認識部分においてわかりやすかったです。

VMware Playerは個人用途ならば無償で利用できるため、適時ダウンロードして導入してください。

OSはFedoraをお勧めします。

当初は使い慣れていて既に環境構築済みのCentOSでやったのですが、カードの認識がうまくいかなかったりドライバが足りなかったりと、最終的に成功したとはいえ、色々と面倒でした。

で、Fedoraならば新し目の要素も対応しているかなと思い試してみた結果、追加で何も設定しない素の状態でSDカードを認識して、リカバリまで持っていけました。

デバイスを接続してfsckを実行

VMware Playerでは親機に接続されている機器を仮想マシンに認識させることが可能ですので、ノートPCに搭載されているSDカードリーダにカードを差し込みます。

この時、初めからカードを差し込んだ状態で、仮想マシンを後から起動しても問題ありませんが、複数のデバイスが選択可能な場合、どれがSDカードリーダなのか案外わかりにくかったりします。

仮想マシンを起動後にカードを差し込みますと、新規の取り外し可能デバイスとして、以下のようなウィンドウがポップアップされますのでわかりやすいかと思います。

その後、実際に仮想マシンに認識させる際は、VMware Playerのメニューを「Player」→「取り外し可能デバイス」→「SDカードリーダ名」→「接続（ホストから切断）」の順番で選択します。

うまく認識してくれると、/dev配下にデバイスが追加されます。

筆者の環境の場合では、以下のようにデバイスが追加されました。

（CentOSでやった際は/dev/sdb～として認識されましたので、必ずしも下記の限りではありません）

mmcblk0

mmcblk0p1

mmcblk0p2

mmcblk0p3

mmcblk0p5

mmcblk0p6

で、とりあえず一通りのデバイスにfsckを打ち込んでみましたが、mmcblk0p6にエラーがあったようで、結構な数のエラーが出力されました。

出力されるエラーに対しては、基本的にyで修復を了承していくだけで問題ありません。

修復が完了後は、Raspberry Piに戻して起動できるか確認しましょう。

（ファイルの救出が目的の場合は、Raspberry Piに戻さず、仮想環境から直接マウントしてファイルを取得することも可能です）

Raspberry Pi A+のモバイルバッテリ連続運転テスト

hahasoha — Sat, 08 Aug 2015 00:00:00 +0900

日本Raspberry Pi ユーザーグループ代表の太田さんが書かれているRaspberry Piの今という記事に、以下のような記載があります。

Ebenからは汎用としてのRaspberry Pi 2と組み込み機器などの利用で省電力性の高いRaspberry Pi A+が主力製品になるだろうとの話を受けています。

私も少々Raspberry Piを扱わせて頂く機会があって、色々な使い道を模索しているのですが…

確かに、Raspberry PiをRaspberry Piらしく使うのであれば、「Pi 2 Model B」より、「Pi 1 Model A+」の方が適しているケースも多いのではないかと思います。

A+が2Bより優れている点は、「価格」「消費電力」「大きさ」「重さ」でしょう。

価格は日本国内ではAmazonで購入すると以下のような価格です。

その他の項目についてはWikipediaの記事をご覧頂いた方が早いかとは思いますが、前述の4項目の中で、A+が2Bより最も優れているのは「消費電力」です。

2Bが900 mAなのに対し、A+は単体で僅か200 mAです。

（A+のCPUが700MHz1コアなのに対し、2Bは900MHz4コアですから当然の差とも言えますが）

（知識不足のため、「電力」「電流」等の用語の使用の誤謬があるかもしれません）

この消費電力の少なさを最大限に生かそうと考えると、モバイルでの用途が思いつきます。

モバイルでの利用について、既に先人の方々が記事を挙げられていますが、自身の調査結果も記事にまとめておきたいと思います。

調査対象モバイルバッテリ

Raspberry PiはマイクロUSBから給電をしますので、スマホ用のモバイルバッテリからも給電が可能です。

ということで、価格.comで最安値となっていた以下の商品を購入してみました。

記事執筆時点では、送料無料で680円で購入できました。

モバイルバッテリってこんなに安いものもあるんですね…

ちなみに、Amazonのリンク先の説明には「USB出力:DC5V1A」とあるのですが、これはウソです。

Panasonicの同商品の詳細仕様には「USB出力　DC5V 0.5A」と明記されています。

本体の裏面にも定格出力0.5A(MAX)と印字されています。

0.5Aとは500 mAのことですので、つまり、A+は動作しても2Bは起動すらできないことになります。

本製品に限らず、出力可能な電圧電流は極めて重要ですので、なるべく正確な情報を確認してから購入するようにご注意ください。

ちなみにこの商品は、Panasonicのサイトに掲載されている写真をご覧頂ければわかりますが、出力用のマイクロUSBが本体に一体化していて、かつ重さが約58gしかありません。

A+の重さは23gですので、合わせても余裕で100g未満ということになります。

容量は1430mAhで、出力時間の目安は満充電した状態で500 mA出力で約1.3時間とのことです。

また、バッテリ残量をランプの色で確認可能で、残り約60%以上までは緑、残り約30%以上までは橙、残り約30%以下で赤く点灯します。

連続運転テスト条件

このモバイルバッテリーで、A+がどれだけ動作できるか連続運転テストを行いたいと思います。

が、何の機器も接続していない状態のA+なぞ全く実用的ではありませんので、Wifiと気温センサを実装させます。

Raspberry Piは素のままではWifiは利用できませんので、アダプタを追加する必要があります。

で、Amazonで安そうなものを見繕うと以下のような商品が出てきますが、ここでも消費電力が重要な要素となります。

これらの商品は大体同じような価格・スペックに見えますが、それぞれの商品の製品仕様を確認すると、白い方のGW-USNano2の最大消費電力は最大約1.3Wで、黒い方のWLI-UC-GNMは最大約2.5Wと、2倍近くも差があります。

これらの値は、mAに換算するとGW-USNano2は260 mA、WLI-UC-GNMは500 mAとなりますので、前述のモバイルバッテリでは、WLI-UC-GNMを利用していると電圧不足で動作不良を起こす可能性が高いです。

なので、今回は消費電力の低いGW-USNano2を採用します。

（Raspberry Piでの利用実績も多いようで、色々なサイトで報告例を見かけます）

モバイルバッテリの出力が500 mAで、A+単体で200 mA、Wifiで260mA消費しますので…

一応の計算上は、センサに残り40 mA使えることになります。

（実際は余裕を持たせるべきでしょうが）

で、気温センサは以下の書籍を参考に、秋月電子でいくつか商品を購入して自作しました。

非常に丁寧に書かれた書籍ですので、Pythonやら組み込み系やらが全くの未経験の方にもお勧めできます。

気温センサ等の消費電流は125 μA等と、単位がマイクロですので、残り40 mAでもまだ余裕がありそうです。

これらを組み合わせて「気温センサから値を毎秒取得し、その都度HTTPのGETパラメータでサーバに情報を送信し続ける」という条件でテストを行いました。

※ランプが消灯するまで使い続けるとデータ破損が発生する可能性が高いため、実際には、バッテリの限界まで利用すべきではありません

テスト結果

結論から書いてしまえば、約5時間連続して動作しました。

センサから情報を取得し続けて毎秒HTTPGETするという、それなりに実用的かつ負荷がかかるであろう条件でしたが、思いの外、長時間動作しました。

（電圧・電流チェッカは後日購入しようかと思います）

また、10秒毎に写真を撮影し続けるという簡易スクリプトを別のRaspberry Piで動作させ、モバイルバッテリのランプ色の変化も合わせて調査しました。

経過時間	ランプ色	残電量
開始時点	緑	100%
開始から146分経過	橙に変化	60%
更に101分経過	赤に変化	30%
更に51分経過	消灯	0%

赤に変化するまでは大体10%を30分ペースで消費していましたが、ランプが赤になってから消灯するまでの時間は急激に短くなっています。

ただ、ランプが消灯して電力が供給されなくなる直前まではRaspberry Piは正常に動作をし続け、以下のように、取得した温度センサの値にも異常は見られませんでした。

エアコンの温度設定を色々と調整して確認しましたが、大体想定通りの値が出ています。

※ちなみに、19時あたりに急激に温度が下がっているのは、温度センサにあずきバーを接近させたためです。

前述のように、今回利用したモバイルバッテリの容量は1430mAhしかありませんが、それでも数時間も動作するのであれば、電源を確保できないような環境でも、お手軽に様々な用途に利用できるのではないかと思います。

安価かつ軽量であることを利用して、GPSやジャイロセンサと組み合わせるのも面白いかと思いますので、また別途調査したいと思います。

PostgreSQLパズル？

hahasoha — Tue, 16 Dec 2014 18:00:00 +0900

これはPostgreSQL Advent Calendar 2014の12月16日分の記事です。

昨日はmoomindaniさんの記事でした。

---宣伝　ここから---

来年の1月16日(金)にPostgreSQLパフォーマンスチューニング実例セミナーを開催することになりました。

ご興味のある方は是非ご応募ください。

---宣伝　ここまで---

数あるデータベースの中からPostgreSQL……というより、RDBMSを採用する理由の一つとして、複雑なクエリが実行できることが挙げられます。

スケールアウト等を考えると、重たい処理をDBで実行させるのはあまり褒められた話ではないとも思いますが、趣味でやっているWebサービスレベルであれば、そもそもマシンが1台しかないなら、なるべくDB内で処理を完結させた方が効率的なこともあります。

で、先日、とある処理をDB内で完結させようとした結果、思いのほかドツボに嵌まり、個人的には相当カオスなSQLが生まれてしまいましたので、ここで紹介したいと思います。

（と申しますか、もっと美しいSQLがある筈……）

現在、Microsoft Office Wordの自動索引登録機能を補助することに特化したサイトを趣味で作っているのですが、wordファイル中から索引候補を抽出する処理でtextsearch_jaのja_analyzeを利用させて頂いています。

例えば、PostgreSQLの公式ドキュメントの一部をja_analyzeにかけると以下のような結果が取得できます。


 =>SELECT word,type,ruby FROM 
   ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に 
               関する情報を収集し、報告するサブシステムです。'); 

      word     |  type  |     ruby 
 --------------+--------+-------------- 
  PostgreSQL   | 名詞   | PostgreSQL 
  の           | 助詞   | ノ 
  統計         | 名詞   | トウケイ 
  情報         | 名詞   | ジョウホウ 
  コレクタ     | 名詞   | コレクタ 
  は           | 助詞   | ハ 
  サーバ       | 名詞   | サーバ 
  の           | 助詞   | ノ 
  活動         | 名詞   | カツドウ 
  状況         | 名詞   | ジョウキョウ 
  に           | 助詞   | ニ 
  関する       | 動詞   | カンスル 
  情報         | 名詞   | ジョウホウ 
  を           | 助詞   | ヲ 
  収集         | 名詞   | シュウシュウ 
  し           | 動詞   | シ 
  、           | 記号   | 、 
  報告         | 名詞   | ホウコク 
  する         | 動詞   | スル 
  サブシステム | 名詞   | サブシステム 
  です         | 助動詞 | デス 
  。           | 記号   | 。 
 (22 rows)

一見すると問題なさそうですが、索引候補とするには、名詞が細かく分割され過ぎています。

索引候補としては「統計情報コレクタ」で一つの語句として抽出したいわけです。

どういった条件で一つの語句とするかは色々な考え方があるのでしょうが、今回は「名詞が連続している部分を抽出する」ことを条件としたいと思います。

つまり、以下のような結果を取得するためには、どのようなSQLを書いたら良いか、ということです。


        word       |            ruby 
 ------------------+---------------------------- 
  統計情報コレクタ | トウケイジョウホウコレクタ 
  活動状況         | カツドウジョウキョウ 
 (2 rows)

まず、「連続している名詞」を抽出しなければならないわけですが、これは先日の記事でも紹介したWindow関数で抽出できます。

後の処理のために、row_numberも取得しておきます。


 =>SELECT row_number () OVER (), 
          lag (type) OVER (), 
          lead (type) OVER (), 
          word,type,ruby FROM 
   ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に 
               関する情報を収集し、報告するサブシステムです。'); 

  row_number |  lag   |  lead  |     word     |  type  |     ruby 
 ------------+--------+--------+--------------+--------+-------------- 
           1 |        | 助詞   | PostgreSQL   | 名詞   | PostgreSQL 
           2 | 名詞   | 名詞   | の           | 助詞   | ノ 
           3 | 助詞   | 名詞   | 統計         | 名詞   | トウケイ 
           4 | 名詞   | 名詞   | 情報         | 名詞   | ジョウホウ 
           5 | 名詞   | 助詞   | コレクタ     | 名詞   | コレクタ 
           6 | 名詞   | 名詞   | は           | 助詞   | ハ 
           7 | 助詞   | 助詞   | サーバ       | 名詞   | サーバ 
           8 | 名詞   | 名詞   | の           | 助詞   | ノ 
           9 | 助詞   | 名詞   | 活動         | 名詞   | カツドウ 
          10 | 名詞   | 助詞   | 状況         | 名詞   | ジョウキョウ 
          11 | 名詞   | 動詞   | に           | 助詞   | ニ 
          12 | 助詞   | 名詞   | 関する       | 動詞   | カンスル 
          13 | 動詞   | 助詞   | 情報         | 名詞   | ジョウホウ 
          14 | 名詞   | 名詞   | を           | 助詞   | ヲ 
          15 | 助詞   | 動詞   | 収集         | 名詞   | シュウシュウ 
          16 | 名詞   | 記号   | し           | 動詞   | シ 
          17 | 動詞   | 名詞   | 、           | 記号   | 、 
          18 | 記号   | 動詞   | 報告         | 名詞   | ホウコク 
          19 | 名詞   | 名詞   | する         | 動詞   | スル 
          20 | 動詞   | 助動詞 | サブシステム | 名詞   | サブシステム 
          21 | 名詞   | 記号   | です         | 助動詞 | デス 
          22 | 助動詞 |        | 。           | 記号   | 。 
 (22 rows)

lagに1行前の、leadに1行後のtypeが表示されます。

ウィンドウ演算が行われた結果に対してフィルタ処理を掛ける場合は副問い合わせを利用します。

また、この後の処理のために、row_numberに対してCASE文を打ちます。


 =>SELECT CASE 
            WHEN lag = '名詞' THEN 1 
            ELSE row_number 
          END num, 
          row_number,word,ruby 
   FROM 
    ( 
     SELECT row_number () OVER (), 
            lag (type) OVER (), 
            lead (type) OVER (), 
            word,type,basic,ruby FROM 
     ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に 
                 関する情報を収集し、報告するサブシステムです。') 
    ) foo1 
   WHERE (lead = '名詞' AND type = '名詞') 
     OR  ( lag = '名詞' AND type = '名詞'); 

  num | row_number |   word   |     ruby 
 -----+------------+----------+-------------- 
    3 |          3 | 統計     | トウケイ 
    1 |          4 | 情報     | ジョウホウ 
    1 |          5 | コレクタ | コレクタ 
    9 |          9 | 活動     | カツドウ 
    1 |         10 | 状況     | ジョウキョウ 
 (5 rows)

lagを利用し、1行前が名詞だったら1、そうでなければrow_numberを表示させます。

これにより、連続している名詞の切れ目でnum列に最大値が現れることになります

次は、この結果に対して更にWindow関数を用いて副問い合わせします。


 =>SELECT max(num) OVER (ORDER BY row_number),word,ruby FROM 
   ( 
    SELECT CASE 
             WHEN lag = '名詞' THEN 1 
             ELSE row_number 
           END num, 
           row_number,word,ruby 
      FROM 
     ( 
      SELECT row_number () OVER (), 
             lag (type) OVER (), 
             lead (type) OVER (), 
             word,type,basic,ruby FROM 
      ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に 
                  関する情報を収集し、報告するサブシステムです。') 
     ) foo1 
    WHERE (lead = '名詞' AND type = '名詞') 
      OR  ( lag = '名詞' AND type = '名詞') 
   ) foo2; 

  max |   word   |     ruby 
 -----+----------+-------------- 
    3 | 統計     | トウケイ 
    3 | 情報     | ジョウホウ 
    3 | コレクタ | コレクタ 
    9 | 活動     | カツドウ 
    9 | 状況     | ジョウキョウ 
 (5 rows)

上から順に各行時点でのnumの最大値を表示するmax列を生成することで、連続している名詞がmax列の値でグループ化されます。（個人的にはここの処理で一番悩みました）

ここまで持ってくればあと一息です。

異なる行の値を連結するために、array_agg関数とarray_to_string関数を使います。

array_agg関数は引数を配列に変換する集約関数で、array_to_string関数は引数の配列を文字列に結合する関数です。

よって、上記の結果を更に副問い合わせし、max列で集約すれば完成です。


 =>SELECT 
   array_to_string (array_agg (word), '') word, 
   array_to_string (array_agg (ruby), '') ruby FROM 
   ( 
    SELECT max(num) OVER (ORDER BY row_number),word,ruby FROM 
    ( 
     SELECT CASE 
              WHEN lag = '名詞' THEN 1 
              ELSE row_number 
            END num, 
            row_number,word,ruby 
       FROM 
      ( 
       SELECT row_number () OVER (), 
              lag (type) OVER (), 
              lead (type) OVER (), 
              word,type,basic,ruby FROM 
       ja_analyze('PostgreSQLの統計情報コレクタはサーバの活動状況に 
                   関する情報を収集し、報告するサブシステムです。') 
      ) foo1 
     WHERE (lead = '名詞' AND type = '名詞') 
       OR  ( lag = '名詞' AND type = '名詞') 
    ) foo2 
   ) foo3 GROUP BY max; 

        word       |            ruby 
 ------------------+---------------------------- 
  統計情報コレクタ | トウケイジョウホウコレクタ 
  活動状況         | カツドウジョウキョウ 
 (2 rows)

如何でしたでしょうか。

……SQLでやらなければforループとかで遥かにシンプルに実装できそうなことはわかってはおりますが、そこはPostgreSQL Advent Calendarの記事だからということで目をつむって頂ければと思います。

SQLに限った話ではないですが、結局、各種機能を「知っているか知らないか」だけで生産性が大幅に変わることになります。

SQLによる他の方法で同結果を得る方法を思いつく方は、是非ご教示ください。

Microsoft Office Word 索引作成支援ツールマニュアル

hahasoha — Sun, 14 Dec 2014 21:02:43 +0900

Microsoft Office Word 索引作成支援ツールの利用方法を説明します。

本ツールは、Microsoft Office Wordに実装されている自動索引登録機能で利用するためのファイル作成を支援します。

実際の索引作成には、正規のMicrosoft Office Wordが必要となることにご注意ください。

初回解析

初めて本ツールを利用する場合は、トップ画面の「索引候補ファイル作成」の「ファイルを選択」をクリックし、索引を作成したいWordファイルを選択して「初回解析」をクリックしてください。

※対応している拡張子はdocxのみです。

本マニュアルでは、サンプルファイルとして青空文庫の「羅生門」の本文を利用します。

解析が終了すると、索引登録候補一覧画面に遷移します。

大量に列挙された候補の中から、索引として登録すべき用語のみチェックをつけてください。

語句や読みが正確に解析されていない場合は手動で修正してください。

作業状態の保存と再開

「作業状態保存ファイルをダウンロード」をクリックすると、現在の作業状態が記録されたtsvファイルがダウンロードされます。

トップ画面の「索引候補ファイル再編集」から該当のファイルをアップロードすれば、編集作業を再開することができます。

索引登録ファイルをダウンロード

「索引登録ファイルをダウンロード」をクリックすると、Microsoft Office Word用の索引登録ファイルがダウンロードされます。

中身は以下のようなdocxファイルですが、通常このファイルを開く必要はありません。

索引登録と索引作成

本作業実施前に、索引作成対象のWordファイルのバックアップを取得するようにして下さい。

索引を作成したいWordファイルをMicrosoft Office Wordで開いてください。

メニューから「参考資料」→「索引の挿入」を選択してください。

「自動索引登録」をクリックし、本サイトからダウンロードした索引登録ファイルを選択してください。

本文中に索引項目が以下のように自動登録されます。

索引項目を非表示にしたい場合は、メニューから「ホーム」→「編集記号の表示／非表示」を選択して表示を切り替えてください。

索引を作成します。

索引を作成したい個所にカーソルを合わせた状態で再度メニューから「参考資料」→「索引の挿入」を選択してください。

任意のスタイルを設定後、「OK」を選択してください。

以下のように索引が作成されます。

説明は以上です。

テストデータに対するテストについて

hahasoha — Sat, 06 Dec 2014 00:00:00 +0900

これはPostgreSQL Advent Calendar 2014の12月6日分の記事です。

河原と申します。

僭越ながらPostgreSQL関連本をAmazon PODで出版させて頂いております。

---宣伝　ここから---

来年の1月16日(金)にPostgreSQLパフォーマンスチューニング実例セミナーを開催することになりました。

ご興味のある方は是非ご応募ください。

---宣伝　ここまで---

この記事以外に全く記事がないことからもお分かり頂けるように、記事の投稿の為にわざわざブログを解説したわけですが、大した記事は書けませんのであしからず…

早速、本題に入ります。

テストデータはなるべく本番環境のデータに近い内容であるべきなのですが、大量のリアルなデータを作成するには、それなりにテクニックが必要となります。

で、PostgreSQLでのテストデータ作成については既に去年のAdvent Calendarで笠原さんが書かれていますので未読の方はまずはそちらの記事をご覧ください。

リアルなデータを作成するには色々と問題があるわけですが、その一つとしてランダム性が挙げられます。

generate_series関数を使えば大量の連番データは容易に作成できますが、そこにランダム性を加えるとなると一工夫が必要となるわけです。

そして、ただランダムなだけであればrandom関数でも噛ませれば済む話なのですが、「ランダム性もあって整合性も保たれてなければならない」となると更に工夫が必要となります。

例えば先日、「シーケンス列とレコード作成日時列」の2列を持つテーブルのサンプルデータを作成する機会がありました。

簡略化しますが、以下のようなイメージです。


  seq |      testtime 
 -----+--------------------- 
    1 | 2014-12-04 00:00:01 
    2 | 2014-12-04 00:00:02 
    3 | 2014-12-04 00:00:03 
    4 | 2014-12-04 00:00:04 
    5 | 2014-12-04 00:00:05 
 　   :

で、testtimeにランダム性を持たせたいわけですが、現実のデータを模するのであれば、testtimeは、seqが小さいレコードより大きな値でなければなりません。

つまり、ランダムの間隔でレコードが作成されていくのを模したいわけです。

とりあえず、本当にランダム性が高いといえるか疑問はありますが、以下のようにサンプルデータを作成したとします。


 => CREATE TEMP TABLE tempdata AS 
    SELECT generate_series(1,1000000) seq, 
    current_timestamp + (generate_series(1,1000000)||'minutes')::interval 
    - (floor(random() * 60)||'seconds')::interval testtime ; 

  seq |           testtime 
 -----+------------------------------- 
    1 | 2014-12-04 00:25:42.593496+09 
    2 | 2014-12-04 00:26:27.593496+09 
    3 | 2014-12-04 00:27:22.593496+09 
    4 | 2014-12-04 00:28:13.593496+09 
    5 | 2014-12-04 00:29:09.593496+09 
 　   :

レコード単位に1分ずつ加算させつつ、60秒未満のランダムな秒数を減算します。

仮にseqが1のレコードが最小の減算値で、逆にseqが2のレコードが最大の減算値であったとしても、1のtesttimeが2を上回ることはない……筈なのですが、確認したくなるのが人情でしょう。

余談ですが、100点満点のテストの点数のサンプルデータを作成する場合、ceil(random()*100)とやりたくなりますが、これだと0点が出ないことになるので、ceil(random()*101)-1かfloor(random()*101)としないと求めたい結果が出ません。

ですが、公式ドキュメントでは、random関数の範囲は8.3まででは「0.0～1.0の範囲の乱数値」と、最新のドキュメントでは「0.0 <= x < 1.0の範囲の乱数値」と説明されています。
この説明が正しければ、random関数でジャスト0が出力される可能性があるということになります。
が、とりあえず以下のSQLで10億回ほど施行してみましたが0は出ませんでした。
 => WITH r AS 
    (SELECT ceil(random()) col1 FROM generate_series(1,1000000000)) 
    SELECT * FROM r WHERE col1 = 0 ; 

  col1 
 ------ 
 (0 rows) 
random関数はdouble precisionを返すようですが、公式ドキュメントの浮動小数点データ型の説明を簡単に読んだ限りでは、相当な桁数まで行くようですね…
本当に0が出るのか確認するためにはソースを読んだ方が早そうです。

本題に戻ります。

testtimeがseqと同様に昇順となっているか確認する方法としてまず思いつくのは、以下のように自己結合を用いる方法でしょうか。


 => SELECT * FROM tempdata t1,tempdata t2 
    WHERE (t1.seq + 1 = t2.seq) AND (t1.testtime > t2.testtime) ; 

  seq | testtime | seq | testtime 
 -----+----------+-----+---------- 
 (0 rows)

ただ、この方法ですと抜け番のない列がないと実施できません。

方法は色々とあると思いますが、個人的にはWindow関数をよく使っています。

row_numberを取得するために使われたことがある人も多いかと思いますが、普通の集約関数も大変便利に使えます。

例えば以下のように使えば、seqでソート後のテーブルに対して、上から順に各行時点での最大値を取得できます。


 => SELECT *,max(testtime) over(ORDER BY seq) FROM tempdata ; 

  seq |           testtime            |              max 
 -----+-------------------------------+------------------------------- 
    1 | 2014-12-04 23:19:49.001853+09 | 2014-12-04 23:19:49.001853+09 
    2 | 2014-12-04 23:20:48.001853+09 | 2014-12-04 23:20:48.001853+09 
    3 | 2014-12-04 23:21:49.001853+09 | 2014-12-04 23:21:49.001853+09 
    4 | 2014-12-04 23:22:32.001853+09 | 2014-12-04 23:22:32.001853+09 
    5 | 2014-12-04 23:23:55.001853+09 | 2014-12-04 23:23:55.001853+09 
 　   :

後は、testtimeとmaxの値が異なる行が存在しないかを確認するだけです。


 => WITH r AS (SELECT *,max(testtime) over(ORDER BY seq) FROM tempdata) 
    SELECT * FROM r WHERE testtime <> max ; 

  seq | testtime | max 
 -----+----------+----- 
 (0 rows)

因みに、今回のケースでは自己結合よりWindow関数を利用した方が約1.5倍ほど高速でした。

さて、ここで記事を終えても良いのですが、もう一つありがちな罠を紹介しておきます。

1回のSQLだけで整合性のあるデータを作らなければならないわけでは必ずしもありません。

データを作成後に、後から問題のあるデータを修正するというやり方もあるかと思います。

が、深く考えずにやってしまいますと、その修正により整合性が失われてしまうことがあります。


 => CREATE TEMP TABLE tempdata2 AS 
    SELECT generate_series(1,1000) seq, 
    current_timestamp + (generate_series(1,1000)||'days')::interval 
    - (floor(random() * 24)||'hours')::interval testtime ; 

 => WITH r AS (SELECT *,max(testtime) over(ORDER BY seq) FROM tempdata2) 
    SELECT * FROM r WHERE testtime <> max ; 

  seq | testtime | max 
 -----+----------+----- 
 (0 rows)

この時点では整合性が保たれているのですが、このクエリでは日付が相当未来になってしまいますので、interval型を利用して2年ほど日付を巻き戻しますと……


 => UPDATE tempdata2 SET testtime = testtime - interval '2 years'; 

 => WITH r AS (SELECT *,max(testtime) over(ORDER BY seq) FROM tempdata2) 
    SELECT * FROM r WHERE testtime <> max ; 

  seq |           testtime           |             max 
 -----+------------------------------+------------------------------ 
  451 | 2014-02-28 07:27:44.77576+09 | 2014-02-28 18:27:44.77576+09 
 (1 row)

ご覧のように、整合性に問題のあるデータが発生してしまいました。

日付を見れば原因はすぐにお分かり頂けるかと思いますが、うるう年の影響です。

interval型は当然うるう年に対応していますが、対応した結果、

2016年2月29日 00:00:00の2年前は2014年2月28日 00:00:00となり、

2016年2月28日 12:00:00の2年前は2014年2月28日 12:00:00となります。


 => SELECT '2016-2-29 00:00:00'::timestamp - interval '2 years' col1 , 
           '2016-2-28 12:00:00'::timestamp - interval '2 years' col2 ; 

 -[ RECORD 1 ]------------- 
 col1 | 2014-02-28 00:00:00 
 col2 | 2014-02-28 12:00:00

このように、前後関係が逆転してしまう可能性があるわけです。

「自分はこんなドジは踏まない」という方も多いかと思いますが、大量のテストデータを作成する場合は、僅かな意識漏れが結果として大きな違いを生む可能性があります。

テストデータを使ってテストをする前に、そのテストデータ自身をテストすることを強くお勧め致します。

12月7日は、nuko_yokohamaさんです。

（カンファレンスお疲れ様でした。私も行きたかったです……）