質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Tera Term

Tera Termは、TeraTerm Projectが開発する国産のWindows向けターミナルソフト。telnetプロトコルでのリモートホストへの接続やCOMポートのシリアル接続が可能で、マクロが利用できます。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

3回答

819閲覧

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

hohohotn

総合スコア8

Tera Term

Tera Termは、TeraTerm Projectが開発する国産のWindows向けターミナルソフト。telnetプロトコルでのリモートホストへの接続やCOMポートのシリアル接続が可能で、マクロが利用できます。

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

0グッド

2クリップ

投稿2017/12/31 10:54

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

あるテーブルが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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

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

sql

1--copy out where id 2\copy (SELECT * FROM order WHERE id > 10) to /home/www/dbiba000/public_html/order2.cpy 3 4--table recreate 5drop table order; 6create table order ....; 7-- or truncate 8truncate table order; 9 10--copy back 11\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

投稿2017/12/31 16:01

編集2018/01/01 20:17
A.Ichi

総合スコア4070

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hohohotn

2018/01/05 00:14 編集

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

2018/01/05 01:29

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

2018/01/12 04:45

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

2018/01/15 08:19

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

2018/01/15 08:21

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

2018/02/05 15:16

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

0

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

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

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

投稿2017/12/31 13:18

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hohohotn

2018/01/04 23:28

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

2018/01/05 00:31

CSVに出力しておいてからTruncateです。 csvのサイズはどの程度でしょうか? また、Diskの空き容量はどの程度でしょう? postgresはメンテナンス時に一時的に最大で通常の3倍の容量を必要とします(あくまでテーブル単位ですが)ので、増分+αのDisk資源は確保しておいた方が良いと思います。 既にデータファイルが破損している可能性もありますので、バックアップを取ってから、クリーンインストール後の再構築を行ってみてはどうでしょうか。
hohohotn

2018/01/12 04:22

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

0

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

投稿2017/12/31 12:12

hichon

総合スコア5737

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hohohotn

2018/01/04 23:18

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問