« スレッドダンプ解析ユーティリティ『侍』 | トップページ | 【メモ】ロックしないインデックス作成(PostgreSQL) »

2014.03.13

PHPpgadmin的なものを作るときの注意点(1)

何回使われるかわからないような、いろいろなレポーティング機能をたくさん実装するのではなく、DBの情報を一定程度ユーザーに解放して、(ある程度)自由にSQLを組んでもらうような環境をご用意することがある。
実際パッケージというかそういうツールは準備済みなので、必要に応じてお客さんに提供させていただく。

いくつかDBのバージョン(Oracle/Informix/PostgreSQL)のものを用意しているが、PostgreSQL版について開発したときにちょっとやらかしちゃった事例。

システムとしては比較的単純で、DB内のテーブル一覧をシステムカタログから取得し、UI上で選択可能(公開するテーブルは別途セキュリティをかけている)。
テーブルを選択したらカラムを選択し、JOIN条件、WHERE条件などをUIで選択していくと、SQLを自動生成してクエリ可能な状態ができあがる、というようなツールである。
まあ一種のクエリビルダ、クエリウィザード的なものである。

今回のメモはこのツールそのものじゃなくて、PostgreSQLにおけるpg_attributeというテーブル内の値について。
pg_attributeは各テーブルの列情報が格納されているテーブル。上記のように動的に存在するカラムを選択できるようにする以上、このテーブルを参照するのが答えとなる。
この際に発生した問題というのは、列情報を正しく取得できないケースがある、ということだった。

最初は発生ケースが特定できず苦労したのだが、ALTER TABLEを実施した場合のみに発生することが突き止めることができ、そこから答えが出てきた。

クエリビルダとしては、列名と列の型が表示できればそれでよかったので、表名(pg_classと結合)して、出力を行う。またこのテーブル内に"attnum"という列があり、これは列番号なのだがOIDがある場合は負の数が入る。
OIDは外したかったので、取得条件は下記のようなSQLになっていた。

SELECT relname, attname, attname
FROM pg_attribute INNER JOIN pg_class ON attrelid = pg_class.oid
WHERE attnum > 0 ;
基本的にはこれでOKだったのだが、先ほども言ったようにALTER TABLEを実行すると変な挙動になる。
これはpg_attributeの仕様に依存していて、ALTERで列名などが変更された場合、変更される前のレコードも実は削除されずに残っているのであった。
MVCCの挙動とは別に、このテーブルの仕様である。

その対象となるカラムが"attisdropped"。
「この列は既に削除されていて有効ではありません。削除された列は物理的にはまだテーブル上に存在していますが、パーサによって無視されるためSQLでアクセスすることができません」
とあるように、SQLで該当表にアクセスする場合には出てこないのだが、pg_attributeにアクセスすると見えてしまうのだ。

このため必要な列情報を取得する正しいSQLは下記のとおりとなる。

SELECT relname, attname, attname
FROM pg_attribute INNER JOIN pg_class ON attrelid = pg_class.oid
WHERE attnum > 0
AND attisdropped IS FALSE;

これで奇妙な列情報は出てこなくなった。
削除された列情報を選んでSQLを組んでも、DML上は存在しない扱いの列のため、正しくSQLが動作しない。

まあ普通に利用している限りお目にかかるような事例ではないだろうが、こんなこともあるもんですな。

|

« スレッドダンプ解析ユーティリティ『侍』 | トップページ | 【メモ】ロックしないインデックス作成(PostgreSQL) »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/59274184

この記事へのトラックバック一覧です: PHPpgadmin的なものを作るときの注意点(1):

« スレッドダンプ解析ユーティリティ『侍』 | トップページ | 【メモ】ロックしないインデックス作成(PostgreSQL) »