r_nobuホームページ (のぶねこブログ)

例えば、PostgreSQLのデータベースをダンプするためにLinuxのコンソール画面などでpg_dumpコマンドを使用した際に「timestamp out of range」と「FETCH 100 FROM _pg_dump_cursor」といったエラーが出てダンプに失敗することがある。

＜例＞
pg_dump -U postgres -D nobuneko_db > 20100421.nobuneko_db.dmp
pg_dump: SQLコマンドが失敗しました
pg_dump: サーバのエラーメッセージ: ERROR: timestamp out of range
pg_dump: 次のコマンドでした: FETCH 100 FROM _pg_dump_cursor
pg_dump -U postgres --table=neko_table1 -b nobuneko_db > nobuneko_db.neko_table1.dmp
pg_dump: ERROR: timestamp out of range
pg_dump: テーブル"neko_table1"の内容をダンプするSQLコマンドが失敗しました: PQendcopy()が失敗しました。
pg_dump: サーバのエラーメッセージ: ERROR: timestamp out of range
pg_dump: 次のコマンドでした: COPY public.neko_table1 (neko_id, neko_time, neko_title,neko_body) TO stdout;

上記の例では、「timestamp out of range」というエラーより、データベース「nobuneko_db」のテーブル「neko_table1」のタイムスタンプ型のカラム（上記例ではタイムスタンプ型のカラムは「neko_time」のみとする）で異常な値が入っていると推測できる。
このような場合、psqlでデータベースに接続して異常な値が入っているレコードを特定して対応方法を考える。以下に対応例を記載する。

(1)データベースに接続
psql -U postgres nobuneko_db
PostgreSQL の会話型ターミナル、psql 8.1.10 へようこそ

\copyright とタイプすると、配布条件を表示します。
\h とタイプすると、SQL コマンドのヘルプを表示します。
\? とタイプすると、psql コマンドのヘルプを表示します。
\g と打つかセミコロンで閉じると、クエリーを実行します。
\q で終了します。

(2)データベースに無事に接続できたら、検索ができるか適当に試してみる。
nobuneko_db=# select max(neko_id) from neko_table1;
max
------
2011
(1 行)

上記例では、全レコードにおいてカラム「neko_id」ではおそらく異常がないと分かる。

(3)検索条件を変えながら異常レコード、異常カラムを特定する。
nobuneko_db=# select * from neko_table1 order by neko_id desc limit 100;
ERROR: timestamp out of range
nobuneko_db=# select * from neko_table1 order by neko_id desc limit 10;
ERROR: timestamp out of range
nobuneko_db=# select * from neko_table1 where neko_id = 2004;
ERROR: timestamp out of range
nobuneko_db=# select * from neko_table1 where neko_id != 2004;
→この最後の検索条件でエラーが起きることなく正常に検索できたため、neko_idが2004のレコードのみで異常があると判明。

さらに（予想通りとは言え、念のため）selectの対象をneko_timeだけにして検索し、エラーが起きることを確認する。
nobuneko_db=# select neko_time from neko_table1 where neko_id = 2004;
ERROR: timestamp out of range

(4)異常レコード、異常カラムが特定できた場合、異常カラムに適切な値をセットしてアップデートする。
update neko_table1 set neko_time = '2010/04/21' where neko_id = 2004;
※'2010/04/21'の部分は「now()」等適切なタイムスタンプ型の値を指定する。

(5)問題が解消したことを確認する。

(6)何故異常な値が入ってしまったのかを検討し、対策する。
Webのフォームで値を入れるような仕組みがある場合、データベースに値を渡す前にユーザが手入力した値が適切な値であるかをプログラムでチェック（入力値チェック）していると思うが、同プログラムに欠陥があると、不適切な入力値のままデータベースに値を渡してしまい、データベース側も不適切な値のまま受け入れてしまい、同エラーになることがある。このような場合は、入力値チェックプログラムを適宜修正して対応することなどが考えられる。
PostgreSQL側でもタイムスタンプ型であるべきカラムにそうでない値を入れようとした時に「そんな値を入れるな！」と拒否してくれたらいいのになあ、と思うけれど、そういえば、数値型に文字列型の値を入れようとしたらエラーを返してくれる機能はあったと思うので、PostgreSQL側の入力値チェックも万全ではないということなのだろう。というわけで、やはり、データベースに値を渡す側（WebだとPHPプログラム等）のチェックプログラムでこの種の問題は対応を続けないといけないのだろうなぁ・・・。

それにしても、たった1つのカラムに異常値が入っているだけで、ダンプに失敗する（「FETCH 100 FROM _pg_dump_cursor」というエラーになる）仕様は、ちょっと怖い。ダンプが取れないと、ダンプでの日次バックアップ処理をしている場合などは困るなぁ・・・。