psqlを便利に使うTipsいくつか

標準添付のGUIツールのpgAdminIIIが十分使いやすいので、psqlからCUIで利用することは少ないかもしれない。
しかし、やはりCUIでいろいろできることはメリットが多い。
そこでいくつかのTips。

続きを読む "psqlを便利に使うTipsいくつか"

特定の名称を含むカラム・テーブルを調べる

画面上のある項目名がどのテーブルに紐づいているかを簡単に調べたいときにカラム名をもとに検索するＳＱＬのメモです。

SELECT relname, attname
FROM pg_attribute
INNER JOIN pg_class ON pg_class.oid = pg_attribute.attrelid
WHERE attname LIKE '%item%'
AND relkind = 'r'

CSVの便利な取り扱い

PostgreSQLで出力したファイルをPHPに食わせるプログラムを作っているのだが、どうしてもファイルの行頭にヘッダ行をいれたかった。
ファイルの出力なのでCOPYコマンドを利用するのだが、その中で良いオプションを発見した。

続きを読む "CSVの便利な取り扱い"

ウィンドウ関数が使えるバージョン

OLAPウィンドウ関数が使えるといろいろ便利。
SELECT
ROW_NUMBER() OVAER (ORDER BY hogehoge ), fuga
FROM honyarara;

こんな感じで行数をセットできるしね。
ただ、データウェアハウスを想定した世界なので、ちょっと古いバージョンでは利用できない。

PostgreSQL: V8.4～
Informx : V11.7～
SQL Sever: V2008～
Oracle: v8.1.6～

Informix11.5でrow_number()を使ってみたらシンタックスエラーとして怒られてしまたたですよ orz
バージョンごとの機能差異は時々注意ですな。

複合インデックス非推奨

インデックスの使用に当たって、よく言われるのが
「同一テーブルへのアクセスは１つのインデックスしか使われない」
というアレ。
これは正しいけど正しくないよ、という内容です。

続きを読む "複合インデックス非推奨"

pg_xlogのパンク再び……

先日再びpg_xlogをパンクさせてしまった(^^;

原因はｐｇAdminIIIのSQLエディタから一度に大量の更新をする複数のupdate文を実行してしまったため。
SQLエディタでクエリの実行を行った場合、複数のSQL文であっても単一トランザクション扱いとなるため、すべてのSQLが完結するまでWALに書かれる単位はひとかたまりなのだと思われる。

そのためpg_xlogが一定量になったところで自動的にgzipする処理を組み込んであったのだが、そもそも単一のWALがディレクトリのクォートを超えてしまい、完全なパンク(^^;
半端にバックアップも動いたようでpg_backupsも道連れでパンクしている状態だった。
そしてPANICを出してサービスがダウン……

今回は完全に領域不足になったので、なんとかクォート割り当てを増やしてから再起動し、肥大化したインデックスを再構築して領域確保をしたのでした(^^;

特定の文字以外を含む行の抽出

英数文字といくつかの記号以外は含まれないようにしたい、というバリデーションニーズは当然よくあることと思う。
もちろん入力段階(クライアントサイド)ではじくべきものではあるが、サーバサイドでもポカ除けをすることは必要だ。
あるいはポカよけが働いていなくて、DBに格納されてしまってから調査が必要になることもあるかと。

当然こうしたニーズに対してはいつだってボクらの味方は正規表現である。SQLだけでこの問題のあるレコードを探してみよう。

続きを読む "特定の文字以外を含む行の抽出"

PostgreSQLの無停止メンテナンスツール

以前、Reindexをシステム無停止で実施するためのbashスクリプトをちょこっと書いたことがあるけども、予想通りNIH(車輪の再発明)であった（笑）
PostgreSQL8.3以後で使用できる拡張モジュールでずばりなものがすでにリリースされている。

pg_reorgである。

続きを読む "PostgreSQLの無停止メンテナンスツール"

PostgreSQLにおけるchar/varchar/text

「あとPostgreSQLではchar型を使うメリットはないからvarchar型にしとこうね(ref:フラグはbooleanで頼みます)」
ということを先日のエントリーで書いたところ、知人からほんとにそうなの？　という問い合わせをいただいたのでちょこっとメモり。

続きを読む "PostgreSQLにおけるchar/varchar/text"

postgresql.logの有効活用

性能改善を行なう場合にスロークエリ分析は大きな比重を占める。
設定によって遅いクエリの結果をpostgresl.logに出力でき、かつcsv形式での出力もできる。
今回はその出力したログをどう活用しようか、というお話。

Let's Postgresで紹介されている[スロークエリの分析]を参考に、私がやっている微修正をちょこっとご紹介。

続きを読む "postgresql.logの有効活用"