20万行超のテーブル内の複数行を削除できない

Question

###前提・実現したいこと

あるテーブルが20万行を超えてるので、
いらない行を削除してダウンサイズしたいが、
データが大きすぎるのか、削除できません。

何か解決策があれば教えていただけないでしょうか？

###発生している問題・エラーメッセージ

Tera Term VTでSQL文（DELETE）を打ちましたが、
途中で止まってるのか
ずっと処理中なのか
反応がなくて困っています。（カーソルが点滅したまま）

###該当のソースコード

2015-12-31以前を削除したい（行数不明）
DELETE FROM order2 WHERE sid IN (SELECT id FROM sale2 WHERE rst < '2015-12-31 00:00:00');

2005-12-31以前でも削除できない（2530行）
DELETE FROM order2 WHERE sid IN (SELECT id FROM sale2 WHERE rst < '2005-12-31 00:00:00');

数行すら削除できない
DELETE FROM order2 WHERE id < 10;

1行なら削除できる
DELETE FROM order2 WHERE id = 30;

###試したこと

あと、
テーブルをCSVでダウンロードして
対象行を削除後、それをアップロード
を試しましたが、ダウンロードできず
（少ない行のテーブルならダウンロードできるのですが）

psql# \o /home/www/dbiba000/public_html/order2.csv
psql# \a

psql# \pset fieldsep ','
Field separator is ','.
psql# SELECT * FROM order2 ORDER BY id;
psql# \o

###補足情報(言語/FW/ツール等のバージョンなど)

CPU Intel Xeon / 2.4GHz Dual Processor
メモリ 2048 MB
ハードディスク 72 GB (RAID1:Mirror)
OS FreeBSD 4.7-RELEASE
データベースサーバ PostgreSQL 7.2.3

Answer 1

エラーの原因が不明なので、上手くいくかは、定かではありませんが、deleteするのではなく必要なレコードを出力し、テーブルを作成し直して（truncate）LOADされてはどうでしょうか？

--copy out where id 
\copy (SELECT * FROM order WHERE id > 10) to /home/www/dbiba000/public_html/order2.cpy

--table recreate 
drop table order;
create table order ....;
-- or truncate
truncate table order;

--copy back
\copy order from /home/www/dbiba000/public_html/order2.cpy

テーブルのdrop＆create部分のddlはpg_dumpでも作成できます
pg_dump database -s -c -t order >/home/www/dbiba000/public_html/order2.ddl

Comments

[hohohotn]

vacuum full後、状況変わらずでしたが、
いつのまにか、deleteされて、8万行（2015-12-31以前）まで減らすことができました。
なぜかは分かりません。

その後、
\copy order2 to '/home/www/dbiba000/public_html/order2.csv'';
でコピーできました。

ただ、DELIMITERをつけるとエラーになり、困っています。
\copy order2 to '/home/www/dbiba000/public_html/order2.csv' DELIMITER ',';

他に下記を試しましたが、
dbiba000=> \o /home/www/dbiba000/public_html/order2.csv
dbiba000=> \a
Output format is unaligned.
dbiba000=> \pset fieldsep ','
Field separator is ','.
dbiba000=> SELECT * FROM order2 ORDER BY id;
calloc: Cannot allocate memory

となり、メモリが足りないようです。
やはり、現サーバーでは、8万行を扱うには無理があるのでしょうか？

[A.Ichi]

当初20万件有ったので8万件に減った時点でエラーには、成りずらいと思われます。
一度vacuum order2（できればvacuum FULL order2)をされてはどうでしょうか？削除の12万件がdeadタプルとして存在していると思われます。
また、DELIMITERを付けた際のエラーメッセージは何でしょうか？

[hohohotn]

vacuumしましたが、やはり8万件でも下記の削除ができません。
DELETE FROM order2 WHERE sid IN (SELECT id FROM sale2 WHERE rst < '2016-01-12 00:00:00');

DELIMITERを付けた際のエラーメッセージは、
\copy order2 to '/home/www/dbiba000/public_html/order2.csv' DELIMITER ',';
\copy: parse error at 'DELIMITER'
となります。

[A.Ichi]

PostgreSQL 7.2.3環境が無くエラーにはなりません。（V8.3)
同じようにcopyコマンドがありますのでこちらを試されてはどうでしょうか。
copy (SELECT * FROM order WHERE id > 10) to '/home/www/dbiba000/public_html/order2.cpy';

[A.Ichi]

上記はDBが同じサーバ上に有る場合です。もし異なる場合は、”to stdout;"として外でリダイレクションして下さい。

[hohohotn]

\copy order2 to '/home/www/dbiba000/public_html/order2.csv'
DELIMITER ','なしでうまくダウンロードできました。
ありがとうございました。

Answer 2

Diskが不足しているのでしょう。
一旦　vacuum full を行って下さい。
その後、deleteを試してみて下さい。

deleteで状況が変わらないなら、
テーブルをtruncate後にvacuum fullしてCSVでの取込を。

それでも変わらないならDISK容量が十分な環境にするしかないと思われます。

Comments

[hohohotn]

truncateで全データ削除されるのは、困ります。

ですが、
vacuum full後、状況変わらずでしたが、
いつのまにか、deleteされて、8万行（2015-12-31以前）まで減らすことができました。
なぜかは分かりません。

しかし、8万行でも、CSVでの取込みができず、
これもDISK容量の問題でしょうか？

下記のようにしましたが
ファイルはできますが、中身が空

psql# \o /home/www/dbiba000/public_html/order2.csv
psql# \a

psql# \pset fieldsep ','
Field separator is ','.
psql# SELECT * FROM order2 ORDER BY id;
psql# \o

[sazi]

CSVに出力しておいてからTruncateです。

csvのサイズはどの程度でしょうか？
また、Diskの空き容量はどの程度でしょう？
postgresはメンテナンス時に一時的に最大で通常の３倍の容量を必要とします（あくまでテーブル単位ですが）ので、増分＋αのDisk資源は確保しておいた方が良いと思います。

既にデータファイルが破損している可能性もありますので、バックアップを取ってから、クリーンインストール後の再構築を行ってみてはどうでしょうか。

[hohohotn]

CSVは5MBほどです。
Diskの空き容量はレンタルサーバーなので、分かりません
WebとMailの部分は分かるのですが（データベースは別サーバーなので）

Answer 3

ディスクに不良ブロックができていないか、ログを確認して下さい。

Comments

[hohohotn]

レンタルサーバーですが、ログ確認できるでしょうか？