PostgreSQL : してログ

12

文字列フィールドの改行コードを取り除くSQLです。trim 関数を使いますがデフォルトだと空白文字だけなので、改行コードを指定してあげます。

update accesslog set useragent=trim(both E'\r\n' from usetagent);

最新の PostgreSQL 9.4 を試してみようと、Windows Server 2003 にインストールしてみたところ、“Problem running post install step...the Database Cluster Initialisation failed.”というエラーで正しく完了しない問題が発生しました。 試しに、8.4 を含めここにあるいくつかのバージョンを試しましたが、いずれもダメでした。 でも不思議なことに、少し前にダウンロードしてあった 9.3.2 は何もしなくてもインストールできます。 最終的には、ここを見てインストールに成功しましたので、その手順を書き留めておきます。

PostgreSQL のインストール
  • インストールに失敗していたら、いったんアンインストールしておきます
  • インストールディレクトリを作成します(一回失敗していれば、C:\Program Files\PostgreSQL が残っているのでそれでも良い)
  • インストールディレクトリに Administrators 権限を持ったユーザーにフルコントロール権限を与えます(適当なユーザーが無ければ、postgres などユーザーを作成し Administrators グループに追加します)
  • インストーラを右クリックして、「別ユーザーとして実行」にて起動します
  • 権限を与えたユーザーを指定してインストーラを実行します
  • これで正常にインストールできるはずです
PostGIS のインストール
  • インストール先で示されるパスが、バックスラッシュで区切られているので、すべて¥マークに直します
  • これで正常にインストールされるはずです

テーブルの定義(データ型、フィールド長、カラムの定義順、デフォルト値、NOT NULLなど)を一括して取得する SQL 文です。 テーブル名は、relname = 'table_name' の部分で指定します。 前の記事の方法より、こちらのほうが簡単で他の情報も取得できます。

select
	*
from
	information_schema.columns
where
	table_catalog = current_database()
		and
	table_name = 'table_name'
order by
	ordinal_position
;

テーブルを構成する、各フィールドのデータ型を取得するSQL文は、下記のようなります。 テーブル名は、relname = 'table_name' の部分で指定します。 プログラム内でこれらを取得することで、入力値の桁数や、データ型のチェックに役立ちます。

select
	at.attname,
	format_type(at.atttypid, at.atttypmod)
from
	pg_attribute as at
		left join pg_type as tp on (at.atttypid = tp.oid)
where
	at.attnum > 0 and
	at.attrelid = (select relfilenode from pg_class where relname = 'table_name')
order by
	at.attnum
;

PostgreSQL の pg_dump や pg_restore といった CLI 管理ツールは、PowerShell ではうまく動かいないようです。 Windows のコマンド・プロンプトに代わるコマンド・ライン・インターフェイスの PowerShell ですが、コマンド・プロンプトの代わりにはならないということが分かりました。

無理に使おうとした場合、下記のような警告やエラーが出て、作成されたバックアップファイルも壊れています。 Windows Server 2012 では、PowerShell が標準という扱いのようなのですが、似たような見た目に騙されると痛い目に遭います。 マイクロソフトに言いたいのは、もっと保守的にアップデートして欲しい、ということ。 特に、サーバー系 OS はね。

pg_dump を PowerShell で使った場合に出る警告
pg_dump: [カスタムアーカイバ] 警告: ftellで想定位置との不整合がありました -- ftellが使用されました
PowerShell + pg_dump で作ったバックアップファイルを pg_restore でリストアした場合のエラー
[アーカイバ]ファイルヘッダにマジック番号がありませんでした。

まさか、PDO のバグだったとは。

PostgreSQL に格納されたバイナリデータ(bytea)を PDO で取得しようとすると、文字列としてしか取得できないバグがあり、5.2.6 で報告されたのに、5.3.28 でも治っていないようです。 おかげで、急ぎのプロジェクトなのに、かなりの時間をロスしてしまいました。 通常、バイナリデータの取得は、以下のように書きますが、このバグにより「xffd8ffdb00430005030404040....」のような文字列が返ってきます。

$sth = $pgsql->prepare("select id,mime,img from t_image limit 1");
$sth->execute();
$sth->bindColumn('id', $id, PDO::PARAM_INT);
$sth->bindColumn('mime', $mime, PDO::PARAM_STR);
$sth->bindColumn('img', $img, PDO::PARAM_LOB);
if ($sth->fetch(PDO::FETCH_BOUND)) {
	header("Content-Type: {$mime}");
	fpassthru($img);
	exit;
}

PDO を使う限り解決策がないため、ネイティブ系の関数を使うか、文字列をバイナリ列に直すしか無いと思われます。 この文字列をバイナリ列に直すコードは、下記のようなものになります。

$sth = $pgsql->prepare("select id,mime,img from t_image limit 1");
$sth->execute();
$sth->bindColumn('id', $id, PDO::PARAM_INT);
$sth->bindColumn('mime', $mime, PDO::PARAM_STR);
$sth->bindColumn('img', $img, PDO::PARAM_LOB);
if ($sth->fetch(PDO::FETCH_BOUND)) {
	header("Content-Type: {$mime}");
	fgets($img,2);
	$data = fgets($img);
	echo pack('H*',$data);;
	exit;
}

文字列で返る以上、扱うデータ量は倍になりますし、pack という重めの関数を用いなければならないため、速度や効率面ではあまりよろしくありません。 ネイティブ系の関数で書ける場合は、そちらのほうがお勧めです。 なお、fgets($img,2); の部分は先頭に入る「x」の文字を捨てるためで、第2引数が「2」なのは、この関数が第2引数マイナス1バイトを読み込むという仕様のためです。 これに気づくのに、2時間ほど要してしまいました(^_^;)

PostgreSQL に接続中のユーザー名を取得する方法です。セッションユーザーは、データベースセッションを開始したユーザー、カレントユーザーは、現在の実行ユーザーとなります。スーパーユーザーでなければ、ユーザー変更できないので、通常は同じユーザーが入ります。

セッションユーザ名
select session_user;
現在の実行コンテキストのユーザ名
select current_user;

他にも、スキーマ名、IPアドレスなど様々な情報が取れますが、それらについてはこちらをご覧ください。ストアドプロシージャなどで活用すると、ログ取得などなど、色々と便利そうです。

以前のバージョンでは、テンプレートを元に新しい空間データベースを作成しましたが、PostgreSQL 9.1 の CREATE EXTENSION で機能拡張としてインストールする形になりました。このため、PostGIS 2.1 では従前のテンプレートが無く、代わりにデータベースを作成した後、下記のコマンドにより空間データベースを構築します。

create extension postgis;

他にも、経路探索を行う pgrouting、トポロジー型をサポートする postgis_topology などがあるようです。また、日本では使えませんが、ジオコーディング用の機能拡張も用意されています。これらも同様に、CREATE EXTENSION で必要に応じてインストールできます。

PostgreSQLのシステムテーブルから、存在するテーブルやビューの一覧を得ることができます。 下記、SQL文の結果で、スキーマ名(nspname)、テーブルやビューの名前など(relname)、オブジェクトの種類(relkind)が返ります。 relkind は、テーブル(r)、ビュー(v)になります。

select
  c.oid,
  n.nspname,
  c.relname,
  c.relkind
from
  pg_catalog.pg_class as c
    inner join pg_catalog.pg_namespace as n on (c.relnamespace=n.oid)

WGS84 はよく使うので 4326 とすぐに出てくるけど、他のはなかなか覚えづらいので、まとめておきます。

最近では、Google Maps で用いられている投影法(EPSG:900913)を使うことも多く PostGIS の spatial_ref_sys にも SRID=900913 として定義がありました。前のバージョンだと自前でパラメータ入れなければならなかったので、記事に残しおこうと思ったらすでに入ってたって言う感じです。字面が Google を表しているので、この覚え方は簡単です。

Google
900913
主要SRID一覧
  • JGD2000(GRS80楕円体)
    緯度経度
    4612 : 緯度経度
    UTM座標系
    3097 : 51N
    3098 : 52N
    3099 : 53N
    3100 : 54N
    3101 : 55N
    平面直角座標
    2443 : 1系 長崎県、鹿児島県の一部
    2444 : 2系 福岡県、佐賀県、熊本県、鹿児島県の一部
    2445 : 3系 山口県、島根県、広島県
    2446 : 4系 香川県、愛媛県、徳島県、高知県
    2447 : 5系 兵庫県、鳥取県、岡山県
    2448 : 6系 京都府、大阪府、福井県、滋賀県、三重県、奈良県、和歌山県
    2449 : 7系 石川県、富山県、岐阜県、愛知県
    2450 : 8系 新潟県、長野県、山梨県、静岡県
    2451 : 9系 東京都の一部、福島県、栃木県、茨城県、埼玉県、千葉県、群馬県、神奈川県
    2452 : 10系 青森県、秋田県、山形県、岩手県、宮城県
    2453 : 11系 北海道の一部
    2454 : 12系 北海道の一部
    2455 : 13系 北海道の一部
    2456 : 14系 東京都の一部(小笠原)
    2457 : 15系 沖縄県の一部
    2458 : 16系 沖縄県の一部
    2459 : 17系 沖縄県の一部
    2460 : 18系 東京都の一部(沖ノ鳥島)
    2461 : 19系 東京都の一部(南鳥島)
  • WGS84系(WGS84楕円体)
    緯度経度
    4326 : 緯度経度
    UTM座標系
    32651 : 51N
    32652 : 52N
    32653 : 53N
    32654 : 54N
    32655 : 55N
    32656 : 56N
  • EPSG:900913(半径6378137m真球)※Google Maps / 電子国土
    球体メルカトル
    900913
  • 日本測地系(ベッセル楕円体)
    緯度経度
    4301 : 緯度経度
    UTM座標系
    102151 : 51N
    102152 : 52N
    102153 : 53N
    102154 : 54N
    102155 : 55N
    102156 : 56N
    平面直角座標系
    30161 : 1系 長崎県、鹿児島県の一部
    30162 : 2系 福岡県、佐賀県、熊本県、鹿児島県の一部
    30163 : 3系 山口県、島根県、広島県
    30164 : 4系 香川県、愛媛県、徳島県、高知県
    30165 : 5系 兵庫県、鳥取県、岡山県
    30166 : 6系 京都府、大阪府、福井県、滋賀県、三重県、奈良県、和歌山県
    30167 : 7系 石川県、富山県、岐阜県、愛知県
    30168 : 8系 新潟県、長野県、山梨県、静岡県
    30169 : 9系 東京都の一部、福島県、栃木県、茨城県、埼玉県、千葉県、群馬県、神奈川県
    30170 : 10系 青森県、秋田県、山形県、岩手県、宮城県
    30171 : 11系 北海道の一部
    30172 : 12系 北海道の一部
    30173 : 13系 北海道の一部
    30174 : 14系 東京都の一部(小笠原)
    30175 : 15系 沖縄県の一部
    30176 : 16系 沖縄県の一部
    30177 : 17系 沖縄県の一部
    30178 : 18系 東京都の一部(沖ノ鳥島)
    30179 : 19系 東京都の一部(南鳥島)
12