08/02/26 04:10:08
プログラムロジックを介さず、DBのみで…というのが適切な表現かどうかはわかりませんが、
PostgreSQLで、1対2とか、1対多関係のうち1対nのnを限定して表現する事は可能ですか？
また可能でしたら是非その手法をご教示頂きたいと思います。よろしくお願いします。
例えばサッカーや野球の試合は必ず2チーム間で行われますが、
試合{試合ID, 一方のチームID, 他方のチームID }
と1テーブルで冗長化した表現でなく、
試合{試合ID} <1-2> カード{試合ID, チームID}
みたく正規化した表現を使いたいのですが...
やっぱり冗長化表現が一般的なんでしょうか？
と、ここまで書いてるうちに今回必要な1-2に限定して思いついたんですが、
カードテーブルにbooleanみたいな2値のカラムを新たに設けて、そのカラムと試合IDの組を
uniqueというかPKにすればなんとかなるかなと…
booleanだとアレなのでenumにすれば、他の1-nもenumの定義によって制限できる…かな。
いやでもこれだと1-(n-m)も許容しちゃうかな…
というのはさておき、何かもっとスマートな手法があれば…何卒ご教示お願いします。
276:NAME IS NULL
08/02/26 07:37:02
>>275 nを厳密にしたい目的はなに？
表現の曖昧さを避けるだけなら、冗長化した形で CHECK(一方のチームID < 他方のチームID) とか。
チームIDで検索したいだけなら GIN 使う手もあるし。
277:NAME IS NULL
08/02/26 08:35:01
>>275
チームIDはチーム情報のマスターに格納してあるんでしょ？
278:NAME IS NULL
08/02/26 14:54:48
>>276
まさに、その何かの試合に関するテーブルを作ろうと思ってまして、
その際必ず試合1に対してチームが2になります。
カードを外に出したいのは、集計のためだったりします。
あるチームを基点に試合を検索する場合、一方か他方かがそのチームで、
検索されたデータからもあるチーム以外のチームを見つけなきゃいけない、
というのが面倒な気がしました。
一方外に出しておけば、おそらくクエリだけでなんとかなります。
検査制約とか配列を使うのはアリかもしれません。
ありがとうございます。
>>277
明示しませんでしたが、チームIDはチームテーブルのPKです。
279:NAME IS NULL
08/02/26 14:57:34
>>278
サッカーなんて単語が出てたから、PKってそっちの方かと思ってたよ。
いや、なにやらごちゃごちゃ書いてあったから詳しくは読んでないんだけどね。
280:NAME IS NULL
08/02/26 15:32:19
ソフトのロジックが複雑でも、
テーブル設計は、標準的でシンプルなほうがいいかもね。
メンテなどしやすい方が便利な事が多い。
281:NAME IS NULL
08/02/26 21:47:49
>>271
>>273
tsearch2よりはludiaの方がいいんじゃないか。
tsearch2は形態素解析。ludiaはN-gram。
形態素解析だとLIKE %% と違った結果になるぞ。
282:NAME IS NULL
08/02/26 23:00:26
LIKEやN-gramなら漏れが無いってわけでもないので、
割り切れるならば形態素解析も使いどころはある。適材適所で。
283:NAME IS NULL
08/02/26 23:54:36
WEBプログラミング板でこちらで聞いてくれと教えていただいたので質問です。
自分は初めてPostgresに触るド素人です。
Postgres8.2.5を使ってDBを作ろうとしてます。OSはLinuxです。
データベースにログイン可能なユーザーを作るため、
スーパーユーザーでCREATE ROLE name WITH PASSWORD 'pass' LOGIN;
と設定しようとしたところ『-bash: CREATE: command not found』とエラー吐かれました。
おかしいなと思ってためしにcreateuser -P nameとしたところ
『CREATE ROLE』 と出ました。
ROLE（ユーザー？）が出来たのでログインしようと試みたところ、
『su: name というユーザは存在しません』とエラーを吐かれました。
CREATE ROLEでもcreateuserでもユーザーが作れない理由と対処方がわかりません。
どなたか教えていただけないでしょうか？
284:NAME IS NULL
08/02/27 00:33:16
>>283
>CREATE ROLE name WITH PASSWORD 'pass' LOGIN;
これはpsql等で実行するもの。
>createuser -P name
こっちはシェル(bash等)で実行するもの。
でこっちは実行出来たっぽいね。userつってもあくまでdb用userで
psql -U name -d dbname
と言う具合につかう。
ちょっと初歩的すぎるので、何か本でも買って読んだ方がよさげ。
285:NAME IS NULL
08/02/27 01:51:48
>>284
丁寧な解説＆ご指摘ありがとうございます。
どこで使うかをわかっていないせいで、知識がごちゃ混ぜになってたんですねorz
一からきちんと勉強しなおします。ありがとうございました。
286:NAME IS NULL
08/02/27 15:48:47
8.3.0 for Windowsです。
ふとログを見たら、plugin_debugger.dllというのが頻繁にロードされていました。
postgresql.confを見たら、
shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'
がデフォルトで有効になっていたんですが、このplugin_debugger.dllとは何者なんでしょうか？
287:NAME IS NULL
08/02/27 17:03:48
プラグインのデバッガじゃね？
288:NAME IS NULL
08/02/28 00:26:16
>>286 PL/pgSQL のデバッガだったはず。
使わないなら単なるリソースの無駄なので外すべし。
289:NAME IS NULL
08/02/28 00:41:09
plpgsqlのデバッグって出来たんだ……！
290:NAME IS NULL
08/02/28 13:47:48
pgpool使用時に、100回に1回くらい通常時1秒程度のplpgsqlによるストアドの返答に
数十秒、長いときだと数分かかるんですが、原因がまったく検討つきません。
アクセスは一秒数回ありますが、autovacuumの実行なども発生には関係ないようで
一度killしてから同じSQLを走らせると1秒程度で終わります。
また、ストアド実行中に同じストアドを別の引数で呼び出しても、全く問題なく実行できます。
ストアド自体にはみたところ問題はありません。
原因や解決策などに心当たりがございましたら、ご教示ください。
291:NAME IS NULL
08/02/28 14:45:33
自分は、詳しくないので見当つきませんが、
アドバイスを受けるのでしたら、
少なくとも、バージョンや環境は提示した方がいいでしょう。
292:290
08/02/28 14:49:09 NWie+e5y
申し訳ありません。
FreeBSD6.3、amd64
PostgreSQL8.3
pgpool-II version 2.0.1
です。
また、稼動を続けるとCPU使用率が増加していき、postgresを再起動すれば治ります。
293:NAME IS NULL
08/02/28 15:09:55
負荷が高くなっていくのは、運用では困りますね。
原因が見つかるといいですね。
8.2.x など旧バージョンと比較して試してみるのはどうでしょうか。
294:NAME IS NULL
08/02/28 18:03:40
8.3もpgpoolII 2もまだまだ枯れていないからなぁ。
295:NAME IS NULL
08/02/29 00:52:01
>>290
問題を切り分けるのに、PostgreSQLだけで同じ事象が
発生するか試したら？
296:NAME IS NULL
08/02/29 13:45:44
そうだな。pgpool切り離しと、ネットの影響もあるからlocalhostだけでやるのも
297:NAME IS NULL
08/02/29 21:16:15 sA1ohjpH
ファイアウォールとＳＥＬＩＮＵＸを切ってみては？あとは
ＰＨＰとかから呼んでるなら、永続的な接続を使ってると色々
リソースを食い続けるので、非永続で。ＣＰＵ使用率はpostgreSQL
が使ってるのかな？ ８．３に興味あるけど、まだ怖いね。
pgpool-II Ver 2は、こういった分野のソフトにしてはバグが多い印象。
ver 1 を使ったほうがいいかもww
298:290
08/03/01 21:56:32
すみません、SQLを改善したら解決しました
問題きりわけのためにplpgsql内のWHERE条件を分割して実行したところ
再発しなくなりました。
元のSQLが間違ってたようには思えないのですが…
pgpooｌをはずして、4BSD、ULEどちらのスケジューラーでも試しましたが
問題はpostgresql単体の問題のようです。
299:NAME IS NULL
08/03/01 22:12:52
時間かかった時は不適切な実行計画が選択されちゃってたとか？
300:anonymous
08/03/02 08:06:09
>>298
よかったですね。
旧バージョンでは問題なくても、
新バージョンで問題が発生する場合もあると思います。
301:NAME IS NULL
08/03/02 12:58:39 4NEnxdcC
>>298
参考のために、オリジナルと分割後のSQLを教えて。
もしPostgreSQLのバグなら、誰かが対処してくれるかもしれないし。
302:920
08/03/02 22:09:11
調査を続けていたところ、NULLが絡んだときにSQL自体が
PERFORM 1 FROM target_table
になっている場合があることに気がつきました。
これの応答に時間がかかっていたようです。
お騒がせしまして申し訳ありません。
SQL間違いとはお恥ずかしい…
303:290
08/03/02 22:11:26
すみません、920ではなくて290です。
304:NAME IS NULL
08/03/02 22:22:55
URLﾘﾝｸ(www.postgresql.jp)
> 重要なお知らせ：
> 2月24日にwww.postgresql.jpサーバが部外者によりクラックされていた事が判明致しました。このため、サイトは安全な別サーバに移動し、トップページのみ表示されるようになっています。
> 現在、復旧作業中です。今のところルート権限を取得された形跡は発見されていません。 詳しい情報は後日公開致します。
> ご迷惑とご心配をお掛けする事をお詫びいたします。
305:NAME IS NULL
08/03/03 14:31:40
うわぁ…
postgresql.jp って、SRAが事務局でサーバも提供してたのは、もう昔の話?
306:NAME IS NULL
08/03/04 16:38:38
うげ、8.3って数値から文字列の暗黙の型変換がされないのか。
怖すぎてアップグレードできん。
307:NAME IS NULL
08/03/04 16:49:33
2月27日 9:13 外部よりjpug.postgresql.jp(www.postgresql.jp)よりsshブ
ルートフォースアタックを受けていると連絡が入る
10:20 pscan2が走行しているのを確認し停止させる
図らずもsshdのパスワード認証が有効になっていたため、ブ
ルートフォースアタックで作業用アカウントのパスワードが解
析され侵入を許していた。
sshdのパスワード認証を禁止し、RSA認証のみとした。
17:00 改ざん等がないか確認開始
28日 14:00 改ざんと個人情報の流出が無いことを確認した。
3月 1日 告知用サーバ運用開始
308:NAME IS NULL
08/03/04 22:44:35
>>306
そんな怖くないだろ
309:NAME IS NULL
08/03/04 23:52:04
>>306
俺はchangelogを読まずに酷い目に遭ったぜ・・・orz
310:NAME IS NULL
08/03/05 12:00:43
>>308
どの言語出身かで分かれるだろうなｗ
311:NAME IS NULL
08/03/05 19:44:44
もともと date_column = 2008-03-01 っていうミスをしたという話があって、
date_column::text = (2008-03-01)::text = 2004::text
と解釈されるのはまずいとして、暗黙の型変換は削られたらしい。
ミスらないためには悪くは無いと思うけどね。
必要なら自分でキャスト追加すればいいだけだし。
312:NAME IS NULL
08/03/05 23:06:59
質問させてください。
pg_dumpall でデータベース全体をバックアップした後、
このデータを元に、部分的な復旧を行う事は可能ですか？
手元に pg_dumpall で取ったデータしかなくて、
それを別サーバに復元したいのですが、
全部ではなく一部分だけ復元したいです。
調べた所、丸々復元する方法は見つけたのですが、
部分的な復元方法を見つける事が出来なかったので
よろしくお願いします。
313:NAME IS NULL
08/03/06 02:54:14
部分的な具体例を示した方がいいと思います。
テーブル単位なのかレコード単位なのか、
カラムや部分文字列なのか。
314:NAME IS NULL
08/03/06 03:00:03
>>312
バックアップ時のオプションにもよるだろうけど
バックアップされたファイルは基本的にテキストファイルだから
必要な部分だけに加工してrestoreするか
一時的にどこかに全てrestoreして、必要な部分だけ
個別にpg_dumpし直すか、だね。
315:NAME IS NULL
08/03/06 10:20:23
なんかCLUSTER発行しても並べかえてくれない気がするんだが…
バージョンは8.2.6で
316:NAME IS NULL
08/03/06 16:33:27
ローカルでpgsql立てて全部入れてから改めて必要な部分を戻せばよいのでは
317:NAME IS NULL
08/03/06 23:29:33
質問です。
カラムの並び順を変えるには、この方法しかないのでしょうか？
テーブル Fruit = カラム名 [orange(int),apple(int),banana(int)]
BEGIN;
ALTER TABLE fruits RENAME apple TO apple_old;
ALTER TABLE fruits ADD COLUMN apple text;
UPDATE fruits SET apple = CAST(apple_old AS text);
ALTER TABLE fruits DROP COLUMN apple_old;
COMMIT;
などを複数回発行して、カラムを追加、削除により並べ替える。
MySQLには、指定したカラムとカラムの間にカラムの挿入などができたと思うのですが、
ポスグレでは、そのような機能はないのでしょうか？
よろしくお願いします。
318:NAME IS NULL
08/03/07 00:41:47
無いんじゃないかなあ
そういう時はテーブル丸々コピーして、新しい並びで空のテーブル作って
select insertで一括挿入っていうのをよくやるけど
319:NAME IS NULL
08/03/07 08:09:12
VIEW 使えば？ そもそもRDBMSでカラム順に依存したSQLを書くのが間違い。
320:NAME IS NULL
08/03/07 10:36:16
うん、俺もVIEW使うのが正解だとｵﾓ。
>>319の言うことは正論。
321:NAME IS NULL
08/03/07 10:51:18
>>317
純粋に、なぜ列の並び替えが必要になるのか知りたい。
322:NAME IS NULL
08/03/07 11:07:36
select * from tabの結果が気持ち悪いからじゃないの？
俺も後からカラムを追加するときに、一番最後じゃ気持ち悪いときが結構ある。
323:NAME IS NULL
08/03/07 11:33:34
>>322
気持ち悪いのは人だけでしょ。
プログラムを実行するコンピュータには何の関係もない。
324:NAME IS NULL
08/03/07 14:16:29
>>322
つか*で取るからそういうことになる。
列指定汁ｗｗｗ
325:NAME IS NULL
08/03/07 14:46:23
>>323 実行する人の精神衛生状態は作業効率に直結する
326:NAME IS NULL
08/03/07 14:48:26
>>325
なんて言っている人の作業効率なんてたかが知れている
327:NAME IS NULL
08/03/07 14:52:24
すんません、vacuumに関して教えてくださいな。
vacuum -a -f -zは毎日夜中に一度やるとして・・・
・オンライン中にvacuumしていいのですか？
（極端に遅くなるとか、壊れる等はありますか？って意味が一番強い）
・いいのであれば、vacuumのオプションは何が最適かしら？
土日は更新メイン。平日は参照メインなシステムです。
バージョンは、8.2.6です。
よろしくお願いします。
328:NAME IS NULL
08/03/07 14:57:09
MyISAM の ORDER BY 無しのデフォルトの並び順での取り出しは、
確かに挿入順であって結構速い気がする。
この動作に依存して組む人たちもいるんじゃないだろうか。
329:NAME IS NULL
08/03/07 15:02:16
>>328
それ行の話でしょ？ いま問題になっているのは列の並び
330:NAME IS NULL
08/03/07 18:19:48
vacuumは遅くなるから夜中にやってるなら
それで良いと思うけど。
うちは随時更新、参照入るシステムだけど、
処理重い時間にvacuumやると結構やばい。
vacuum１回で足りないと感じるのは何故だろう？
メモリが十分にあるならチューニングしたらどう？
URLﾘﾝｸ(www.thinkit.co.jp)
autovacuumはどうなのか知らん
331:NAME IS NULL
08/03/07 19:56:07
世の中にはソースのないプログラムとかもあるからなぁ。
データベースの列の並びがちがうだけで動かなくなる糞プログラムなんかに限ってソースが無かったりする。
俺は今またそれに引っかかったよ。 ｏｒｚ
332:NAME IS NULL
08/03/07 23:47:46
そんなプログラムにこそ、カラムの挿入なんてやったらダメなんでは。
333:NAME IS NULL
08/03/08 09:12:47
>>327
vacuum -a -z だけで十分。
VACUUM FULL は必要ないよ。
オンライン中の VACUUM で DB が壊れることはないし、
極端に遅くなることもないけど、負荷が気になるなら
vacuum_cost_delay とか VACUUM のコストを弄ってやればいい。
334:NAME IS NULL
08/03/09 01:19:17
create table hoge( foo integer not null );
create table fuga( bar integer not null check( bar < foo ) ) inherits( hoge );
こんな感じに継承元と先とでの数値の検査制約って出来ないんだっけ？
335:NAME IS NULL
08/03/10 23:45:35
>>334 まったく問題なくできるようだが。
336:327
08/03/11 14:16:00
>>330,333
ありがとう。
>vacuum１回で足りないと感じるのは何故だろう？
うーん、一個件数の多いテーブル（100万件ぐらい）があって、
そこが土日の更新の対象で且つ、結構他のテーブルとjoinしてるんですよ。
一応、explainで見て、indexの見直しと対象SQLのチューニングをして、
大分早くなったんだけど、１年で100万件程度増えるので、
いつかはまた遅くなるんだろうと感じてます。
なので、手軽？に早くする手法はないかと調べていた訳です。
オンラインのVACUUMで壊れないのであれば、
一度負荷と相談して試して見ます。
ありがとさんでした。
ところで、V8.x系のメモリチューニングで、1GB以上共有メモリを取っても、
メモリマネージメントの都合で逆に遅くなるってあるんだけど、
やっぱりそういう物なんですかね？
サーバー4GBにしたのに無駄？
337:NAME IS NULL
08/03/12 00:35:41
>>336
パーティショニングすれば？
> サーバー4GBにしたのに無駄？
共有メモリ以外はOSキャッシュとして使われるから、
まったく無駄にならん。
338:NAME IS NULL
08/03/12 19:51:58
>>337
レスありがとう。
パーティショニングを今度試してみるダス。
日々の売り上げデータに近い構造なので、
効果は大きそうな気がします。
大抵の集計自体も長くて３ヶ月ぐらいだし。
ありがとうございました。
339:NAME IS NULL
08/03/13 00:47:20
共有メモリ１Ｇ以上だと遅くなるのってどっかに書いてある？
340:NAME IS NULL
08/03/13 08:07:54
> 共有メモリ１Ｇ以上だと遅くなる
というよりは、単に増やせば増やすほど速くなるわけではないってこと。
DB/OS 2層でキャッシュするよう設計されているから。
他のデータベースでも似たようなことはよくある。
341:NAME IS NULL
08/03/13 19:38:18 RTgKtkUI
>>339
8.1のチューニングのページに書いてあった気がします。自分の8.1
での経験ですが、２０Ｇとか指定すると、小さなテーブルのdrop table すら
１秒くらいかかった記憶が、、、。
>>338
おなじく8.1での話ですが、パーティショニングしたテーブルをjoinす
ると、selectがシーケンシャルになって激遅になるケースがありました。
同じ構造で同じＳＱＬでも8.3はインデックスを使ってくれるんですけどね
342:NAME IS NULL
08/03/13 20:43:28
>>341 たしかに drop table は遅くなるかも。メモリ全部舐めるから。
drop table を高速化する目的でチューニングすべきかは疑問だが。
343:NAME IS NULL
08/03/14 00:29:37
temp tableしまくるなら効果ありそうだなｗ
344:NAME IS NULL
08/03/14 01:03:30
>>341
> ２０Ｇとか指定すると、
実メモリはいくら積んでたの？
345:NAME IS NULL
08/03/14 01:19:18
>>343 temp table はローカルバッファなので関係なかったりする。
346:NAME IS NULL
08/03/14 09:25:11
>>339
URLﾘﾝｸ(itpro.nikkeibp.co.jp)
ここ以外にも、具体的な数値を上げて書いてあったところが
あったと思うけど、おもいだせませんでした。
347:NAME IS NULL
08/03/14 23:16:26 gKUPvhD2
drop table だけじゃなくて、delete して insert する処理も、もっさり感が
ありました。０コンマ何秒の処理だったけど、共有メモリ１．５Ｇ設定時の
２倍くらいの所要時間だったイメージ。ＤＢ全体のサイズは６Ｇ程度です。
>>344
会社のサーバなので、たしか３０Ｇ台でした。
348:NAME IS NULL
08/03/15 07:58:18
>>347 そんな豪勢な環境はめずらしいからなぁ。
共有メモリ20GBなんて大きな設定は、テストが足りてないのかも。
4GBを超えるとちょっと心配だな。
あまらせてもOSがキャッシュとして使うから、無駄にはならないし。
349:NAME IS NULL
08/03/16 01:45:48
>>313>>314>>316
亀レスすいません。
>>316の方法で行く事にします。
どうもありがとうございます。
350:NAME IS NULL
08/03/17 21:50:44
数百万件以上など、どんどん増えていくテーブルに対して遅くなっていく場合の対処ですが、
日付で管理できるテーブルなら、
例えば、
今月とそれ以前のテーブルに分割してみるとかは？
バッチでレコードの移行を行う必要が出たり、
プログラムの変更が必要になりますが。
その場合、veiwで対応ということが考えられますが、
例えば、レコードが少ないテーブルAとレコードが多いテーブルBの連結を記述しているveiwの場合、
テーブルAにINSERTなど変更が加わる際、
veiwのレコードが多いテーブルBの影響を受けて、多少遅くなるということはあるのでしょうか？
あったとしても、気になる程度じゃないのかな？
351:NAME IS NULL
08/03/17 21:53:17
veiw
352:NAME IS NULL
08/03/17 22:03:59
失礼。よく間違えるんですよ。(^^;
353:NAME IS NULL
08/03/17 22:18:35
>>350
千万件ぐらいならclusterかければ直ぐに結果返ってくるよ
一億件は試した事ないから判らんｗ
354:NAME IS NULL
08/03/17 22:30:52
>>353
ありがとうございます。
やったことはないので、試してみます。
現在、Group by など複数のSQLを一度に発行しているので、
それが完了するのに、20～30秒くらいかかっています。
さらに早くなるとストレスなくなるなぁ。
355:NAME IS NULL
08/03/17 22:32:28
view って、select など、参照する時だけにしか使用されずってことで、
insert や update には、全く影響はないのかな？
356:NAME IS NULL
08/03/17 22:34:53
あ、view って、insert 時など、キーの重複などの整合性をチェックしてたっけ？（＾＾；
なんか、そんなことがあったような。。。
357:NAME IS NULL
08/03/18 18:30:33 cmzwaPqX
pgpool3.2のバグで、FreeBSD4.11上でmakeできないらしいので
3.1.2を入れようと思うのですがどこかでげとできますか？
bugfixした3.4.1をmakeることも考えたのですが、稼動中のサーバに
突っ込むので開発版は避けたいところです。
358:NAME IS NULL
08/03/18 19:18:39 1so+byeI
8.3.1 & 8.2.7
359:NAME IS NULL
08/03/19 04:27:00
>>350
適切なindexを介してアクセスする場合は対数オーダーだから、それが可能ならば
1テーブルで済ませる方が絶対有利。
どうしてもfull scanが必要となる処理ではテーブル分割も考えられるが、
union viewを使う場合、ちゃんとプランナがそれを認識してあらかじめ不要な
テーブルを実行計画から除外することが出来ないとかえって遅くなる。
360:NAME IS NULL
08/03/19 08:04:05
>>359
削除のことも考えた方がいい。
テーブルを分割する最大のメリットは、古いデータの一括削除だから。
絶対に削除しないならば、確かに分割する利点は薄い。
361:NAME IS NULL
08/03/21 02:39:37 bZgunjjY
www.postgresql.jp 繋がらない(´･ω･｀)
362:NAME IS NULL
08/03/21 02:53:43
>>361
未確認情報だけど、またクラックされたから復旧中らしいよ。
363:NAME IS NULL
08/03/21 05:06:01
>>362
その話がMLに流れたって、スラドのアレたまで見たよ。
364:NAME IS NULL
08/03/21 16:07:05
>>359-360
どうも。
１テーブルだと、どんどん増えて数百万レコード件以上になり、
１画面の表示に２０～４０秒はかかってしまいます。
分割すると、数千レコードなので、１～２秒以内で終わります。
将来は数千万レコードにもなり、もっとかかってしまいます。
ひとつのSQLで済むまない１０以上のSQLの処理を行っている情報てんこ盛り画面なので。。。
365:NAME IS NULL
08/03/21 20:59:17 3PY9kdIC
>>364
数千レコードで１～２秒の処理って、、、どんなＳＱＬなんでしょう？
処理範囲分のデータを一時テーブルに取り出して使えば、それで
速くなったりはしないですか？
366:NAME IS NULL
08/03/22 15:18:52
>>364
正規化されていないってオチじゃないよね？
複雑なデータ取得でカーソルすら使ってないとかだったら
単純に技量不足だと思うよ
367:NAME IS NULL
08/03/23 01:51:18
>>365-366
どうもありがとうございます。
>>365
合計で１～２秒ですから、
ひとつのSQLで、0.1秒～0.2秒以下ということですが、それでも遅いですかね？
ほとんど全部が、Group by 使用のSQLです。
一時テーブルへ抽出ですか。
普通のテーブルへInsertでしょうか？
その方法はメモリなど特殊な方法でしょうか？
一時テーブルへ抽出しても、複数同時アクセスが頻繁にあるので、
同じテーブル利用にならないよう個別利用になるよう考慮する必要があります。
同じテーブル利用で重複しても数万レコードですから、
Delete & Insert が早ければ、効果あるかもしれません。
しかし、頻繁に画面更新を行うので、Insertが多くなり、意味があるかどうか。
頻繁にバキュームしないといけなくなるのかな？
>>366
カーソルは使っていません。
カーソル利用とSQLでどのくらいの差があるのか知りませんが、
１０倍以上と劇的に早くなるようでしたら、今後検討してみますが、
はっきり言って、管理と工数のことを考えると面倒ですね。
ただ、カーソル制御はわかりますが、
SQLをどう、カーソル使用で置き換えて利用するのか、勉強不足で、わかりません。
正規化とは、どの意味で、言われているかわかりませんが、
PostgreSQL固有で言われるものでしたら、正規化はしていません。
もちろん、冗長データはありません。
368:NAME IS NULL
08/03/23 03:00:03
>>365-366
次に、ひとつのSQLを紹介します。（改行が多いので）
テーブル名やカラム名は、変えていますが、
生のSQLです。
まだ未熟なので、SQL自体を見直す点もあるとは思います。
テーブル詳細を紹介できないので、わかりづらいと思いますが、
table001が１００万レコード以上で、
約、1.2秒～2.0秒かかります。
数千レコードでしたら、
0.2秒前後です。
このようなSQLが１画面に１０以上あると合計、２０～４０秒以上になってしまうので、
よく使う該当レコードをテーブルを分割して数千レコードにすると、合計１～２秒で収まります。
テーブル設計自体やインデックスの見直し、チューニング次第でまだ高速になるのは間違いないと思いますが、
将来的に、数百万、数千万レコード以上になる前提では、
テーブル分割で１～２秒の現在と同様に早くなるとは思えないので、検討もしていない現状です。
369:NAME IS NULL
08/03/23 03:01:30
（改行が多いので続き）
------------------------
select
case
when ee.kbn001='' then ''
when ee.kbn001='1' then '区分1'
when ee.kbn001='2' then '区分2'
when ee.kbn001='3' then '区分3'
when ee.kbn001='4' then '区分4'
when ee.kbn001='5' then '区分5'
else null
end as kbn001,
case
when ee.kbn001='2' then ee.kbn002
else 'kbn002'
end as kbn002,
aa.name as 名称,
aa.kana as カナ,
aa.tel,
case
when cc.flg001=1 then 'フラグ1'
when cc.flg001=2 then 'フラグ2'
when cc.flg001=3 then 'フラグ3'
when cc.flg001=4 then 'フラグ4'
when cc.flg001=5 then 'フラグ5'
else null
end as flg001,
case
when aa.kbn003='0' then '区分30'
when aa.kbn003='1' then '区分31'
when aa.kbn003='2' then '区分32'
else null
end as kbn003,
'' as dummy,
dd.sum_kei as 計,
translate(aa.biko,'
',' ') as 備考,
bb.jun as 順番,
bb.code01 as コード
370:NAME IS NULL
08/03/23 03:02:06
（改行が多いので続き）
------------------------
from
(
select
min(jun) as jun,
code01
from table001
where
code002 =6
and translate(to_date(data001,'yyyy/mm/dd'),'-','/')='2008/03/21'
group by
code01
) bb
left join data002 aa
on (aa.code01=bb.code01
)
left join data003 cc
on aa.code01=cc.code01 and cc.code002 =6
and translate(to_date(cc.data001,'yyyy/mm/dd'),'-','/')='2008/03/21'
left join
(
select
code01,
coalesce(sum(kingaku001),0) + coalesce(sum(kingaku002),0) as sum_kingaku001
from table001 aaa
where
aaa.code002 =6
and translate(to_date(aaa.data001,'yyyy/mm/dd'),'-','/')='2008/03/21'
group by
code01
) dd
on aa.code01=dd.code01
left join table003 ee
on (aa.code01=ee.code01 and ee.code002=6)
order by bb.jun
------------------------
371:NAME IS NULL
08/03/23 08:41:01
>>369-370
これじゃ遅いだろうね。
code002のカーディナリティが低くてdata001に関数インデックスも
設定していないなら、そのクエリは table001 の seq scan が発生
するというのはわかるかな？
検討する気もないんだったらどうでもいいが。
372:NAME IS NULL
08/03/23 09:38:31
> translate(to_date(data001,'yyyy/mm/dd'),'-','/')='2008/03/21'
「関数(列) = 定数」の書き方ではインデックスが使われない。
data001列をdate型に置き換えて date001 = '2008/03/21' か、
timestamp型なら '2008/03/21' <= date001 AND date001 < '2008/03/22' にするか。
一般的な方針としては、文字列型を減らす（意味にあう他の型を使う）のが良い。
373:NAME IS NULL
08/03/23 10:16:54 YDmnE8vH
>>367
一時テーブルですが、
create template(?) table *** as select * from table001 where ***
とかで、必要な範囲のtable001のデータを持った一時テーブルをメモリ上に
つくれます。他の接続とは干渉しないので、複数処理とかは気にせず
でＯＫ。delete も必要ないです。pgpoolとか使ってるなら、使用後に dropが必要。
んで、一時テーブルを使って処理すれば、とりあえず簡単に速く
なると思うけど
374:NAME IS NULL
08/03/23 11:35:59
>>371-372
ありがとうございます。
文字列だと遅くなるのは、認識していますが、
「関数(列) = 定数」だと、インデックスが使用されませんか。
考えてみると、そうですよね。致命的欠陥ですね。
date001 = '2008/03/21'
の書き方にすると、４倍ほど早くなり、1.2秒が、0.3秒ほどになりました。
まずは、データの統一整備をして、
translate(to_date(data001,'yyyy/mm/dd'),'-','/')='2008/03/21'の記述をやめ、
date001 = '2008/03/21'の記述にしようと思います。
データ型の変更は、時期を見て検討しようと思います。
>>373
ありがとうございます。
簡単にメモリ上にできるのでしたら、時期をみて試してみようと思います。
pgpoolは使用していません。今のところも予定はありません。
375:NAME IS NULL
08/03/23 13:50:33
>>374 の補記
>致命的欠陥ですね。
は、私の設計、SQLが欠陥という意味です。念のため。
376:NAME IS NULL
08/03/24 12:50:57
>>375
大丈夫。その意味が分かる人間ならクエリを見た時点で分かっていると思うぞ。
まあ何だ。頑張れ。
377:NAME IS NULL
08/03/26 20:53:54 HwJR8fjT
LEFT JOINでの結合に関する質問です。
頭の中でごちゃごちゃやる分には、
結合の順番によって内部処理のコストが大分変わりそうですが、
PostgreSQLの実際の作業としては、
SQL最適化した上で実行されるから関係ないのでしょうか？
（実際はもっと複雑ですが）
例えば以下のようなSQLでtable_a、table_b共に数百万件あったとします。
数百万件を結合した上で、table_bで絞り込むより、
table_bで絞り込んだ後にtable_aを結合した方がコストがかからないような場合です。
SELECT *
FROM table_a
LEFT JOIN table_b
WHERE
table_b.id=1;
378:NAME IS NULL
08/03/26 21:07:32
>>377
explain してみるといいのでは。
379:NAME IS NULL
08/03/27 10:42:10
360万件のテーブルAをselect count(1) するのに
85秒もかかっていて悩んでいます。別の730万件のテーブルBは11秒でした。
テーブルAデータ量は1GBは超えていると思います。テーブルBは数十MBくらい。
環境:
OS:Linux(FC7)
PostgreSQL8.3
Core Duo@2.66GHz
Mem:4GB (認識は3.6GB)
設定
shared_buffer 1500MB
work_mem 256MB
shared_bufferは大きくしすぎても遅くなるとかの情報があったのですが、
なんか速くする設定とかありますか？
380:377
08/03/27 16:11:25 tPBRhLWv
>>378
explainの結果をどう見たらよいのか良く分からなかったのです(DB毎に違い過ぎますよね)。
検索していたらよさげな情報を見つけたので、explainしてみます。ありがとうございます。
SQLiteやMySQLのexplainよりも実用的なようですね。
>>379
primary key をはっていないのでは？？
381:NAME IS NULL
08/03/27 22:12:57
>>379
単にテーブルサイズが大きくて、ディスク読み込みが大量に発生しているからでは？
キャッシュに乗った後の速度で良ければ、synchronize_seqscans = off を試してみると良いかも。
他に、更新を繰り返したせいでテーブルが太っているならば、
いったんCLUSTERなりVACUUM FULLなりをかけてみるとか。
>>380
何の効果も無いぞ > primary key
382:NAME IS NULL
08/03/27 23:54:21
すいません8.2.5でした
>>381
synchronize_seqscans = offは試してみます。と思ったけど8.3からなのかな。
ディスク読込のせいなのはほぼまちがい無いんだけど、shared_buffers割り当てを
増やしてもほとんど解決しなかった。もしかしてpostgresのキャッシュって、
queryでoidを読み出して、そのoidに対応するデータがキャッシュされてれば
キャッシュを見るだけなんじゃないのかなあと思ったんだけど、それで合ってる？
そう考えるとメモリはOSのディスクキャッシュに回した方がいいんじゃないかなとも
思ったんだけど。
vacuum fullは一回途中までかけたんだけど、20分くらい返ってこないのでやめた。
365d24hサービス中なので厳しい。vacuum analyzeは毎日かけるようにしたけど。
383:NAME IS NULL
08/03/28 01:14:47
365日24時間稼動しているなら、
まずは、テストできる予備機は必要ですね。
あとはサーバーの再起動とか。。。
365日24時間稼動で、それも不可能ならメンテ期間しか無理ということになりますね。
384:NAME IS NULL
08/03/28 08:18:06
>>382
ぜんぜんちゃう。oidなど使わない。
テーブルBの「数十MBに11秒」でもものすごく遅いので、環境がおかしい気がする。
100MBで10秒ならば、1GBで100秒かかるのも仕方ないでしょ？
あと、VACUUM FULLはアホのように遅いので、やるならCLUSTER。
385:NAME IS NULL
08/03/28 17:00:26
>>383
予備機はあるけどスペックが違うので大分遅いんです。
本番データは2GB強あるのでインポートするのも一苦労だし
>>384
そうなんだ。じゃあshared_buffersを全データが載るくらい増やせば
劇的に速くなると思うんだけど、postgresql8でも1GBくらいに性能ピークが
あるっていうのはなんでなの？
>100MBで10秒ならば、1GBで100秒かかるのも仕方ないでしょ？
select * じゃなくて select count(1)だから容量では単純比較できないと思う。
730万件が11秒ってなかなか優秀だと思ってたんだけどものすごく
遅いん？うーん。このスペックなら何秒くらいが妥当だと思う？
数十万件のテーブルから複雑な検索条件で検索かけるのとかは
軒並み1秒以内で終わるし、テスト環境(これもLinux)にくらべたら大分速いから、
環境のせいじゃないと思ってたんだけどなあ。
386:NAME IS NULL
08/03/28 21:02:35
見当外れなこと書いてたらスマソだが、
select count(*)にはできないのかな？
Postgreは知らんけどOracle8iではえらい差がでるよ。
count(1)ではDistinctのカウントになるので重複値の検出でえらい時間がかかる。
全件count(1)なんてやっちゃうと全件の中間テーブルを作ろうとしたりして。
387:NAME IS NULL
08/03/28 21:08:15
>>385 730万件が11秒
手元の環境だと、2秒未満。一昔前のデスクトップ機。
730万件だと最低250MB以上にはなるはずだし、なにか勘違いしてないか？
=# select pg_size_pretty(pg_relation_size('test'));
pg_size_pretty
----------------
252 MB
(1 row)
=# select count(1) FROM test;
count
---------
7300000
(1 row)
Time: 1876.152 ms
388:NAME IS NULL
08/03/28 21:09:22
>>386 残念だが見当外れ。
count(*)とcount(1)はまったく同じ動作です。
389:NAME IS NULL
08/03/28 22:28:14
>>388
あれ？同じ動作だったんだっけ？
昔なにかでcount(*)よりcount(1)の方が良いって記事をどこかで見た気がするんだが・・・。
勘違いか？
390:NAME IS NULL
08/03/28 22:51:29
>>387
ごめんそのpg_size_prettyを知らなくて、データのバイト数(1レコード36バイト)に
730万をかけて20～30ＭＢかなあと思ったんだけど、一桁間違えてた。
それやってみたら485Mだったよ。
360万件の方は3587MB
トータルのデータ領域が広すぎて遅くなってんのかな。
shared_buffersも1.5GBじゃ全然収まってないってことだね。
サービスインからしばらくVACUUMかけてなかったので、一回くらいは
VACUUM FULLかけたいんだけどね。
CLUSTERは調べたらテーブルロックしちゃうんだね。
夜中やってみるかな
391:NAME IS NULL
08/03/28 23:06:44
>>389
ユニークインデックスを持った列名の方がいいという記述なら見つけた
URLﾘﾝｸ(www.geocities.jp)
URLﾘﾝｸ(oracle.se-free.com)
でもって今日は325万件なんだけどやってみた
1. count(key) 76秒
2. count(1) 65秒
3. もう一回count(key) 63秒
4. selct count(*)が83秒
1の実行でキャッシュに載ったのかな。アクセスが割とある時間帯なので、
別要因で変動しているかも知れないけど
392:NAME IS NULL
08/03/28 23:45:45
>>389
他のDBMSだと効果があるのかもしれないけど、PostgreSQLに関しては関係ないのですよ。
あんまり鵜呑みにしないほうが良いのですよ。
393:NAME IS NULL
08/03/29 20:05:32
見当違いかもしれないが、HDD壊れてると遅くなったりしないか？
394:NAME IS NULL
08/03/30 09:29:37 7s6ei0XY
>>391
マシンの物理メモリ量と、DB全体のサイズの問題なのでは？メモリを
８Gくらいにすれば、あんましHDDにアクセスせずに済んで早くなると
思います
395:NAME IS NULL
08/03/30 09:56:52
>>391
オススメとしては、
・ＯＳをCENTOS、x86_64にしてメモリを８Ｇくらいは積む。
・shared_buffersは１．５ＧのままでＯＫ
・ＨＤＤを高速なものに交換する。
２４時間３６５日について
・VACUUM FULLはロック掛かると思いますので、運用中は難しいかと。
・バージョンによっては、VACUUMがらみでバグがあったと思うので、
結構不安。
8.2系統だと、8.2.7のリリースノートに
Repair potential deadlock between concurrent VACUUM FULL operations
on different system catalogs (Tom)
って載ってるし、バージョンアップをすすめます。
参考
URLﾘﾝｸ(www.postgresql.org)
・ＯＳ再起動も、１ヶ月に１回くらいしたほうが良いのでは？
396:NAME IS NULL
08/03/30 12:38:56
ソフトやハードで力技で解決するよりは、
・VACUUMをちゃんとする運用にかえる
・count()を多用しない設計にする
ほうが重要だと思うのだが・・・
397:NAME IS NULL
08/03/30 15:59:36
8.3にしてVACUUMの必要性をなるべく減らすという手もある。
んまぁ、>>396の言うとおり、時間がかかるからVACUUMはしないというのは
腐った運用としかいいようがないけどね。
398:NAME IS NULL
08/03/31 11:57:27 NLL0Kbcv
テーブルは以下のようになっています。
TableName(
id serial,
up_id integer,
data text,
updatetime timestamp
);
このとき、up_id毎にupdatetimeが最新のものをリストしたいのですが、
どのようなSQL文を書いたらいいのでしょうか？
up_id 指定で1件取り出すだけなら以下のSQL文でいいのはわかるのですが
SELECT * FROM TableName WHERE up_id=? ORDER BY updatetime DESC LIMIT 1;
399:391
08/03/31 12:10:22
みんなありがじゅー。
VACUUM ANALYZEは毎日やってる。最初AUTOにしてたんだけど、
毎日10万レコードくらい追加/削除するのでデイリーでやるようにした。
count()するのは、一覧表示の時にまず全件カウントしてページング処理を
したいから避けられない。
HDDが遅いのはアレだと思ってたので、次のシステムではRAID0+1を提案した。
で、>>395の
・メモリを８Ｇくらいは積む。
・shared_buffersは１．５ＧのままでＯＫ
っていうのは、残りの6GBはOSのディスクキャッシュにおまかせするってこと？
400:NAME IS NULL
08/03/31 12:29:58
>>398
idとup_idの関係がイマイチ分からんのでkwsk
401:NAME IS NULL
08/03/31 12:43:44
>>398
てきとーに、
select up_id, max(updatetime) where tablename
gourp by up_id
と書いてみるテスト
402:NAME IS NULL
08/03/31 12:44:24
あ、whereじゃなくて、fromだorz
403:NAME IS NULL
08/03/31 12:44:57
あ、gourpじゃなくて、groupだ。
てきとーすぎるorz
404:398
08/03/31 13:08:16
ありがとうございます
>>401
idは主キーで、特に今回は使わないです（すみません）
up_idは他のテーブルと結合するためのidです（他のテーブルの主キーつまり一対多のテーブル）。
up_idはユニークではないので、up_id毎に複数レコードが存在します。
>>401
そのSQLでup_idとupdatetimeのリストは作れるのですが、
dataのカラムが取れないので、困ってしまうのです。
他のDBだと（本当は文法違反であるものの）、
取得カラムにdataを追加するだけで欲しいものが取得できたりするのですが、
PostgreSQLだとgroup byにもdataを追加しなければいけないので、
dataまで同一じゃないとうまくグループ化されません。
405:NAME IS NULL
08/03/31 13:23:51
>>404
up_id別の最新updatetimeを取得できるなら、
そのSQL(をVIEWにするなりして)と
元テーブルのJOINを取ればいいだろ。
406:401
08/03/31 13:37:12
まぁ、そういうことだな。
407:NAME IS NULL
08/03/31 13:59:28
>>404
何に困ってるんだ？
select tablename.*
from tablename
join (
select up_id,max(updatetime) as updatetime from tablename group by up_id
) tmp using(up_id,updatetime);
408:398
08/03/31 14:03:41
>>401,405
ありがとうございます。
ということは、自分のテーブルに自分のテーブルを
up_idとupdatetimeで結合する感じですよね。
なんか、こう珍しくなさそうな検索条件なので、
もっと単純な解決方法があるのかと思いまして。
409:398
08/03/31 14:10:55
>>407
ありがとうございます（すれ違いました）。
>>408の通りですが、usingという書き方は知りませんでした。
勉強不足ですね…。
410:NAME IS NULL
08/03/31 15:14:44
>>399
全件カウントなら・・・別テーブルでカウントしておいた方がよくないか・・・・？
引いてくる条件のバリエーションが多いと結局面倒になるだけだけど。
411:NAME IS NULL
08/03/31 15:24:33 cYc2EXId
PostgreSQL初心者です。
idを主キーにして、データはidの昇順に登録しておきました。
WindowsなのでpgAdminIIIのデータビューで見るとidの昇順にデータが見えます。
一方
SELECT id from table
とするとid昇順になっていないところがありました。
order byを使えばいいことは分かったので問題はないのですが、こういうことは十分起こりうることなのでしょうか？
412:NAME IS NULL
08/03/31 15:28:25
>>411
PostgreSQLに限らず一般的にそう。
413:NAME IS NULL
08/03/31 15:28:39
>>411
レコードの順序は保証されない。順序が重要ならば必ず ORDER BY すべし。
414:411
08/03/31 15:35:57
>>412,>>413
わかりました。どうもありがとうございます。
415:NAME IS NULL
08/03/31 16:05:11
>>410
結局はよく使う検索条件だけに絞って一瞬で検索できるようにしたんだけど、
それとは別に勉強のために数GBのデータを早くする方法無いのかなと
思ってさ。Oracleなら全部キャッシュにいれてしまえばいいんだろうけど、
PostgreSQLはキャッシュサイズ800MBにピークがあるって事だったので、
どうしたらいいかなと。
元々は操作する人が任意の検索条件を指定できるようになっていたので
レコード数を別テーブルで管理は無理だった。
416:NAME IS NULL
08/03/31 21:03:41
>>415
PostgresとOSでキャッシュ管理がダブるので、中途半端にメモリの半分とかしないほうがいい。
少なめにしておくか、むしろ限界まで割り当てるか、どっちか。
417:NAME IS NULL
08/03/31 22:35:44
pbbenchとか使って、テストをして決めればいいと思うが．．．
418:NAME IS NULL
08/03/31 23:35:10
>>399
>っていうのは、残りの6GBはOSのディスクキャッシュにおまかせするってこと？
そのとおりです。ＨＤＤから読むから遅いのであって、メモリからなら
ＯＳキャッシュからでも、postgreSQLのメモリからでも大差ないと思います。
419:NAME IS NULL
08/04/01 07:35:04
>>417
テストをして決めるときの、パラメータ値の探索範囲の話をしているんだと思うよ。
420:NAME IS NULL
08/04/02 09:25:18
4GBのテーブルの全件カウントを速くしたいのでpgbenchは意味無いんです。
ベンチテストという意味ではそのselect count()の結果自体がテスト
なんだけど、本番稼働中にDB止めながらパラメータ変えつつ
テストするわけにもいかないし、とりあえずメモリ増設と設定パラメータ変更
(バッファ1.5GBとか)はやってみたので、みなさんの知恵を借りに来ました、
という感じです。
421:NAME IS NULL
08/04/02 09:35:36
もしかして「キャッシュサイズ800MBにピーク」っていうのも
テーブルサイズとか無視した単なる指標に過ぎなくて
環境によっては大きく変わるのかなあ。
例えば今回の場合だったら4GBキャッシュに割り当てた方がいいとか
参考にしたのはここ
URLﾘﾝｸ(itpro.nikkeibp.co.jp)
422:NAME IS NULL
08/04/02 10:20:27
>>420
全部SSDにつっこめ。
423:NAME IS NULL
08/04/02 10:50:22
ネタにマジレスするのはなんだけど
業務用のシリコンディスクっつーのもあるよね
URLﾘﾝｸ(www.newtech.co.jp)
16GBで570万て
424:NAME IS NULL
08/04/02 11:51:17
>>421
全件カウントじゃ4GBがテーブル全部がキャッシュに乗っからないと劇的な変化はなさそうだし、
shared_buffersを4GB強にして試してみるのは悪くないと思う。
>>422
ランダムアクセス性能が非常に重要になるので、ランダムアクセスでは速いSSDは確かに
面白そうではあるけど、あっという間に寿命がくる罠…
425:NAME IS NULL
08/04/02 15:03:01
pgperlのソースを探してるのですが
フォーラムにも見当たりません。。
どなたかご存知ないでしょうか？
426:NAME IS NULL
08/04/02 18:09:27
4GBのテーブルってデータ保存期間が14日間だったんだけど、
30日に延長されたよ。今後はこのテーブルだけで8GBになる。
ちなみにデータ領域をdfでみると今10GBくらい。
427:NAME IS NULL
08/04/03 11:29:13
>>426
だからSSDに配置汁。
428:NAME IS NULL
08/04/03 14:30:26
トリガで常に件数どっかに置いておく、とか
429:NAME IS NULL
08/04/03 22:18:13
SSD買う金があったら、その金でメモリ買ったほうが良くね？
430:NAME IS NULL
08/04/04 13:53:48
>>429
でも、8GB以上メモリを積めるマシンはそれなりの価格になるぞ。
431:NAME IS NULL
08/04/04 15:29:07
SSD載せれるサーバもそれなりな価格になりそうだが^^;
最近とったメモリ大目の見積もりだと
クアッドコアのSMPでメモリ16GでSASの73GB(15000回転)でRAID10で
(保守とかその他オプションつけて)だいたい100万～150万くらいだったかな
432:NAME IS NULL
08/04/04 18:59:30
結局のところ、426がどれだけ予算をかけられるのかによるからなぁ。
433:NAME IS NULL
08/04/08 17:59:30 JAlLqxhR
telnetでつないでpsqlでselect文だして、ちょっとした確認をすることが結構あります。
で、結果をマウスで選択、コピーして、使ったりしてるのですが、結果が多い時コピペが大変です。
そんな時、コマンドの>みたいにテキストに書き出すことはできないでしょうか？
434:NAME IS NULL
08/04/08 18:19:36
例えばtest.sqlというファイルにSQLを書いておいて
psql -U postgres test < test.sql > test.result
こうすればtest.resultに結果が書かれると思うんだけど
これじゃだめ？
435:NAME IS NULL
08/04/08 18:24:54
>>433
psql を -o オプション付きで起動するか
psql で \g ファイル名とするか。
こういう時は psql --help するか
psql で \? すれば大概分かるかと。
436:NAME IS NULL
08/04/08 18:29:32
\o
437:NAME IS NULL
08/04/09 03:58:11
teratermかpoderosaで繋いでログファイル指定しとく、とか
Windowsじゃなかったらアレか
438:433
08/04/09 13:44:15 vjpBiAeV
>>434
>>435
>>436
>>437
レスありがとうございます。
おっしゃる通り、ヘルプの存在をすっかり忘れてました。
失礼。
ログっていうのも手軽でいいですね。
439:NAME IS NULL
08/04/10 00:56:47
手がかかる方法としてはsshでつないでport forwardしてPgAdminつかうという手も
440:NAME IS NULL
08/04/16 12:46:24 xMO3l+R5
80万レコードほどあるテーブルの集計をとっているのですが
かなりコストがかかってしまい、15分ほどかかってしまっています。
単純なテーブル＋単純なクエリなんですが、どこか改善できるポイントは
あるのでしょうか？
count_table
id｜user_id｜date
select user_id, count(*) AS Num from count_table
where date between '2008-01-01 00:00:00' and '2008-02-01 00:00:00'
group by user_id
order by Num desc
441:NAME IS NULL
08/04/16 13:01:41
>>440
date列にindexを張るとか?
442:NAME IS NULL
08/04/16 19:52:51
久しぶりに来たら進んでる・・・
>>306
そんなのSQLサーバ側で変わったら使えないと思ったんだが、
見た目文字列リテラルなのか、日付リテラルなのかは、コンテキスト次第だ
った・・
>>440
やっぱり、dateにINDEX張ってないと遅くなるね。って441と同じ意見であった。
データが膨らむからその時点でディスク容量が増えているか・・・。
INSERT系なら、
URLﾘﾝｸ(www.postgresql.jp)
で、幾分か速くなった。
443:442
08/04/16 19:55:44
補足： INSERT構文を最適化するのはこっちだった。。。
URLﾘﾝｸ(www.postgresql.jp)
444:NAME IS NULL
08/04/16 22:27:02
>>442
他には月ごとにパーティショニングしてみるとか。
445:NAME IS NULL
08/04/17 16:28:11
引っかき回すようで申し訳ないんですが
>>369-370
のような場合って、
on aa.code01=cc.code01 and cc.code002 =6
を
on aa.code01=cc.code01 and bb.code002 = cc.code002
という感じに変えて、最後にWHERE句でbb.code002 = 6
とやるのよりはやいの？
状況によりけりとは思うんだけど、どちらのほうがより安定しているのかな、とおもって。
446:440
08/04/17 18:38:38
>>441-442
アドバイスありがとうございます。
date にインデックスをはったら大分早くなりました。
というかめちゃくちゃ早くなりました。
447:NAME IS NULL
08/04/17 22:19:51
失礼します。
pg_dumpallから生成されたファイルから、ある一つのDBだけリストアしたい
場合、コマンドとしてどのように実行すればよいでしょうか？
テンパってます。。。
448:NAME IS NULL
08/04/17 23:03:32
-d オプションで指定してもだめでしたっけ？
最悪、ダンプファイルを開いて必要なとこだけ残してリストアする？
あまりお力になれず申し訳ない。
449:NAME IS NULL
08/04/17 23:17:08
>>448
ありがとう、試してみます。
450:448
08/04/17 23:29:49
いい案が思い浮かばないままですが、
postmasterのインスタンスをもう一個起動して、そっちに放り込んだあと
必要なDBだけのダンプファイルを作成するのはどうでしょう？
ダンプファイルを直接編集するよりは楽かもしれません。。。
451:NAME IS NULL
08/04/17 23:38:56
>>450
ありがとう、私もそれしか思い浮かびませんでした。
いったん別マシンのpostgresにフルで戻した後、必要なdbだけをdb_dumpして、
それを戻すようにしました。手間ですが、何とかなりました。
452:NAME IS NULL
08/04/18 00:15:32
>>451
何とかなったようでよかったです。
おつかれさま。
453:NAME IS NULL
08/04/18 10:11:21 TLsFyIeR
テーブルの主キーを変えるのってできませんか？
主キーを削除できればできそうですけど・・
お願いします。
454:NAME IS NULL
08/04/18 10:56:55
616 名前：NAME IS NULL[sage] 投稿日：2008/04/18(金) 03:12:10 ID:???
MySQL、新機能追加は有償版の「MySQL Enterprise」だけを対象に
URLﾘﾝｸ(www.technobahn.com)
Linuxを代表するオープンソースベースのリレーショナルデータベース管理システムのMySQL
が近くソースコードの公開を停止する方向で準備を進めていることが16日、米カリフォル
ニア州サンタクララで開催中のMySQLコンファレンスの席上で明らかとなった。
455:NAME IS NULL
08/04/18 11:06:32
>>454
スレ違い
MySQL vs PostgreSQL Part2
ｽﾚﾘﾝｸ(db板)
こちらへどうぞ
456:NAME IS NULL
08/04/18 11:14:49
嫌気がさした人はこっちに来そうですねw
457:NAME IS NULL
08/04/18 22:09:27
ライセンスの問題からしてもこっちに移住する人は多いだろう
458:NAME IS NULL
08/04/18 22:56:43
呼び込み運動をさりげなくしましょうw
『なんか大変ですね。よかったらこっちも試してみませんか。』
459:NAME IS NULL
08/04/18 23:01:55
PostgreSQLの方がシンプルで好きなんだが・・・。
XOOPSが動けば、PostgreSQLに乗り換えたい。
460:NAME IS NULL
08/04/18 23:13:45
>>457
つーか、ライセンスのこと気にする人は最初からこっちにいるでしょ。
>>459
へ？ 動かないの？ XOOPS コミュニティってそんな低レベルな世界だったんだ。
461:NAME IS NULL
08/04/18 23:19:07
>460
PostgreSQLで動かしたことがあるのか？
推奨環境には、MySQLしかないようだが。
462:NAME IS NULL
08/04/18 23:25:42
>>461
何言ってるの？
XOOPS って MySQL に依存しまくりのコーディングしてるの？
なんという前時代的な。
にしたって、それくらい改造出来るだろって話。
実際やってる例もあるみたいだし。
463:NAME IS NULL
08/04/18 23:29:47
既知外ってなんでイキナリ喧嘩腰なんだろう。
464:NAME IS NULL
08/04/19 01:18:47
共有ロック/排他ロックってどうやんの?
465:NAME IS NULL
08/04/19 06:28:53
ロックなぞわざわざ「やる」ものではない。
466:NAME IS NULL
08/04/19 09:32:09
>>464
行ロックなら SELECT ... FOR SHARE/UPDATE
表ロックなら LOCK tbl IN SHARE/EXCLUSIVE MODE
>>465
MVCCあるからあんまり必要ないのは同意。
でも SELECT FOR UPDATE くらいは使わないといけないこともよくある。
467:NAME IS NULL
08/04/19 12:14:16
>>466
というより、SELECT FOR UPDATEはOracleやPostgresのように直接RRレベルを
サポートしないDBMSでRRが必要な場合に使うものと考えた方が良い。
下手にロックとか考えると、同期制御とごっちゃに理解して後々困ることになるからね。
OracleのRead Commitedは間違ってる、とかw
468:NAME IS NULL
08/04/19 15:14:52
RRって何？
469:NAME IS NULL
08/04/19 15:17:10
たぶん REPEATABLE READ
470:NAME IS NULL
08/04/20 07:02:44
>>425
>pgperlを使う方式(旧称 pgsql_perl5)
>1995年のpostgres95の時代からPostgreSQL7.2.3まで PostgreSQLのソースの含まれていた Perlインタフェースで，
>libpqに似せたシンプルなインタフェースデザインとなっています．
>libpqライブラリが必要です．
471:NAME IS NULL
08/04/22 13:52:15 qNcJmW6i
FETCHコマンドで
FETCH [ direction { FROM | IN } ] cursorname
とありますがFROMとINの違いってなんですか？
「INを使用するオプションはPostgreSQLの拡張です。 」としか書いてないし
実際に使ってみても同じ結果しか得られないし・・・
472:NAME IS NULL
08/04/22 14:18:56
>>471
FROMが標準語で、INはPostgreSQLの方言ってだけかと
473:NAME IS NULL
08/04/22 17:12:16
webページの言語をutf-8で作成してる場合は
PostgreSQLでもutf-8を使ったほうがいいんですか？
utf-8にしたことにより発生するエラーというものも有るんでしょうか？
474:NAME IS NULL
08/04/22 23:14:06
今は外字も入るし、utf-8にしてもデメリットないんじゃないかな。
基本的にはドライバが吸収するから、webサイトとDBの言語は
別々に考えていいよ。そして、どっちもutf-8にすべき。
諸事情でwebをSJISにする場合でもDBはutf-8で
いいんじゃないかな
475:NAME IS NULL
08/04/23 00:32:56
>>474
ありがとうございます！
統一できると、ローカル作業で言語切替の事を気にしなくていいのでよかったです
476:NAME IS NULL
08/04/23 01:40:14
DBのエンコードがなんだろうがクライアントと関係ないんじゃ？
477:NAME IS NULL
08/04/23 10:52:33
そうだな
まあ日本語以外のマルチバイト文字も考えなきゃならんときはUTF-8はありがたい
478:NAME IS NULL
08/04/23 11:00:32
思いつきだけど UTF-8 とそれ以外じゃソートしたときに結果違わね?
479:NAME IS NULL
08/04/23 11:17:38
ひらがなさえ同じならいいけどな
個別にソート用に表持たせることもあるかな
480:NAME IS NULL
08/04/23 19:00:39
将来、PostgreSQL のバージョンアップで、
int 型を検索するときに
c = 1 ではなく、' で括った
c = '1' がエラーになる可能性はありますか？
PostgreSQL 8.3 にしたところ、text 型のカラムを検索する場合
where foo = 1;
ではエラーになりました。
int 型は括っても括らなくても使えているのですが、
SQL の標準とか、将来的にどうなのかな、と疑問に思って。
ご存じの方、よろしくお願いいたします。
481:NAME IS NULL
08/04/23 19:28:27
>>480
↓が参考になるかも？
URLﾘﾝｸ(www.postgresql.jp)
個人的には
bigintとintとかtextとcharみたいにサイズが違う同じ型ならDBにまかせるけど
intとtextみたいに別物は明示的にキャストしてあげたほうがいいと思う。
482:NAME IS NULL
08/04/23 19:45:46
>>480
その書き方で大丈夫。
クォートされていると型の決定は文脈依存で、適当に変換される。
ちなみに、若干似てるけど、
Java で PreparedStatement#setInt() の代わりに setString() するのはダメよ。
483:480
08/04/23 21:26:43 mDsa5IzI
>>481-482
ありがとうございます。
create table test(
text text,
int int
);
というテーブルにおいて、
8.2 までは select * from test where text = 1 は問題なく、
8.3 からエラーになるようになりました。
int 型のカラムは 8.3 でも
select * from test where int = 1; -- (1)
select * from test where int = '1'; -- (2)
のどちらも問題ありませんが、
8.4 や 9.0 になって (2) がエラーになることはありますか。
484:NAME IS NULL
08/04/23 23:01:02
>>483
エラーにはならない。安心して (2) を使って大丈夫。
485:NAME IS NULL
08/04/24 05:05:18
>>8.2 までは select * from test where text = 1 は問題なく、
これが、問題なく使えていたのを初めて知った。
気になったので確認したら、数字だと通ってたんだな。
486:NAME IS NULL
08/04/24 08:04:22
>>485
正確には、型の不一致があったとき、両辺を勝手に text にキャストして比較していた。
where timestamp = 数字 とかも通っちゃってた。
いろいろと危険なので、変更されてよかったと思う。
487:NAME IS NULL
08/04/24 13:49:37 LtHVlG1o
>>484
数字は 1 でも '1' でも危険はないから
今後とも大丈夫、ということなのでしょうか？
488:NAME IS NULL
08/04/24 22:01:50
将来のことなので誰もなんとも言えないと思うけど、
可能性の話をするならエラーになる可能性はあるんじゃない？
もしくはエラーにはならなくてもtextで検索しにいくので
条件にマッチしない可能性とか
489:487
08/04/24 22:40:07 v9wdKkgn
確かに将来は分かりませんよね。
ありがとうございます。
SQL:1999 や SQL:2003 などの
SQL 標準的にはどうかご存知の方は
いらっしゃいませんか。
490:NAME IS NULL
08/04/24 23:52:03
それを知ってどうするつもりなんだろ。
ちゃんと型あわせるようにしておけば大丈夫なだけじゃん。
どんな言語でも「やっぱ暗黙のキャストやーめた」となる可能性は
0じゃないよ
491:NAME IS NULL
08/04/25 00:02:58
実行計画とると、解析後のSQL見られるけど
内部的にはかなりキャストしてるのが分かる
たいしたことは無いんだろうけど、表の結合条件でキャストが多いと
性能が落ちるかもしれんね
492:NAME IS NULL
08/04/25 07:50:04
>>491
リテラルなら、SQLパースのあと1回だけキャストされて、処理中はすでにキャスト後になっている。
性能を心配する必要は無いよ。
そもそも「数字」といったって、int2/4/8, float4/8, numeric と
いろいろあるんだから、'' で括らなくても何かしらの変換が挿入される可能性はある。
493:NAME IS NULL
08/05/04 14:06:49
8.3系で運用してる人いる？
過去バージョンと比較して使用感はどんなもんでしょうか
494:NAME IS NULL
08/05/07 10:21:33 AHN5+Ycn
ユーザ会からpostgresをダウンロードしようとすると
Internet Explorer ではこのページは表示できません
になってしまいますが、サーバがこけてますか
495:NAME IS NULL
08/05/07 10:40:52
他のミラーから取ってきたら？
fURLﾘﾝｸ(ring.ocn.ad.jp)
496:NAME IS NULL
08/05/07 11:25:58
>>495
いけました。ありがとう。
497:NAME IS NULL
08/05/07 14:23:09
>>493
8.2でパフォーマンスが凄く悪かったサーバが見違えるようによくなった。
他のサーバもそこそこ改善してるし今のところ人為的なのを除きトラブルはない。
無茶といわれてた大量UPDATEに耐えられるかな？
できるならそういうコードも書きたいのだが・・・
498:NAME IS NULL
08/05/07 14:46:10
>>497
是非試していただければと思います。
499:NAME IS NULL
08/05/07 17:10:05
Windows版のPostgreSQLを自動インストールする方法はありますか？
Windowsの他のプログラムから、インストーラを起動して自動的にイン
ストールできるようだといいのですが。
500:NAME IS NULL
08/05/07 17:38:32
>>499
URLﾘﾝｸ(pginstaller.projects.postgresql.org)
501:NAME IS NULL
08/05/07 18:30:16
>>497
具体的に8.2ではいけなかった処理ってなんとなく尻尾つかめてたりするのでしょうか・・？
502:NAME IS NULL
08/05/07 21:34:20 Lqr18+2G
postgresql8.2のSQLで質問があります。
lent /*貸し出し管理*/
+id
+bookid 書籍ID
+lentdate 貸し出し日
book /*書籍*/
+id
+name 書籍名
+fromdate 有効期間（開始日）
+todate 有効期間（終了日）
select lent.lentdate, lent.bookid, min(book.name), count(*) as lent_count
from (
select lent.id, lent.bookid, ... from lent, ... where 条件１
union
select lent.id, lent.bookid, ... from lent. ... where 条件２
) lent
left outer join book on lent.bookid = book.id and lent.lentdate >= book.fromdate and lent.lentdate <= book.todate
group by lent.lentdate, lent.bookid
本当は、グルーピングしたlent.lentdateの最小値の日付でbook.nameを取得したいのですがやり方がわかりません。
仕方なく、下記のようにbook.fromdate || book.nameで取得して、フェッチ後にbook.nameだけ切り出そうと考えました。
select lent.lentdate, lent.bookid, min(book.fromdate || book.name), count(*) as lent_count
from (
select lent.id, lent.bookid, ... from lent, ... where 条件１
union
select lent.id, lent.bookid, ... from lent. ... where 条件２
) lent
left outer join book on lent.bookid = book.id and lent.lentdate >= book.fromdate and lent.lentdate <= book.todate
group by lent.lentdate, lent.bookid
しかしmin(book.fromdate || book.name)でグルーピング単位の最小値の値が返ってきません。ちょっと考えられない結果です。
試しにインラインビューの中のunionをやめてみたら、期待した値が返ってきました。
select lent.lentdate, lent.bookid, min(book.fromdate || book.name), count(*) as lent_count
from (
select lent.id, lent.bookid, ... from lent, ... where 条件１
) lent
left outer join book on lent.bookid = book.id and lent.lentdate >= book.fromdate and lent.lentdate <= book.todate
group by lent.lentdate, lent.bookid
unionがいけないのか特定したくて、うまくいった条件１の方のSQLをunionで２本繋いでみるとNG(minの結果が変）になりました。
select lent.lentdate, lent.bookid, min(book.fromdate || book.name), count(*) as lent_count
from (
select lent.id, lent.bookid, ... from lent, ... where 条件１
union
select lent.id, lent.bookid, ... from lent, ... where 条件１
) lent
left outer join book on lent.bookid = book.id and lent.lentdate >= book.fromdate and lent.lentdate <= book.todate
group by lent.lentdate, lent.bookid
・インラインビューの実体がunionで繋がれた複数SQLであること
・そのインラインビューでグルーピングすること
・そのインラインビューに外部結合した値をmin()関数で取得すること
この辺でpostgresqlの制限に引っかかっているようなことがありませんでしょうか？
503:499
08/05/07 23:29:35
>>500さま
英語の方に解説があったのですね。
もう少しぐぐり方を工夫する事を覚えようと思います。
ありがとうございました。
504:NAME IS NULL
08/05/08 09:31:00
>>502
min(book.fromdate || book.name) の部分で、日付が yyyymmdd に変換されると
期待してそうだけど、ちゃんと動作している？
単に、group by に book.name を含めるべきなのでは？
> minの結果が変
「変」とか「おかしい」とか言われても、助けられない。
具体的にどうなったのか、説明して欲しい。
505:NAME IS NULL
08/05/08 12:59:40
>>504
ﾚｽどうもです。book.fromdatはchar(8)でYYYYMMDDの文字列が入っています。
>単に、group by に book.name を含めるべきなのでは？
book.nameは集約したグループの中でmin()を使って取得したいのでgroup byには含めないと思います。
「minの結果が変＝グルーピング単位の最小値の値が返ってきません。」です。すみません。
「book.fromdate || book.name」の値が下記のような場合、
20080501書籍01
20080502書籍02
min()で"20080501書籍01"が取得されることを期待しましたが、"20080502書籍02"が返ってきています。
ちなみにmax()にすると"20080502書籍02"が返ってきます。(；・∀・)
インラインビューの中身を切り出して単独で実行してみると
select lent.id, lent.bookid, ... from lent, ... where 条件１
union
select lent.id, lent.bookid, ... from lent, ... where 条件１
必要なデータは取得されていることを確認しました。
group byを外して、全件そのまま出力させてみたら
20080501書籍01
20080502書籍02
の両方のレコードが取得されていることも確認できました。
あとはmin()で"20080501書籍01"が返ってくれさえすればOKなのですが。。
506:NAME IS NULL
08/05/08 13:15:26
>>505
unionとか関係無しに単純に文字列のminとmaxをやったらどうなるの？
encodingは？
507:NAME IS NULL
08/05/08 13:55:21
>>506
min(book.fromdate)とmin(book.name)を追加して実行すると
select lent.lentdate, lent.bookid, min(book.fromdate), min(book.name), min(book.fromdate || book.name), count(*) as lent_count
from (
select lent.id, lent.bookid, ... from lent, ... where 条件１
union
select lent.id, lent.bookid, ... from lent. ... where 条件２
) lent
left outer join book on lent.bookid = book.id and lent.lentdate >= book.fromdate and lent.lentdate <= book.todate
group by lent.lentdate, lent.bookid
min(book.fromdate) min(book.name) min(book.fromdate || book.name)
20080501 書籍02 20080502書籍02
というおかしな結果になります。min(book.fromdate)だけは正しい結果が返ってきてますね。
DBのエンコーディングはEUC_JPです。
508:NAME IS NULL
08/05/08 13:57:57
すみません。>>505に誤りがありました。
誤：ちなみにmax()にすると"20080502書籍02"が返ってきます。
正：ちなみにmax()にすると"20080501書籍01"が返ってきます。
509:507
08/05/08 14:03:26
進展がありました。book.nameの値から全角文字を除去してみたところ
書籍01→01
書籍02→02
min(book.fromdate) min(book.name) min(book.fromdate || book.name)
20080501 01 2008050101
正しく集計されました。
もしかしてmin()関数の引数に全角文字が含まれるとおかしな動きをする、とかありますか？
510:NAME IS NULL
08/05/08 14:06:09
book.fromdate || book.name
20080501書籍01
20080502書籍02
min(book.fromdate || book.name)
この場合、先頭の８文字で大小比較が完了するので、全角文字までの評価は行われないだろうと期待していましたが、
>>509の結果を見ると、そうでもなさそうですね？
511:NAME IS NULL
08/05/08 14:53:27
>>509
どんな漢字が含まれるとmin()の動作に影響を与えるのか調べてみました。
「総合」が含まれていると×なようです。
どうも>>502の長いSQLは関係なかったようで、試しにbookテーブル単体で
select id, name from book
01, 総合20080501
01, 総合20080502
select id, min(name)
from book
group by id
01, 総合20080502 が返って来ました。ガーン。postgresqlのmin()関数が全角文字をサポートしていない？
>>502の代替手段がなくて困っています。
512:NAME IS NULL
08/05/08 15:02:03
DBのEncodingはどうなってるの？
513:NAME IS NULL
08/05/08 15:10:32
>>512
>>507
514:NAME IS NULL
08/05/08 15:27:32
--no-locale付けた？
515:NAME IS NULL
08/05/08 15:42:59
>>514
分かりません。Windows版なんですけど、付けたかどうかを確認する方法ってありますか？
でも、なんかビンゴっぽいですね。
516:NAME IS NULL
08/05/08 16:02:08
>>515
Windowsかあ。createdbはどうしたの？インストール時ならチェックする項目あったかなー。
SELECT name,setting FROM pg_settings WHERE name ~ '^lc';
とかどう？
517:NAME IS NULL
08/05/08 16:04:40
>>516
name setting
"lc_collate" "Japanese_Japan.932"
"lc_ctype" "Japanese_Japan.932"
"lc_messages" "Japanese_Japan.932"
"lc_monetary" "Japanese_Japan.932"
"lc_numeric" "Japanese_Japan.932"
"lc_time" "Japanese_Japan.932"
でした。ダメ？
518:NAME IS NULL
08/05/08 16:05:54
あとは、pg_controldata コマンドでもいい。
519:NAME IS NULL
08/05/08 16:06:32
>>517
noにすれば、そこは C になるはず。
noにしないとソート順狂うから、それだろうね。
520:NAME IS NULL
08/05/08 16:15:06
>>519
ありがとうございます！
ちなみにinitdbからやり直さないとダメなんですよね。キッツイなぁ。createdb毎に指定できないものか。。
521:NAME IS NULL
08/05/08 16:28:19
pg_dumpall ですぐだべ
522:NAME IS NULL
08/05/08 17:01:28
>>521
やってみます！
523:NAME IS NULL
08/05/08 17:24:44
ロカールの話で盛り上がっているようですが、
そもそもそれに依存しない抽出方法があるような気がします。
それぞれの本ごとに、一番古い貸し出し日および冊数を出したいのですよね？
524:NAME IS NULL
08/05/08 17:28:36
切れてしまいました。
以下のような頻出事例と似てるような気がします。
ｽﾚﾘﾝｸ(db板:35-36番)
525:NAME IS NULL
08/05/08 17:30:31
>>521
C:\>pg_dumpall -U postgres > pg_dumpall-20080508.dmp
postgresサービス停止
C:\>cd \postgres
C:\>mv data data-old
C:\>initdb --encoding=EUC_JP --no-locale -D C:\postgres\data
postgresサービス起動
C:\>psql -f pg_dumpall-20080508.dmp postgres
psql: could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "???" and accepting
TCP/IP connections on port 5432?
C:\>netstat -a | grep 5432
postgresサービスが立ち上がらなくなってしまった!?
526:NAME IS NULL
08/05/08 17:35:10
>>523
>それぞれの本ごとに、一番古い貸し出し日および冊数を出したいのですよね？
一番古い貸し出し日は抽出対象ではないのですが、
bookテーブルが世代管理されているので（同一idでfromdateとtodateで管理）
一番古い貸し出し日の時のbook.nameを世代を見て抽出したいのです。
527:NAME IS NULL
08/05/08 17:43:12
>>525
postgresql.conf を data-old から data にコピーすればいいと思う。
528:NAME IS NULL
08/05/08 17:43:40
>>525
そういうときは、ログとpostgresql.confをよく嫁
529:NAME IS NULL
08/05/08 18:58:28
>>527 >>528
お騒がせしました。パニクって再インストールしてしまいましたｗ そしてその再インストールでも小一時間ハマってました。
ロケールをCにして再度SQLを実行したら期待通りの結果が返ってきました！！アドバイスをくれた皆さん本当にありがとうございました。
530:NAME IS NULL
08/05/08 22:31:42
合わない locale と encoding を使ってしまうのは、頻出ミスだった。
v8.3 からは適合しない組み合わせはエラーが出力されるようになったので、
うっかりミスも減らせるはず。
531:NAME IS NULL
08/05/08 23:12:35
ついに対策されたんだｗ
石井さん乙？
532:NAME IS NULL
08/05/08 23:25:38
8.3リリースノートより：
> サーバのロケール設定と矛盾するデータベース符号化方式を不許可としました。(Tom)
533:NAME IS NULL
08/05/09 00:38:51
テーブルの中の特定カラムを、AESとか、desとかで、暗号化する事って出来るんでしょうか？
プログラムで行えば出来るのは解っているのですが、
一部一致検索で、ひっかからなくなって困ってしまいました。
そこで、PostgreSQL自体で暗号化する方法があれば、部分一致検索も
正しく動作するのか？と考えました。
だれか、こんな事をやった事ないですか？
534:NAME IS NULL
08/05/09 00:45:42
これ？
URLﾘﾝｸ(osb.sraoss.co.jp)
特定の列に関する暗号化
/contrib関数ライブラリ pgcryptoにより、あるフィールドを暗号化して保存することができます。データの一部が極秘データであるような場合に有用です。クライアントが提供した複合化用のキーで、サーバ上のデータを複合化し、クライアントに返されます。
複合化されたデータと復号化用のキーは、復号処理中およびクライアントサーバ間の通信中サーバ上に存在します。このため、データベースサーバへの全てのアクセス権限を持つユーザ（例えばシステム管理者）によって、データとキーが盗まれる瞬間があります。
535:NAME IS NULL
08/05/09 01:53:57
>>532
トム。。。
536:NAME IS NULL
08/05/09 07:54:44
>>533
部分一致検索したいのなら、ファイルシステムレベルで暗号化しないとだめ。
ただ、SELECT * で普通に元の値を取得できてしまうのが難点かも。
pgcryptoのような方式で、部分一致検索できるようにしてしまうと、
暗号化の強度としてはゼロに等しい。難読化くらいの価値しかない。
大小の順序を保つ必要があるので、二分検索で簡単に特定されちゃう。
537:NAME IS NULL
08/05/09 08:42:47
>>536>>534
こういう話出す人はもう少し暗号の勉強をして、表現力を養ったほうがイイよね。
権限を持つ者がデータを持っていけるのは、暗号とは相関性が無いんだよ。
無駄に暗号強度とか持ってくるから、「説得力があると騙される無知な人も居る」と覚えておこう。
538:NAME IS NULL
08/05/09 16:32:04
create table members (
id integer primary key auto_increment,
name varchar(256) not null,
birth date,
gender char(1) not null
)
というテーブルがあって、birth と gender でグループ分けしたいとき、どうしたらいいですか。
select birth, gender from members group by birth, gender だと id が分からず、
select id, birth, gender from members group by birth, gender だと エラーだし、
どうしたらいいんでしょう?
539:NAME IS NULL
08/05/09 17:20:05
何がしたいのかによる。
birthとgenderでグループ化したらそのグループに複数のidが含まれる可能性があるから
そのまま出すのは無理。
最大値 max(id) とか、一つに特定できるのならばそれが使えるけど。
540:NAME IS NULL
08/05/09 18:00:55
>>539
ですよねー
やりたいのは、まさにグルーピングであり、複数のidをグループごとにとりだしたいんです。
でもSQLではなんかうまくできなくて...すごく不自由しています。
こんなとき、みなさんどうしてますか。やっぱりPL/SQLとか使わないといけないんですかね。
541:NAME IS NULL
08/05/09 18:13:23
複数idが必要ならグループにする理由がないのでは？
具体的に、どんなデータからどんなデータを抽出したいのか書いてくれればわかるかもしれん
542:NAME IS NULL
08/05/09 18:51:20
select id, birth, gender from members order by birth, gender
とか？
543:NAME IS NULL
08/05/09 19:17:14
元のデータ
id | name | birth | gender
------------------------------
1 | test1 | 2008-05-09 | M
2 | test2 | 2008-05-10 | F
抽出結果
?? | ?? | ??
--------------
x | xxxx | xx
x | xxxx | xx
こんな感じに
544:NAME IS NULL
08/05/09 20:39:07
>>537
で、あなたはいったいなんの役に立っているの？
545:NAME IS NULL
08/05/10 02:51:14
>>543
抽出結果が伏せ字だらけ、、、はっ！これが暗号ってやつDESか？
546:NAME IS NULL
08/05/10 08:50:59
>>538
idを配列として取り出してみては?
URLﾘﾝｸ(www.postgresql.jp)
のarray_accumを参考に。
547:NAME IS NULL
08/05/10 13:49:28
>>537
>>534はドキュメントのコピペですよ。
どの経路をどういう目的で暗号化したいかによって全然変わってくると思う。
>>533の文面から察するに、ディスク上のデータを暗号化したいだけで、サーバ上のメモリとか通信路の上では暗号化されなくてもいいみたいだし。
548:NAME IS NULL
08/05/10 23:13:17
>>547
> >>534はドキュメントのコピペですよ。
それは見れば解る。
> どの経路をどういう目的で暗号化したいかによって全然変わってくると思う。
> >>533の文面から察するに、ディスク上のデータを暗号化したいだけで、
> サーバ上のメモリとか通信路の上では暗号化されなくてもいいみたいだし。
キミに知識があるのは理解したけど、
具体的な部分を引用で誤魔化しただけじゃ意味が無い。
>>533みたいな質問を恥ずかしくなくできる程度だと、
その引用が役立つとは思えない。
暗号の基礎を学ぶ方が先決と感じるけどね。
549:NAME IS NULL
08/05/10 23:41:57
>>547
部分一致検索が出来る方法を探していたので、
メモリー上や通信経路上での暗号は別問題って事で。
534や536のpgcryptoを試してみます。
550:NAME IS NULL
08/05/11 16:40:36 nKIrUfLq
postgresqlをlinuxにインストールしてみましたが、
jdbcがありませんでした。
jdbcのみインストールできるサイトはありませんか？
よろしくお願いいたします。
551:NAME IS NULL
08/05/11 18:12:16 hAgtRqsE
>>550
本家->downloadでDLのページいってみては？
552:NAME IS NULL
08/05/11 18:44:24
複数のサーバに分散させてたアクセスログテーブルを一サーバにまとめる
作業をしてるのだけど、IDカラムをPRIMARY KEYにするために
数字を重ならないように振り直す良い方法ない？
連番である必要はないのだけど、
UPDATE id = nextval('foobar_id_seq');
だとトランザクションが完了までにストレージの容量不足であぼ～ん。
データベースが500Gでストレージ容量が800Gな感じ。
行数はpgAdminの概算で10億のオーダー（実際にもたぶんそんなもん）。
Intel 8コア2Ghz、メモリ6Gで丸一日動かすぐらい以下で終わるとうれしい。
553:NAME IS NULL
08/05/11 19:33:49
>>548
暗号の基礎というのが想像つかないけど、公開鍵暗号とか、ハッシュ関数？AliceとかBobがいて、…みたいなことかな。
よかったらポインタとかキーワードください。
部分一致検索「できない」ところに暗号化する意味があるといえばあると思うんだけど、暗号化されたままのデータに対するプログラムによる処理、という研究領域もあったような。
まだ実用段階ではないはず。
554:NAME IS NULL
08/05/11 20:59:34
>>552
250GBぶん処理 → VACUUM → 残りの250GBを処理
で、750GBぎりぎりに収められるような気はするけど、効率的では無いね。
オリジナルの各サーバからデータをダンプすると思うのだけれど、
そのときについでにIDを再割り振りしてはいかがでしょ？
COPY (SELECT ID + <サーバごとのオフセット>, <ID以外の属性> FROM tbl) TO ...
みたいな。
555:NAME IS NULL
08/05/11 21:49:59
>>554
レスどうもです。
すでにpg_dumpでテーブルを圧縮ダンプしてオリジナルを消してしまって
いるのですが、書き戻しつつまったりやってみます。
スタンドアロンで起動してチョメチョメするとUPDATEがトランザクションを
作らずできるとかあると良かったのですが。
556:NAME IS NULL
08/05/11 22:49:47 nKIrUfLq
>>551
このやり方だとすべてをインストールしなければならないですよね？
jarファイルだけほしいんですが・・
557:NAME IS NULL
08/05/11 23:05:40
>>556
つ 本家 [URLﾘﾝｸ(jdbc.postgresql.org)
558:NAME IS NULL
08/05/12 00:19:43
新しい列を追加してサーバID＋IDカラムを主キーにすれば？
559:NAME IS NULL
08/05/12 02:57:40
>>548
>>534はpostgres標準でカラムごとに暗号化する方法が用意されてるよ
というポインタを出しただけだろう。なんでそんな喧嘩腰なん？
560:NAME IS NULL
08/05/12 13:03:06
>>543
せめてどの列に何のデータが欲しいかくらい書いて下さい。
561:NAME IS NULL
08/05/12 14:25:10
>>560
>>543は質問者への例だよ。
質問者が>>542みたいなこと書くからさ。
562:NAME IS NULL
08/05/12 16:11:58
>>561
ごめんよ('A`)
563:NAME IS NULL
08/05/12 19:38:57
wal_sync_methodをfsync,open_sync,fdatasyncの3つで試したのですが
以下の通りほぼ同じ数字なのですがこの場合どれを使えばいいのでしょうか？
■fsync
tps = 1027.689818 (including connections establishing)
tps = 1209.468247 (excluding connections establishing)
tps = 1034.481365 (including connections establishing)
tps = 1216.056506 (excluding connections establishing)
tps = 752.244097 (including connections establishing)
tps = 845.057033 (excluding connections establishing)
■open_sync
tps = 1052.034042 (including connections establishing)
tps = 1242.544732 (excluding connections establishing)
tps = 1028.417258 (including connections establishing)
tps = 1211.121120 (excluding connections establishing)
tps = 1019.530379 (including connections establishing)
tps = 1204.729693 (excluding connections establishing)
■fdatasync
tps = 775.081298 (including connections establishing)
tps = 873.168620 (excluding connections establishing)
tps = 1016.433399 (including connections establishing)
tps = 1197.497231 (excluding connections establishing)
tps = 1022.273040 (including connections establishing)
tps = 1208.232215 (excluding connections establishing)
564:NAME IS NULL
08/05/13 23:21:07
Direct I/Oが効くからopen_syncでいいんじゃね?
たまにバグ持ちのOSもいるけど。
565:NAME IS NULL
08/05/14 16:50:51
VACUUM FULLをやるとすごい時間かかると聞いていたけど、3分くらいで終わってしまった。
テーブルは200個くらいで、多いテーブルでもレコード数は20,30万レコードくらい、大半は数千、数万程度
これってだいぶ少ない感じだからかな？それともdumpを戻してからあまりデータが更新されてないから
汚れてないってことで早かったんですかね？
後、VACUUM FULL テーブル名って感じでテーブルのみ指定できるけど、これを
全テーブルやれば、VACUUM FULLやったときと変わらないのでしょうか？
566:NAME IS NULL
08/05/14 19:31:16
>>565
>dumpを戻してからあまりデータが更新されてないから
> 汚れてないってことで早かったんですかね？
> これを全テーブルやれば、VACUUM FULLやったときと変わらないのでしょうか？
いちいちその通りでなにも返す言葉がない
567:NAME IS NULL
08/05/16 22:29:54
sequenceの値を自分で設定することは出来ますか。
今は 1 から始まっているのをたとえば10000から始めたいんですが。
568:NAME IS NULL
08/05/16 22:51:10
URLﾘﾝｸ(ash.jp)
答えを知らない俺がググって10秒で分かる内容を質問する勇気に感心した。
569:567
08/05/16 22:57:55
自己レスです。setval()関数でできるようです。
570:567
08/05/16 22:58:21
>>568
すみません
571:NAME IS NULL
08/05/17 00:28:43
使用環境はXPなんですが
COPYでテーブルのデータを全てCSVに落としたいのですが
文字列が２重引用符で囲まれなくて困っています。
COPYの説明読む限り、デフォルトは２重引用符となっています。
QUOTEで直接指定しても変わりありません。
572:NAME IS NULL
08/05/17 09:40:53
>>571
FORCE QUOTE
573:NAME IS NULL
08/05/17 13:30:17
FORCE QUOTEってカラム指定しないと駄目ですよね？
文字列だけ指定した引用符で囲いたいんですが
単純にQUOTEだとNULL文字だけしか引用符で囲ってくれないんです。
574:NAME IS NULL
08/05/17 13:54:31
文字列型のカラムを全部指定すればいいじゃん。
575:NAME IS NULL
08/05/17 17:56:34
集約関数のパラメータとなるような複数行のデータを表現する式というのはありますか？
こういう感じのことをしたいのですが
SELECT * FROM TABLE1 ORDER BY MAX(COL1,COL2,COL3); // COL1～3のなかで最大の値を取りたい
576:NAME IS NULL
08/05/17 17:57:02 UrIKqt4y
しまった。
習慣でsageてしまった・・・
577:NAME IS NULL
08/05/17 18:58:10
>>575
greatest() かな。max(greatest(col1, col2, col3))
578:NAME IS NULL
08/05/19 20:51:28 KbQaa3g3
2chのような掲示板を作ろうと、以下のテーブル構成を考えています。
この場合、連番はどうやって取るのがスマートでしょうか？
板（板No、･･･）
スレッド（板No、スレッドNo、･･･）
レス（板No、スレッドNo、レスNo、･･･）
板Noはシーケンスでいいとして、スレッドNoとレスNoの取り方で迷っています。
Max(レスNo) + 1で取るのがいいか、発番テーブルを作るのがいいか。
あるいはもっとスマートなやり方があるのでしょうか。
アドバイスお願いします。
579:NAME IS NULL
08/05/19 22:29:57
スレッドも(big)serial型でいいんじゃ？規則正しく連続してる必要ないよね？
insert ～ select ～でいいんじゃない？
発番号テーブルとか作ってわざわざ整合とりにくくする必要性無いと思う
580:NAME IS NULL
08/05/20 01:06:45 yIeco2cc
あいうえお<br><h1>かきくけこ</h1>
というデータが入っていたとしまして、SQLでこのタグ(<>で囲まれている文字列)を省き、
あいうえおかきくけこ
として検索できるようにする方法はありますでしょうか。
もしありましたらご教示いただけますと幸いです。
宜しくお願いします。
581:NAME IS NULL
08/05/20 02:47:41
>>580
> SQLでこのタグ(<>で囲まれている文字列)を省き、
regexp_replace(データ,'<.*?>','','g')
582:NAME IS NULL
08/05/20 09:41:17
取り出してからの切った貼ったですかね。
583:NAME IS NULL
08/05/21 09:35:37 LdhCgnd0
よろしくお願いします。
金額を入れるカラムは、精度の高いnumericを使用した方がよいと聞いたのですが、
マニュアル等で調べたところ、そのカラムに整数しか入らない場合はinteger等で十分のような気がするのですが
この考え方で合っていますでしょうか？
584:NAME IS NULL
08/05/21 09:58:22
10/3*3とか？
585:583
08/05/21 11:52:47
>>584
確かにその場合は小数点があるのでnumericが必要ですよね。
必ず整数しか入らない前提の場合はintegerでも良いと思ってよいでしょうか。
「精度」の意味がよく分かっていないかもしれませんが、
整数しか格納しないのであれば、numericもintegerでも計算で誤差は生まれないと
思っているのですが。。
586:NAME IS NULL
08/05/21 13:33:19
SQLで計算はしないのか？
587:583
08/05/21 17:20:11
>>586
SQLで計算はしないOR計算しても小数点が出ないという前提です。
588:NAME IS NULL
08/05/21 20:30:49
>>583
numericが「何と較べて」精度が高いのか聞かなかった？
真数値： numeric integer
概数値： real float
あと、「精度」といっているのが誤差の少なさなのか
有効数字のことなのか。
「金額～」という文脈で精度のことを言うときは、上記の
真数値である(誤差がない)ことを言う場合が多い。
589:NAME IS NULL
08/05/22 07:29:35
そもそも、通貨って円だけじゃないし
日本でも銭まで必要なこともあるし
そういうのを踏まえて必要かそうでないか判断しては
590:583
08/05/22 10:19:23
>>588,589
レスありがとうございます。
元々、素人DB設計者がnumeric(10,0)としていたので
突っ込んでやりたいなと思って質問しました。
＞numericが「何と較べて」精度が高いのか聞かなかった？
金額の計算をする時、integer等と比較しての話として精度が高いと聞きました。
なので、「精度」は
＞真数値である(誤差がない)ことを言う場合が多い。
こっちのことだと思います。
＞日本でも銭まで必要なこともあるし
情報小出しですいません。
私も、numericを使うことに異論は無いのですが、
もともとのカラムがnumeric(10,0)だったんです。。
「精度が高い」と人から聞いただけで、「精度」の意味も考えずに
numeric型を採用しているDB設計者に対して
「小数以下格納しないならintegerでいいじゃないですか」という理由が欲しかった次第です。
低レベルな話ですごく申し訳ないです。。
591:NAME IS NULL
08/05/22 10:48:29
整数としてしか扱わない、というのなら整数でもいいんじゃない？
金額を扱う場合はnumericと決めている、のならnumericでもいいし。
592:NAME IS NULL
08/05/22 12:10:45
numericだのintegerだの云々言いたい583は、当然
URLﾘﾝｸ(ja.wikipedia.org)
あたりの知識はあるんだよな?
まあ、ヲレはPostgreSQLでnumericがどう実装されているか知らないけどさ。
593:NAME IS NULL
08/05/22 21:03:28
整数を扱うとき、numeric型とinteger 型と選ぶ基準は
扱う桁数でいいだろ。
お金だと桁数10桁で足りない事もあるし、そういうときはnumericでいいんじゃね？
俺は、Java上で金額計算するから格納時には桁数roundしてるから
入れ物さえあれば問題ない。
>>590
逆になんでnumericなのか聞けば？
俺は桁数が分かるという利点でnumericにしてるんだと思うがね。
DBのポータビリティを考えて、もう1層上でテーブル設計してるんじゃないか？
594:NAME IS NULL
08/05/22 21:10:58
>>590
うちも同じようなテーブル設計があったので、直せるところはintにしま
した。物の個数ですらnumericでやってあったから、さらに低レベルかも。
intかbigintの方がコンパクトで高速と思ってます。
595:590
08/05/23 12:41:48
>>591-594
レスありがとうございます。
とりあえず、DB設計者になぜnumericなのか聞いてみた結果
→「これがいいって聞いたから」
あとは愚痴スレ行きます。。
>俺は、Java上で金額計算するから格納時には桁数roundしてるから
>入れ物さえあれば問題ない。
私も同じようにしようと思います。
ご助言いただき、ありがとうございました。
596:NAME IS NULL
08/05/24 07:11:45
>>565
ごめん嘘書いちゃった
テーブル指定無しで実行しないとシステムカタログがvacuumされないらしい
URLﾘﾝｸ(www.thinkit.co.jp)
597:NAME IS NULL
08/05/25 19:07:27
>>596
いや、システムカタログ名を指定すればできるだろう。
VACUUM pg_class;
とか。面倒だから普通は autovacuum に任せるけど。
598:NAME IS NULL
08/05/27 14:05:46 ZU6uB09A
結合条件にLIKEなどを使って
あるカラムが別テーブルのカラムを含んでいる行と外部結合ってできないでしょうか？
SELECT o.foo, u.bar FROM hoge o LEFT OUTER JOIN huga u ON o.foo LIKE '%'||u.bar||'%';
じゃだめでした。
なんかうまい方法ないでしょうか？
599:NAME IS NULL
08/05/27 14:38:11
ONのあとは結合条件、o.foo LIKE '%'||u.bar||'%'はWHERE句に