象と戯れ

|

2011-10-14

PostgreSQLで時差を計算する

18:49 | PostgreSQLで時差を計算する - 象と戯れ を含むブックマーク はてなブックマーク - PostgreSQLで時差を計算する - 象と戯れ

最近は大陸間で電話することが多くて「今あっち何時?」的な脳みそが足りない自分は苦労するわけです。タイムゾーンで時差を教えてくれる手っ取り早い方法がPostgreSQLに内蔵されたOlson Databaseをつつくこと。

-- PDT (Pacific Daylight Time)で何時だっけ?
SELECT timezone('PDT', '2011-10-17 15:40:00');
      timezone       
---------------------
 2011-10-16 23:40:00
(1 row)

-- その9時間後は?
SELECT timezone('PDT', '2011-10-17 15:40:00 JST'::timestamptz + interval '9 hours');
      timezone       
---------------------
 2011-10-17 08:40:00
(1 row)

タイムゾーンの指定の仕方はpg_timezone_names/pg_timezone_abbrevsから。

TABLE pg_timezone_names;
TABLE pg_timezone_abbrevs;

Olsonデータはだいぶ昔から採用しているみたいですが、実装はまるで最近のFDWですね。

余談ですが、世界で一番早く朝を迎えるのはどこでしょう。

SELECT * FROM pg_timezone_names ORDER BY utc_offset DESC LIMIT 3;
        name        | abbrev | utc_offset | is_dst 
--------------------+--------+------------+--------
 Etc/GMT-14         | GMT-14 | 14:00:00   | f
 Pacific/Kiritimati | LINT   | 14:00:00   | f
 Pacific/Chatham    | CHADT  | 13:45:00   | t
(3 rows)

KiritimatiGMT+14ってフライングしすぎ。

というわけで、時間の計算は下手なアプリをダウンロードするよりpsqlの方がよっぽど早い。ぜひお試しあれ。

pgsqlpgsql2011/10/14 19:22AT TIME ZONE 演算子も使ってあげてね!

umitanukiumitanuki2011/10/14 21:44そうそう、それもあった!

2011-06-15PostgreSQLをhackしてみたいそこのアナタのための、たった10のステップ。

PostgreSQLをhackしてみたいけどどうしたらいいかわからないというそこのアナタのための、たった10のステップ。

21:49 | PostgreSQLをhackしてみたいけどどうしたらいいかわからないというそこのアナタのための、たった10のステップ。 - 象と戯れ を含むブックマーク はてなブックマーク - PostgreSQLをhackしてみたいけどどうしたらいいかわからないというそこのアナタのための、たった10のステップ。 - 象と戯れ

纏めるべき時期といえばそうだけど別にそんなんじゃないんだからね!9.2向けのCommitfest初回が始まるからだからね!


1.hackersを購読する

PGのメーリングリストはテーマによって多岐に渡りますが、開発が行われているのはpgsql-hackersです。なんてステキな名前なんでしょう。コードを書きたくてウズウズしてきますね。

登録は下記のページから。まずはどんなことが議論されているかじっくり眺めるといいと思います。

http://archives.postgresql.org/pgsql-hackers/

PGの開発は完全にMLベースで進められているため、ここが全てといっても過言ではありません。どこかの企業内で勝手に議論が進んでパッチが入ってるといったことはまずありません。フェアですね。

ちなみにその他のおすすめMLとしては、

  • pgsql-general: 一般ユーザ向けの内容。SQLの書き方がわかんないよ!とか
  • pgsql-performance: パフォーマンス関連いろいろ。EC2で動かすならどんな設定?とか。クエリの高速化で投げるときはぜひTuning Your PostgreSQL Server - PostgreSQL wikiとかSlow Query Questions - PostgreSQL wikiとかを読んでから。EXPLAINの結果はNew explain | explain.depesz.comに貼っとくと後でリンクできます
  • pgsql-committers: gitメインリポジトリのpush(コミット)ログが流れてます。議論の末どんな機能追加・変更があったのかが意外とわかりやすい
  • pgsql-bugs: バグレポートが流れてます。ここのレポートから明らかになったことがhackersで議論されて、back patchがcommittersに流れたり
  • pgsql-advocacy: プレスのこととか。カンファレンスのお知らせとか知るにはここです


2.ソースコードを取得する

メインリポジトリはここから閲覧することができます。もちろん

git clone git://git.postgresql.org/git/postgresql.git postgresql

てな感じでとってくることができます。上記ページをちょっと眺めると気づくかもしれませんが、実はgithubにもリポジトリがあります。

https://github.com/postgres/postgres

ここはメインのミラーですが、github内でフォークしたりするにはここをoriginにするとよいと思います。ちなみにgithubのフリー版ではリポジトリの容量制限があるのですが、postgresをフォークするとそれだけで一杯になってしまうらしいです。ご利用の際は是非有料版のご検討を。


3.デバッグビルドして、gdbしてみる

cloneしてきたらビルドです。開発するなら当然デバッグビルドだよね? 自分のUbuntu11では下記のconfigureです。

#!/bin/bash

VERSION=`pwd | perl -ne 's/.+postgresql-([^\/]+).*/$1/;print'`
./configure CC="ccache gcc" CFLAGS=-pthread --prefix=$HOME/local/pgsql-$VERSION --enable-debug --enable-cassert
  • --enable-debug --enable-cassert: 文字通りデバッグオプション"-g"でコンパイル、アサート有効。これでgdbできます。
  • CFLAGS=-pthread: CentOSでやってたときは-pthreadいらなかったのですが、Ubuntuになってからなんかこれがないとうまくgdbできなかったのでずっとつけてます。CLFAGS= と空文字設定をおすすめします。というのも、デフォルトでは-O2なのでgdbがあんまりよろしくありません。
  • CC="ccache gcc": 最近覚えたのですが、ccacheインストールしていると再コンパイルが劇的に速くなります。
  • --prefix=$HOME/local/pgsql-$VERSION: masterブランチを-headに、リリースブランチを-8.4とか-9.0とかにして外部モジュールを開発するために切り替えたりしてます。ホームディレクトリ以下にローカルインストールして自分ユーザで動かすのがほとんどです。

configure中にいくつかこけるかもしれません。ありがちなのは、readline、zlibあたりの開発パッケージ不足。あと、git cloneしてきたときはbison/flexの最新版が必要です。パッケージインストールがもし古いと言われてしまったら、頑張って最新ソースからインストールしておきましょう。

インストールディレクトリのPATHを設定して、あとは、

$ make && make install
$ cd $HOME/local/pgsql-$VERSION
$ initdb -D data
$ postgres -D data

とかなんとかでバックエンドが立ち上がるはずです。お好きなようにcreatedb, psqlして下さい。

え?gdbしたことないって?モグリと言われないうちにこっそり覚えておいて下さい。巷のgdbのページをググっても gdb a.out みたいにgdbからプロセスを起動する方法ばかりが見つかると思いますので、バックエンドにアタッチする方法を一つ。

別シェルからpsqlした状態で、

$ (sudo) gdb -p `pgrep -n postgres`

以上。-pオプションで最新のバックエンドプロセスIDにアタッチします。個人で開発している限り、これでまず目的のバックエンドにアタッチできるはずです。

あとは普通にブレークポイント設定してc(ontinue)するだけ。psqlで何かクエリを発行すればgdbがブレークするはずです。

最初の最初のおすすめブレークポイントはexec_simple_queryですかねえ・・・ブレークしたら、btしてソースと照らし合わせると幸せになれます。


4.回帰テストを走らせる

PGの評判の一つに「コードの品質が高い」が挙げられると思います。そのための要因は様々あると思いますが、何は無くとも回帰テスト。これが通らないパッチは話にならないばかりか、機能追加であればここへの追記も必須です。

ソースコードツリーのトップから、

$ make check

で勝手にテストが走ると思います。ちゃんと走ったら何が起きたか確認する意味でも、src/test/regress/expected/*.outなんかを眺めてみるといいと思います。

ちなみにmake checkは回帰テストのターゲットですが、その他のターゲットとして、

  • installcheck: インストール済みのPGに対してテスト
  • world: contribやドキュメントを含めて全てビルド
  • docs: ドキュメント(HTML)のビルド。jadeなどのSGMLプロセッサが必要
  • clean: 言わずとしれたお掃除
  • distclean: allしたあとにこのターゲットで配布用ソースツリーになります。gram.yの再ビルド不要状態
  • maintainer-clean: 開発者用のclean。git clone初期状態に戻りますので、gram.yなども再ビルドとなります

なんかがあります。


5.Developer FAQを読む

さてさていよいよパッチを書くぞ、と意気込むところ申し訳ないのですが、ローマにおいてはローマ人に従うのが無駄のないクールな人生です。Developerがすべきことがまとまっているので是非ここをご一読下さい。

http://wiki.postgresql.org/wiki/Developer_FAQ

SQL標準へのリンクまであって読むだけでもデータベース知識が深まる一ページ。ソースに含まれる開発ツールなんかも説明があっていいですね。個人的にはvim使うのでmake_ctagsは必須です。関数を初期カタログ(bki)に追加するならunused_oidとかも要チェック。

コーディングルールや進め方など、この軟弱なはてなブログを読むよりよっぽど有用な情報満載です。ereport()って何なの?CommandCounterIncrement()って?何でpalloc()/pfree()使うの?


6.TODOリストを読む

上記FAQにもありますが、何から手をつけていいかわからないけどなんかしたい、というときはこのTODOがおすすめ。

Todo - PostgreSQL wiki

25年も経つプロジェクトなのにこんなにあるのかよ、と突っ込みたくなる量です。むしろ近年爆発的に増加しています。それぞれのアイテムには元になっているhackersあたりのURLにリンクがあります。管理・データ型・関数・国際化・ビュー/ルール・SQLコマンド・サーバサイドインターフェイス・クライアントサイドインターフェイス・ソート・VACUUM・プランナ・エグゼキュータ・トリガ・インデックス、、、ありとあらゆる面でやることいっぱいおなかいっぱい。改めて見るとExotic Featureとかあるんですね。クエリ進捗プログレスバーとか。。。

このページ、きっちり読んでおきたいのが一番最後。「Features We Do Not Want」と題して、「この機能他のSQLにあるからPGでもやってよ」的なお問い合わせに対する回答。

  • スレッドモデルにしたい → やりません。プロセスモデルで十分です
  • Oracle式の「オプティマイザヒント」 → やりません。様々な観点から、コストベースであるべきです
  • 埋め込みサーバ → やりません。そういうのはそういうのが得意な連中に任せましょう
  • 関数のソース隠匿 → やりません。いずれにせよ見えるものです
  • GROUP BYに指定しない列の無視 → やりません。9.1からは関数従属性に従った対応が完了していますが、MySQLのように全てを受け入れることはありません。


7.パッチを書く

やっとパッチを書くところまで来ました。ここまでで多くの情報をインプットしているはずですから、後はひたすらソースを読んでgdbを走らせ、動きを捉えてコードを追加しましょう。


8.Commitfestを理解する

パッチができたら、説明を添えてhackersに送ります。実際にアナタのコードをメインリポジトリにコミットできるのは十数名のコミッタなので、彼らが納得しなければなりません。それぞれのコミッタには好きで得意な領域もありますし、何より大量のパッチが日々送られてくるため、アナタのパッチに返信をくれないかもしれません。ところが膨大な数のデベロッパが参加しているこのMLには思いがけずアナタのパッチに興味を持ってくれる人もいるでしょう。そういう人の意見を最大限に取り入れ、少しずつ改良していくといいと思います。

そんなことを日々続けていると開発全体がグダグタになってしまうので、8.3か8.4の開発の頃からCommitfestというイベントが定期的に開かれるようになりました。一定期間でパッチのレビューを行い短いサイクルで開発者にフィードバックを送るための機会です。

Commitfests

上記のリンクからCommitfestに行くと、現在時点で9.2に向けたスケジュールがひかれています。各回において自分のパッチを登録し、フィードバックがあったら速やかに対応・新しいパッチ作成を行う必要があります。熟成されたパッチはそのままコミットされますし、フィードバックを得られてもっと時間が必要と判断されたものはReturned With Feedbackとマークされておしまいです。次回以降のCommitfestを目指して開発を続けることになります。

このCommitfestで重要なのは、コードレビューです。パッチ作者は少なくとも自分のパッチと同じ数だけ、他人のパッチレビューを行うことがこのイベントのルールです。コミッタの数が絶対的に少ないわけですから、ピアレビューを重ねることによって少しでも彼らが行う作業を軽減することができます。また今まで自分が触ったことのない領域のパッチを読むと、それだけでも知識が深まるというものです。

他人のレビューをできるほど自分に力がないとか腰抜けたことを言っているそこのアナタ、レビューは誰にでもできるので是非レビューだけでの参加もトライして下さい。どのようなことを行えばよいかということが下記のガイドラインを読めばわかります(なんと日本語に翻訳済み!)

Reviewing a Patch/ja - PostgreSQL wiki

Reviewing a Patch - PostgreSQL wiki(英語)

基本的にはパッチを当てて、ビルドして、言われている機能を満たしているか、想定外のエラーがでないか、ということをチェックして定型文を埋めていくだけでよいのです。

ちなみにCommitfestにパッチを登録するためにはCommunity Loginが必要です。初めての方はこちらからどうぞ。なんと気軽に登録した後はCommitfestへの登録だけでなく、Wikiの編集、さらにはgit.postgresql.orgでの自分のリポジトリ作成までできちゃいます。とりあえず登録しとけ。


9.ドキュメントを書く

さあここまでくると大詰め。よいプログラマはよい文書を書くものです。PGの品質の高さの一つには充実したドキュメントがあります。すごい機能を実装したコードはうまく動いているけれどもなかなかパッチレビューが入らない、コミッタに拾ってもらえない、ということが希にあるのですが、そういうのはだいたいドキュメントがおろそかなことが多いのです。無駄な時間をとらないように、面倒でもきっちり書いておきましょう。

先にも説明しましたが、make docsでドキュメントのビルドが走ります。ビルドにはjadeなどのXML/SGMLプロセッサが必要です。ソースはdoc/src/sgml以下にありますので、似たようなところをコピーしていじるのが得策ではないかと思います。


10.パッチを投稿する

全て揃いましたか?パッチができたらpgsql-hackersに添付ファイルで送信します。私用のサーバにアップロードしたからダウンロードしてよ、とお願いしても聞き入れてもらえません。必ずメールに添付となります。パッチの形式として以前(CVS運用時)はcontext-diff形式で、ということになっていましたが、git移行と前後してほとんどのパッチがunified-dff形式になっているようです。git-format-patchで送られている例もたまにみますが、たいていの人はgit diff origin/masterの出力を保存して送っています。

最終段階では何度もパッチそのものを見直し、余分なスペースが入ったり逆に無駄に空行を削除してしまっていないかどうかといった点についてもよく確認してください。神は細部に宿るものです。



これでアナタも明日から立派なPG開発者! よい結果を楽しみにしています。

2011-05-13

writable CTE in 9.1

23:10 | writable CTE in 9.1 - 象と戯れ を含むブックマーク はてなブックマーク - writable CTE in 9.1 - 象と戯れ

betaとなって機能もFixした9.1ですが、いつものようにLet'sにはすばらしい新機能紹介ページが用意されています。

PostgreSQL 9.1 の新機能 | Let's Postgres

本日時点で下記のように紹介されているwritable CTEですが、

WITH 句を使った INSERT, UPDATE, DELETE

WITH (...) INSERT/UPDATE/DELETE ができるようになりました。WITH RECURSIVE も使えるので、再帰クエリの結果を元にテーブルを更新するようなケースで便利かもしれません。

というのは最後のCommitFestの途中までで、最終的にはWITH()の中にもINSERT/UPDATE/DELETEが書けるようになりました。Marko++

この機能自体は(似たようなものがSQL Serverとかにはあるものの)、標準でもまだ規定されていないレベルで、まさに前衛的データベースを体現しております。

何がうれしいのか、その使い方はユーザそれぞれですが、一つの参考例として、MySQLのUPSERTを模倣した例が挙がっています。

Upserting via Writeable CTE - zillablog

簡単にすると、こんな感じ。

TABLE target;
 id | val 
----+-----
  1 | Tom
(1 row)

TABLE source;
 id | val 
----+-----
  1 | Mom
  2 | Bob
(2 rows)

WITH upsert AS (UPDATE target SET val = source.val FROM source WHERE target.id = source.id RETURNING source.id)
INSERT INTO target
SELECT id, val FROM source WHERE id NOT IN (SELECT id FROM upsert);
INSERT 0 1

TABLE target;
 id | val 
----+-----
  1 | Mom
  2 | Bob
(2 rows)

targetテーブルをUPDATEして、FROM句で結合されなかったidだけINSERT ... SELECTするという。MERGEコマンドが入るまではこんな感じで悩みを解決してくれそう。

Enjoy!

twitter_fdw 1.0.0 released

22:56 | twitter_fdw 1.0.0 released - 象と戯れ を含むブックマーク はてなブックマーク - twitter_fdw 1.0.0 released - 象と戯れ

9.1がbetaに入り、FDWインターフェイスが固まったことで、先日実験的に作成したtwitter_fdwをリリースしました。

twitter_fdw: a foreign data wrapper to Twitter Search / PostgreSQL Extension Network

PGXNのページがカッコイイ!のでイカした感じがしますね。現在のところPublic Search APIのみを提供しますが、CREATE USER MAPPINGでOAuthを使ったユーザ系APIも叩けるようになるといいと思っています。それは2.0系としてのお楽しみ。

ついでにpgxnclientを試しました。

GitHub - dvarrazzo/pgxnclient: A command line client for the PostgreSQL Extension Network

$ sudo apt-get install python-setuptools
$ sudo easy_install pgxnclient
$ pgxn install --nosudo tinyint

最初の二つはUbuntuでeasy_install入れてpgxnclient導入するためのものですので、実質一行。make installにsudoを使わない場合は--nosudoオプションでOK。cpanmよろしくモジュール名をコマンドラインで叩くだけでダウンロード&インストール。PGXN開設から1ヶ月弱だと思いますが、このスピードですばらしいクライアントを実装してeasy_installサイトにまで登録してるDanielは結構イカしてると思う。

試してないですが、9.1をインストールしてあれば

$ pgxn install --nosudo twitter_fdw

でさくっと入るはずです。あとは

$ psql mydb
mydb=# CREATE EXTENSION twitter_fdw;
mydb=# SELECT from_user, text, created_at FROM twitter WHERE q = '@planetpostgres';

などとすればステキな感じです。

Enjoy!

2011-05-12

Functional Relational Database Management System

00:13 | Functional Relational Database Management System - 象と戯れ を含むブックマーク はてなブックマーク - Functional Relational Database Management System - 象と戯れ

関数型関係データベース管理システム、という造語を思いつきました。

プログラミング言語の世界では、「手続き型」があって「オブジェクト指向」があって、「関数型」があるわけです。何をもって関数型言語かというと、「関数(手続き)が第一級(First-Class)であること」と定義されるわけです。すなわち、関数をその他のオブジェクトと同様に変数に代入したりと扱うことができるということ。この定義にしたがえば、JavaScriptはもちろんPython/Perl/Rubyあたりも関数型と呼べると思います。そのへんと区別する意味で、Haskellなんかは「純粋関数型言語」と呼ばれたりしますね。

PostgreSQLはしばしば「オブジェクト関係データベース」と自称しています。どこをどう指してそのように呼ぶのか自分もはっきり理解していないですが、確かにPostgreSQLのデータ型拡張インフラとかはハンパじゃないです。データ型(クラス)を定義してその型に紐づく演算子やインデックスを定義できるという意味で、オブジェクトデータベースなのかもしれません。

ここまでくれば賢い方は察しがつきますね。関数をデータベースに定義して保存できるようにすれば、是即ち「関数型関係データベース」也。イメージ的にはこんなかんじ:

CREATE TABLE ftbl(
  id serial,
  fcol function( int, int ): int
);

INSERT INTO ftbl (fcol) VALUES ('return $1 + $2'), ('return $1 - $2');
SELECT
  fcol -> (id, 3)
FROM
  ftbl
WHERE
  fcol -> (id, id) > 0

みたいな。関数型を名乗るからにはカリー化もできないと。

SELECT
  fcol2 -> (3) -> (5)
FROM
  ftbl
;

そんなニーズどこにあるのかって思いますね。こういうものが存在しなかったからニーズがないのか、ニーズがないから存在しないのか。いやいや、そういえばこないだかかわった案件ではそんなDBになってましたね。もしかしたらFRDBMSとして論文書けるかも?「弊社は関数型関係データベースを開発しております」とか言ったらなんか22世紀の会社みたいだ。

「それは1960年代にだな、」という識者のご意見があればぜひコメントください。

2011-04-29

9.1betaも出るが

22:05 | 9.1betaも出るが - 象と戯れ を含むブックマーク はてなブックマーク - 9.1betaも出るが - 象と戯れ

イースターも終わって日本がGWだと何か気が抜けてる感が(個人的には)ありますが、9.1betaのタグも打たれたようで何よりです。

Robert Haasが自身のブログでサービスチームへ移動したことを報告しています。

Robert Haas: A Change of Role

もともと何チームだったのかよくわかりませんが、文章を読む限り「問題解決のためにコードを書くのだから、もっと問題に接している必要があることに気づいた」と述べています。意外と若さあふれますね。

このコメント欄に出てきたのが今やRobertに絶大な信頼を置く(ように見える)Tom Lane。曰く、

Yeah, I get similar results by following the PG user mailing lists. More developers ought to pay attention to lists like pgsql-novice

「デベロッパはもっとpgsql-noviceあたりのMLを読むべき」だそうです。確かに奴のgeneral/novice/bugsへの出没っぷりは鬼そのもの。信念がなければできない神業ですね。

|