PostgreSQL Part.5

PostgreSQL (ぽすとぐれすきゅーえる, ぽすとぐれす) について語るスレです。
前スレ [【Windows】 PostgreSQL8　Part.1 【対応】] http://pc11.2ch.net/test/read.cgi/db/1102247223/

● 関連サイト
[PostgreSQL 本家] http://www.postgresql.org/
[日本PostgreSQLユーザ会] http://www.postgresql.jp/
[ドキュメント] http://www.postgresql.jp/document/current/html/
[ダウンロード] http://www.postgresql.jp/PostgreSQL
[pgFoundry] http://pgfoundry.org/

● 関連スレ
[2ch検索] http://find.2ch.net/index.php?STR=PostgreSQL
[WebProg/PostgreSQL 2テーブル目] http://pc11.2ch.net/test/read.cgi/php/1047317680/

>>1
乙！　盛り上がっていきましょうw

もうpostgresSQLが入ってるサーバを全部ぶっ壊したい

ネタがないので、PostgreSQL関連の過去ログでもはっとく。

【Windows】 PostgreSQL8　Part.1 【対応】 http://pc11.2ch.net/test/read.cgi/db/1102247223/
■　　　PostgreSQLのことならここで聞け　　　■ http://pc8.2ch.net/test/read.cgi/db/1056960249/
■　　　PostgreSQLのことならここで聞け　　　■ http://pc8.2ch.net/test/read.cgi/db/1091523132/
PostgresSQLについて語ろう http://pc8.2ch.net/test/read.cgi/db/1056992724/
PostgreSQLについて語ろう where OID=2::oid http://pc8.2ch.net/test/read.cgi/db/1136805513/
PostgreSQL 2テーブル目 http://pc8.2ch.net/test/read.cgi/db/1056944337/
PostgreSQL ＆ pgsql-jp ML 3テーブル目 http://pc11.2ch.net/test/read.cgi/db/1079771059/

「ネタがない」と書いて思い出したんだけど、8.3の正式リリースはいつかな?
年明けにでもPCを入れ替えるものがあるので、どうせなら8.3をつっこんでやろうかと思ったり。

>>5
今月とか来月じゃなかったっけ？
セミナーでベータ版使ってくれって言ってた。

1月だね
本家のMLに流れてた

>>6-7
サンクス。
初夏の頃にフリーズされてから結構かかったような。
こんなもんでしたっけ? 当初夏から秋にかけてリリースする
雰囲気だったような気もするのだが。

-- 素直に8.2を入れて頃合いを見て8.3へ上げることにすっかな。

1月リリース予定のミニプロジェクトの開発で8.3beta使ってる。
初めて使うもので (MySQL からの移行)、せっかくだから Auto VACUUM あったら楽そうでいいかなとか。

Beta 4 の文字列が公式サイトで見られるけど、配布されているもののファイル名は beta3 だね…。

AUTO VACUUMは8.1からあるべ

beta4やっとで配布サイトに入ったな

CentOS5でphpPgAdminをrpmでインストールしようとしています。

http://phppgadmin.sourceforge.net/?page=download
↑のページの中断にRHEL用のrpmの置き場として、
↓へのリンクがあるのですが、NotFoundになっています。
http://ftp.icdevgroup.org/phpPgAdmin/


どなたか他の置き場などご存知ないですか？？

DB使用中の一覧は以下のSQLで見られるようですが、
select * from pg_stat_activity
その接続を強制切断（強制終了）はできるのでしょうか？
Windows版使用です。

>>8
9.0とメジャーバージョンアップしてもいいぐらい
機能がてんこ盛りだからね。
レビューに時間がかかったみたい。

ポスグレと怪獣フェドラはどんどん成長してしまって、
もうわかりませんわ。

>>13
procpid でプロセスを終了させるといいようですが、
自分自身の接続のpostgres.exe の PID（procpid）を取得する方法はあるのでしょうか？

>>16
つpg_cancel_backend()

>>17
16が知りたいのは自分自身のPIDを取得する方法

>>18 のレスの意味がよくわからなかったので>>17のpg_cancel_backend調べたら
引数にpidいるのかｗ
\df でそれらしいの探したら pg_backend_pid() というやつで返ってきたけど。

>>17-19
ありがとうございます。

select pg_backend_pid();
で自分自身の接続のpostgres.exe の PID（procpid）を取得できますね。
http://www.fiberbit.net/user/kunyami/postgresql/statcollector/statfunc/pg_backend_pid.html

select pg_cancel_backend(引数procpid);
で"t"（trueのこと?)が返ってくるけど、プロセスがKILLされるわけじゃないようだし。。。

pg_cancel_backend(引数procpid) の使い方がよくわからないけど、ま、いっか。（＾＾；

pg_cancel_backend(引数procpid)
って、たぶん実行中だとキャンセルされるっていう意味なんだろーなー？！（想像）

誰かプロの方、おしえていただけませんでしょうか。

テーブルの列には、改行コード(\n)を含む文字列が格納されています。
db=# insert into testtable values('123\n456\n789');
db=# select * from testtable;
value
-------------
123
456
789

そこで、上記のように改行された状態ではなく、以下のように
改行コードが\nのままの値でデータベースに入力したいのです。。。
db=# select * from testtable;
value
---------------
123\n456\n789

このようにするには、どのようにinsertすれば良いのでしょうか。。。
すいませんが、よろしくお願いしますm(__)m

>>22
> 改行コードが\nのままの値
改行コードが含まれているんだから、表示すると改行されるのが当たり前。

「改行コードを『'\n'という文字列に変換』して」ということなら、
INSERT INTO Table VALUES(replace('123\n456\n789','\n','\\n'));
なんかでやればいいけど、それはもう改行コードじゃなく'\n'という文字列。

>>22
http://www.postgresql.jp/document/pg825doc/html/sql-syntax-lexical.html
の4.1.2.1.

>>23
>>24
どうもありがとうございます。
ちょっと試してみます。

>>20
pg_cancel_backend()は実行中のSQLをキャンセルする関数。
プロセスを終了させるもんではないわな。
例えば、数分間結果が返ってこないSQLを一旦キャンセルしたいようなときに使う。

>>26
やはりそうでしたか。
どうもです。
pg_kill_pid()なんていう関数があると便利なんですがねぇ。笑

バカって悪用される可能性を考えないんだよな。

プロセス切られることによって、それから悪用される可能性ってあるかなぁ？

もっとバカはね、
postgres.exe 以外のプロセスもKILLできると勘違いしてるか、
プログラミングができない人だよ。
仮にKILLできても、su や admin なら問題ない。

次の一手は、
『釣られてるよ』
ですかね？！

うえへへへへ、まったくバカだぜこいつ……orz
接続毎にバックエンドが上がるのを完全に忘れてた。

危険な機能には権限で対処てのは当然思ってたけど、
pg_cancel_backend(pid)も管理者権限がないと使えないんだな。
PQcancelと違って任意のバックエンドに対して使えるみたいだから
当然だけど。

13が意図してることはさっぱり理解も同意もできないし、
単に接続終了かキャンセル＋終了でよさそうなもんだけど、
調べてみるとpg_terminate_backend(pid)てのがソースにあった。
中身はSIGTERM送るだけ。
信頼性を損ねるからダメとコメントが付いてて使えないが。

長文スマソ

プロセスの強制終了できないみたいね。
APIで強制終了できるのかな？

>>32
＞単に接続終了かキャンセル＋終了でよさそうなもんだけど、

それってどういうコマンドですか？
その操作でプロセスも終了するんですか？

32って反省文ですかね？

>>28
ちなみに悪用される危険性、わからないな。
悪用される危険性があるなら、されないように
関数を作ればいいんではないか？

Linuxでは、該当のプロセス、KILLできますか？

サービスの停止、最開始が手っ取り早そう。
なんの権限無しにできるのが、アレだけど。笑

悪用もへったくれもないが、さて、どんな風にからんでくるの？

>>37
×最開始
○再開始

>>37
Windowsの管理者権限だけが必要だね

「今から HOGE.HOUR 時間前」 ってどう書けばいいですか?

CURRENT_TIMESTAMP - INTERVAL (HOGE.HOUR || ' hour')

と書いてみたが HOGE.HOUR で構文ネラーで撥ねられた。

CURRENT_TIMESTAMP - CAST(HOGE.HOUR || ' hour' AS INTERVAL)
CURRENT_TIMESTAMP - (HOGE.HOUR || ' hour')::INTERVAL

おお、でけた。
INTERVAL って前置演算子みたいなものかと思ってましたが、よく考えたら型でしたね。
ありがとうございました。

PostgreSQL の INHERIT (テーブル定義) って中では内部結合でやってるんですか?

Vistaに8.2.5が入らないと思ってたら、以下のページを見つけたのですが、
8.3ではインストーラの方でVista対応してくれるんでしょか？
http://www.iwahrt.com/iwahrt/ListProblemSolution.do?problemNo=185&categoryNo=13

postgresql(linux版)を自分のノートパソコンにいれて使ってます。
某国の株価データを入れているのですが、レコードが7000万件くらいに
なってしまいます。(今はずーっとinsertしつづけていて、5000万件を
越えたところ)。まだ全部入ってないので途中なのですが、
SELECT * from stock_data;
とすると、20分ほど頑張った後でメモリ不足で表示がされませんでした。
一件のレコードは、35個のデータから構成されてます。こういう状況で
上記のSQLとかがサクサク実行できるような方法はありませんか？
メモリは2GB積んでます。
今週終わりくらいに時間がとれるので、oracleとmysqlもためしてみようと
思ってるんですが、mysqlはさわったことないし、oracleはGUI経由
で操作するのを強制されるようなところが嫌で、、psqlとかでリモートから
色々できるのがいいので、できればpostgresqlでやりたいんですが。。
あと、ティックデータも入手できれば飛躍的にデータ量が増えそうなのですが、
postgresqlの実用的な範囲って、データ量どれくらいなのでしょうか？
色々質問ばかりですいません。

自分は詳しくないので、答えられませんが、
詳細環境も提示したほうが答えやすいかも。
少なくともPostgreSQLのバージョンは。

DB触り始めで大量データ扱うと、最初にぶつかるよなこれ。
SELECT文で条件指定しないで全取得すれば、データ全取得しようとするから
遅いし、（クライアントの）メモリが足りなくなるよね。

とりあえずは、条件設定したり、LIMIT OFFSET で件数絞ってやってごらん。

条件設定して件数が少なくても遅いなら、プライマリキーやインデックスがどうなってるかもチェック

>>47
サーバではなくクライアント側のメモリが足らなくなるというのがポイントですな。
クライアント側では、普通、何万件オーダーのSQLの返りを受け取れるようにはできていないので。

カーソルの使い方覚えて、例えば10万件ずつFETCHするような
クライアントプログラムを書けばいい。
http://www.postgresql.jp/document/current/html/sql-fetch.html
http://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor

あとは、年月毎や銘柄毎でテーブルのパーティショニングを行うとかかね。
常にデータ全体をスキャンする必要があるならあまり意味無いけど。
http://www.postgresql.jp/document/current/html/ddl-partitioning.html

まずはcout(*)でチェクしてから、
Limit とかoffset で抽出してみるのわ？

count(*) ですね。
これもインデックス無いとかなり時間かかると思う。

COUNT() ってインデックス関係あるんだっけ?

WHERE句入ってれば場合によっては

8.3では同期スキャンの副作用でSELECTの度に結果が変動するリスクがあるため、
ORDER BY を付けずに LIMIT, OFFSET を付けたSELECT文の実行を繰り返すと妙な事になるかもしれない。
http://journal.mycom.co.jp/special/2007/postgresql/005.html

全体のデータを取り出したいのであれば、
SELECT * FROM hoge ORDER BY idhoge LIMIT 10000 OFFSET $1; （$1には数字を入れる）
みたいなSQL文を複数回実行するより
SELECT * FROM hoge ORDER BY idhoge;
のカーソルを作ってFETCHを繰り返した方がレスポンスは良くなると思う。
（カーソルを使わないとSELECTの度にソートが行われてしまうかも？）
結果がソートされてる必要も無ければ、ORDER句も外せてさらに快適に。

大昔に DB2 調べてて、ORDER BY + FETCH FIRST n ROWS ONLY 使うと
n 件の行を取ってきた後にソートするという動きで金玉もげるかと思ったのを思い出した。

みなさんありがとうございます。
ようやくinsertが終了しました。
kabu=# SELECT count(*) from stock_data;
count
----------
73056656
(1 row)

主キーは、日時、会社コード1、会社コード2、の3つです。
会社コード別か、日時を範囲指定して、selectすることが多いと
思うので、それらにインデックスを設定しようかと思います。
（インデックスって勝手には設定されないですよね。）

あと、カーソルというのを調べてみます。存在すら知りませんでした。
テーブルを分けるというのは考えてみませんでしたが、上記のことを
やってみてダメだったら考えます。でもINSERTのやり直しは嫌だなあ。

それにしても、データだけでディスクをかなり占領するし、メモリ確保関係の
エラーにも何回も遭遇するし、大量データを扱うのは大変ですね。
これ以上データサイズが増えるととても扱いきれないと思いました。

あと、バージョンは、postgresql-8.2.4-27 となってました。
openSuSEのrpmのバージョンです。
ありがとうございました。

oracleとmysqlはやってみてあとで報告します。

>>57
ノートPCでメモリ2Gでクライアント&サーバをやって
> それにしても、データだけでディスクをかなり占領するし、メモリ確保関係の
> エラーにも何回も遭遇するし、大量データを扱うのは大変ですね。
ってそりゃあ、おいおいって感じだ。

零細がダイレクトメール用の情報扱ってるみたいな状況だな。

7300万件w

慣れればどうってことないけど、最初は少ない数でいろいろやったほうが
操作も覚えるしいいと思うがな

>>57
データベースシステムを図書館と司書さんに例えると、
----------------例え話----------------------------
お客(クライアント)は、司書さんに「○○の本を貸してください(SELECT * FROM xxx WHERE ○○)」と問合せをかける。
お客は問合せの結果をバッグに入れてユーザーの所まで持ってくるわけだ。

藻前さんが最初にやったのは、図書館の本全部を一気にバッグに入れて持ってこさせようとした。そりゃ無理だ罠。

だからこそ、「○○な本のうち、50音順で500冊目から100冊持ってきて」という様に要求を区切って出したり、
「○○な本全体で、△△という単語が何回出現するか数えてきて」と集計する要求を出したりするわけだ。

DBのテーブルを分類ごとの書架だとすると、
その書架でも本がすごく多いモノ(例えば小説とか)を一定単位(作者別とか)で仕切るのをパーティショニングという。
パーティショニングされていないと、司書さんは書架全体の中から探す事になり、時間が掛かる。

インデックスっていうのは、図書館の蔵書を管理するカードの様なもので、それを元に検索すると素早く検索できる。

いくら司書さんが、本の扱いに慣れていても、一瞬一瞬に扱う本は1冊づつで、
その時扱っている本(インデックス含む)を差す指がカーソルという事だ。
----------------例え話----------------------------

株価のデータベースで、過去も含めた全てのデータを集計もせずに取り出す場面が(バックアップ目的以外で)あるとも思えない。
バックアップ用途なら、ダンプを取れば良いし、集計クエリやら条件付けやらをして、取り出す件数を絞れば、
メモリ不足でクライアントが動かなくなる事態がそうそう頻発するとは思えない。

OlacleやらMySQLにデータを移すなら、共通に使えるクライアント(ODBC等)経由で、
PostgreSQLのテーブルからSELECT INTOとか使って一気に流し込んじゃう方が楽だ。

これかなーり便利だわ
http://pgfoundry.org/projects/ptop/

よくわからないけど、自分には関係なさそう

>>63
パフォーマンスチューニングするのに使えそうね。

そういえば8.3の全文検索機能試してる人いる？

Ludia は試して…　みたい

>>62
57じゃないが、勉強になったThx

JPUGの忘年会は盛り上がったの？

あー、昨日だっけ。
参加できたなー、申しこむんだった

v8.3はまだRCも出てないんだね

57です。
色々勉強になりました。(62の方とか)
ところでoracleとmysqlとの比較ですが、ノートではきついので
別のマシンでやることにしました。postgresqlへのinsertは終わったのですが、
mysqlとoracleへの移行で止まってます。

> OlacleやらMySQLにデータを移すなら、共通に使えるクライアント(ODBC等)経由で、
> PostgreSQLのテーブルからSELECT INTOとか使って一気に流し込んじゃう方が楽だ。
これについて、もう少し具体的におしえてもらえませんか？
一方のDBからselect * from tableとかでデータを読み込んで、それをselect into
で別のテーブルに流し込むんですよね？それはpsqlの中で行えますか？

上の内容が分からなかったので、postgresqlにダンプをsqlで出させて移行しようと
思ったのですが、(例えばmysqlについては、こんな感じ)
# pg_dump -d kabu > dump_file
# dump_fileを編集(テーブル一個の単純なDBなので、これは簡単そう)
# mysql -u root kabu < dump_file
これよりも速くできると思われるでしょうか？

スクリプト書いて両方にアクセスするって話でしょ？
psqlでDOBC使わないし。
ODBC使うツールでやってくれるやつもありそうだけど。

まあ実行可能ならdumpしたSQL食わせるのでいいと思うよ

>>71
> # pg_dump -d kabu > dump_file
> # dump_fileを編集(テーブル一個の単純なDBなので、これは簡単そう)
本当に編集が簡単と思っているのか？
7000万件INSERTした結果のdump_fileなんだぞ？
なんか最初から見てるけど、感覚がおかしい。

>>73
テーブルが一個しかなくて、編集といっても、create tableと
insert 以外を削除するだけで、7000万件のinsertには触らなかったので、、、、
スクリプト(ruby)を書いてなんとかやりました。
いまmysqlのDBにinsert中です。
insertが始まってから3時間くらいですが、もう4000万件を突破しました。
select count(*) from table;
も速いし、とりあえずmysqlのほうが速そうな感触を持ちました。

ところで7000万件がDBとして多いのかどうかいまいち見当がつかないのですが、
株価のデータとしては仕方ないというか、みんなこれくらいのサイズになって
しまうと思うのですが、大体では、
3000(上場の会社数)×340(一年で市場が開いている日数)×80(データのある年の数)＝81600000
になります。(実際には昔に行くほど会社数が少なくなる)
これはやはり、多すぎると考えるべきなのでしょうか？
それともDBの使い方がまずいのでしょうか？

>>74
少なくとも
> それともDBの使い方がまずいのでしょうか？
なんてことを書いているようなレベルの人には
> これはやはり、多すぎると考えるべきなのでしょうか？
多過ぎるかと。

>>74
抽出条件に対して適切にINDEX張ってれば
そんなに多いって訳でもないと思うが…
>>75の言うことももっともだと思う。

少ないデータで仕組みきっちり作ってから
大量データ流し込んでチューニング

>>71
いまさらだろうが、クライアントPCにMS-ACCESSと各DB用のODBCドライバをインスコして、
テーブル構造だけ先に作って、
INSERT INTO コピー先 (項目1,・・・,項目n) SELECT 項目1,・・・,項目n FROM コピー元;
とかやれば、変に編集する手間もなくコピれる。

藻前さんが何をやりたいのか今ひとつ理解できないが、
MySQL、Oracle、PostgreSQLのどれを選ぶかの目安を書いてみようと思う。
・ クライアントプラットフォームが多種多様になる場合
　 → ストアードプロシージャによるビジネスロジックの共通化が有効
　 　 → OracleかPostgreSQL
・ webなどクライアントが1種類のみで処理スピードの高速化が必要な場合
　 → DB単体でのスピードを優先
　 　 → MySQL
・ DB未経験でこれから色々なシステムを構築するための勉強の場合
　 → ストアードプロシージャに慣れておく事は有効
　 　 → OracleかPostgreSQL
って感じだと思われ。

OracleとPostgreSQLだが、仕事として見た場合、
・ 客の予算が少ない or 面倒事を自分でやってでも金が欲しい時はPostgreSQL
・ 客の予算がとても多い and (客がうるさい Or 忙しいので面倒事をサポセンに任せたい)時はOracle
・ 客の予算が少ない and (客がうるさい Or 忙しいので面倒事をサポセンに任せたい)時は仕事自体をお断りする
って感じだと思われ。

仕事自体お断りする権限がないときは辞めるしかないか・・・

>>79
作るだけ作って辞めるのもアリだなwww
ﾄﾞｷｭﾒﾝﾄ? ﾒﾝﾃﾅﾝｽ? ｼﾗﾈwww

>>79
お断り出来ないときは、見積でふっかけましょう。

メンドクサイ客で予算が少ない時は、見積次第で客をコントロールする事は可能です。
自社の営業がヘッポコな時はむずかしいですが、
自社の営業を接待漬けにするぐらいの勢いがあれば危機対応能力は向上すると思います。

見積もりで吹っかける→「いやーそれは高いでしょう」→見積もり減

見積もりするけんｇ（ry

そうですか　この見積もりでだめだとしますと他をあたっていただく事になりますが。

つ〜か、最初から予算ありきで
「お前らの見積なんぞ知らんがな。とにかくこれだけの金で作れ。」
・・・ってところも多いがな。

8.3日本語版はいつでんの？

日本語版って何よ

すごいくだらないことかもしれなけど質問させてください。

ユーザーを管理するテーブルって何て名前にしてます？
user
はすでにあるみたいで使えないし
"user"
にすると、セレクトかける時も"user"にしなきゃならないし
tbl_user
とか接頭語や接尾語つけるのはあんまり好きじゃないし・・・
users
っていうのも考えたけど、なんでユーザーだけ複数なんだ？って感じだし

どうして、こんな一般的にみんな使いそうな名前を初期で入れてるだよ〜
他の名前にしてくれればいいのに
まあ、どうでもいいから好きなのつけろって言われそうだけど、皆さんどうしてます？

affiliate, associate, brother, comrade, constituent, fellow, joiner, member, sister
どうでもいいから好きなのつけろ

>>86
http://www.postgresql.jp/PostgreSQL?top

>>89
本体は日本語版も何も無いだろう

>>87
passwd

>>90
そもそも本体の話しはしてない禿

>>87
usertbl

Oracleと性能比較した資料てどこかにある？

>>94
そんな資料は出せないはず。Oracleが禁止してなかったっけ？
昔の話だから、今はどうか知らないけど。

データ型として配列を使っています。
配列の要素にNULLが存在する場合にマッチするクエリは、どのように記述するのか教えてください。

PostgreSQL 8.3beta4です。

http://click.j-a-net.jp/1006827/223692/

>> 96
自前で比較用の関数用意するしかないんじゃね。
ANYとIS NULLの組み合わせじゃダメだし。

>>98

ありがとうございます。次のテーブルに対して、
下記のクエリを作成することで対処しました。遅いですが。

create table tbl(pkey integer primary key, a integer[] not null);

select * from tbl t1 where exists(
select * from (select a,generate_series(array_lower(a,1),array_upper(a,1)) from tbl t2 where t1.pkey=t2.pkey) as t3(a,i) where a[i] is null
);

http://itpro.nikkeibp.co.jp/members/ITPro/oss/20050722/165140/?P=2

このページのConstraint Exclusionは、条件に合う部分だけ検索がかけれるということなのでしょうか？
実際に試してみたところ、テーブル内を全て検索するのと速度があまりかわらないような気がするのですが・・・

PostgreSQL 8.2.5です

2008-01-07 Cumulative Security Update Release (8.2.6, 8.1.11, 8.0.15, 7.4.19 and 7.3.21)
http://www.postgresql.org/about/news.905

PostgreSQL 8.3RC1 is out.
http://www.postgresql.org/ftp/source/v8.3RC1/
http://www.postgresql.org/ftp/binary/v8.3rc1/

っと、まだ8.3rc1はアナウンスされておらず勇み足だったらしい。すまぬ。

今朝見たらアナウンスされてました。
しかし8.3は難産ですね。RCも1で終わってくれるのかな･･･？

http://www.postgresql.org/developer/beta
http://archives.postgresql.org/pgsql-announce/2008-01/index.php

8.3rc1のアナウンスはまだじゃない？と見てたら、pgAdminIII 1.8.1 が出てるね。

pgAdmin III v1.8.1 released
http://archives.postgresql.org/pgsql-announce/2008-01/msg00001.php

PostgreSQLに危険なセキュリティ・ホール，管理者はバージョンアップを
http://itpro.nikkeibp.co.jp/article/NEWS/20080108/290554/

PostgreSQLに脆弱性：バージョン7.3ブランチのサポート打ち切りに
http://builder.japan.zdnet.com/news/story/0,3800079086,20364378,00.htm

PostgreSQL 7.3ブランチが終了：8.2系へアップグレードを
http://builder.japan.zdnet.com/news/story/0,3800079086,20364391,00.htm

後者の記事は、Windows版バイナリとそれ以外のEOLの話をごっちゃにしているのが何とも。

Windows版 8.3 に期待してるのだけど
通常、PC-Unix 版に比べてどのくらい遅れてリリースされるのかな。

>>107
普通は遅れはほぼゼロ。

sequenceをデフォルトテーブルスペース以外に作成する方法はないんかいな？

マニュアル見ても、create/alter でテーブルスペース指定することはできないし、
serial型で自動作成されるsequenceも指定できないし。（テーブルが別テーブルスペースでもsequenceはデフォルト域になってしまう。）
最新の8.3でもだめそう。
だれかこの件について情報持ってないですか？

ちなみに、pg_classのテーブルスペース情報をupdateして、物理ファイルをmvしたら、なんとなく動いてるいるようには見える。が、さすがに本番では怖いのでやってない。

>>108
サンクス

>>109
そもそもSEQUENCEを別のテーブルスペースに作成する意味はあるの？
メリットが分からん。

あと、pg_classをUPDATEしなくても、移動した物理ファイルに対して、
ln でリンクを作ってやればいいんじゃない？

>> 109
設定パラメータ default_tablespace を変更してもダメ？

>> 111
> SEQUENCEを別のテーブルスペースに作成する意味はあるの？
誰しもそう思っているから、むしろ忘れられていたのかも。
機能としてはあってもかまわない気はする。

java+tomcatであるテーブルの列を５０づつ表示するロジックを作ったとします。
まずはじめの0〜50は正常に表示されるのですが次の50を表示するを選択すると
50〜100までの表示ではなく50〜ラストまでの表示になってしますのです。
SQL文は次のようになっています"select * from -- where number > "+number+" and number <= "+number+50;
何か間違っていることがあれば指摘してください。お願いします。numberの変数はデバッグで意図した値な事を
確認しています。なおnumberはserialです。

>>113
知らんがな。実際に発行されているSQL文を0〜50と50〜100の時で
どうなってるか確認して、psqlで実行してみなよ。

LIMIT OFFSET のが簡単な気も

>>113
全然関係ないけど、実験の時でも普段から PreparedStatement 使うようにしとくといいぜ。

>>113
String + int + int って、((String + int) + int) って解釈されね？
n = 0 なら "number <= 050" で上限は 50 と解釈されるけど、
n = 50 だと "number <= 5050" になる気がするんだが。
「number <= " + (number+50)」と括弧を付ければ良いのでは。

同じ演算子の優先順位は左から右だから >>117 の通りだよ。

int n = 50;
System.out.println("select * from ... where number > " + n + " and number <= " + n + 50);

↓結果

select * from ... where number > 50 and number <= 5050

あと Statement は事故の元だから PreparedStatement 使え。

>>111
>>112
レスありがとうございます。

>>111
SEQUENCEを別のテーブルスペースに作成するメリットは、
テーブルを別のテーブルスペースに作成するメリットと同等と思ってます。
つまり、SEQUENCEのNextValを取得する際のI/O分散です。

>>112
default_tablespace を変更してから作成しても、だめなようです。

alter database db_hoge set defalult_tablespace = tablespace_hoge;
した上で、
crate table table_hoge( colume_hoge serial ) ;

したら、table_home は、tablespace_hoge 上に作成されますが、
colume_hoge の sequence は、oid = 0 の tablespace 上に作成されてます。

詳しい説明ありがとうございます。

が、numberはintです。

>>121
だからさぁ、PostgreSQL的にはそんなことどーでも良いんだよ。
サーバ側で処理したSQLをチェックしろ。
意図した通りのSQLになっていて、取得結果がおかしいなら
ここで質問するのはありだが、プログラムの間違い探しなら
よそでやれ。

失礼しました。>117様の言う通りでした。

"select * from -- where number > " や " and number <= " は文字列だからな。
しかしどれ使うにしても、ORDER BY つけないとな。

そして、実行するSQL文を吐き出してpsql等で確認するのは常に必須。

あらかじめ用意されたテーブルicmpを

create table d22t11(check(time < '2005-11-22 12:00:00' and time >= '2005-11-22 11:00:00)) inherits(icmp);

というように時系列で分割した後、constraint_exclusionパラメータをonにしてSELECT文かけてみたんですが、分割前と検索速度が変わっていません・・・。
もしかして分割してからデータを挿入しなければならないのでしょうか？

>124 なるほど〜order by という便利なソート機能があったんですね。
更新すると列が最後尾にくるので"どうしたもんか？"と思っていたところでした。
情報ありがとうございます。

>>119
> つまり、SEQUENCEのNextValを取得する際のI/O分散です。
それはさすがに意味ないと思う。千〜万単位で SEQUENCE 作るつもりでない限りは。
たいていはキャッシュに乗ってる。
バックアップなり信頼性目的ならば、ありうる状況かも。

>>127
今回の疑問は、そもそも、sequenceのテーブルスペース変更はできないのは何故なのか？
という好奇心的なものが発端でして、実質的に問題が発生しているわけではありませんけどね。
対応してないなら仕方ないですね。少し残念です。

>>125
それは、どう変わることが期待されてんの？

>> 125
timeを検索条件にした？

>>125

Ａというテーブルが親なら、　Ａ1　Ａ２　Ａ３　Ａ４という子の
テーブルを継承で作成する。そんで、Ａ１　Ａ２といった子に
insert する。そうすると親のＡに対してselect すると、子を見て
くれて、制約があれば関係ないのは除外するという仕組み。

質問です。
2つのテーブル、TABLE_A と TABLE_B があります。
両者のカラム構成は同じです。
現在、使用しているテーブルは TABLE_A です。TABLE_Bは使われていません。
TABLE_B にデータ列を挿入します。
その後、両者のテーブル名を入れ替える操作を次のようなSQLで行います。

BEGIN;
ALTER TABLE "TABLE_A" RENAME TO "TABLE_TMP";
ALTER TABLE "TABLE_B" RENAME TO "TABLE_A";
ALTER TABLE "TABLE_TMP" RENAME TO "TABLE_B";
COMMIT;

このように、テーブル名をスワップする際、データ量とかによっては、
TABLE_Aにアクセスできない時間が発生するのかどうかが疑問です。
(リネーム時に何かしらデータ量に応じた処理が行われますか？)

上記テーブルデータは、WEBサービスで使うデータで、
WEB上からユーザーがアクセスしてきた際に、呼び出されます。
アクセスのタイミングによっては、ユーザーには、空データが表示されてしまうのでしょうか？

よろしくお願いします。

>>132
> (リネーム時に何かしらデータ量に応じた処理が行われますか？)

行われない。
たぶんシステムカタログの一部が更新されるだけ。

> アクセスのタイミングによっては、ユーザーには、空データが表示されてしまうのでしょうか？

一瞬だけど TABLE_A が存在しないタイミングがあるから、
そのときのアクセスはエラーになる。

エラーじゃなくてテーブルロックの解除待ちじゃないかな。
他からTABLE_AとTABLE_Bを順次SELECTされた場合、
タイミングによっては片方のテーブルを2度読みしちゃう可能性があるけど。

>>134
ALTER 文が BEGIN, COMMIT で囲まれてるの見のがしてた。
ロック待ちだね。

> 他からTABLE_AとTABLE_Bを順次SELECTされた場合、
> タイミングによっては片方のテーブルを2度読みしちゃう可能性があるけど。

1トランザクションで ALTER 文を全部実行してるから、2度読みはないはず。

>>135
> 1トランザクションで ALTER 文を全部実行してるから、2度読みはないはず。

TABLE_Aを読み出して、TABLE_Bも読み出そうとしたら >>132が走ってロック待ち、
解除後TABLE_Bを読み込んだら、それはリネームされる前のTABLE_A。

>>136
最初に全部ロックを取ってしまうのはどうだろう。
BEGIN;
LOCK TABLE_A IN ACCESS EXCLUSIVE MODE;
LOCK TABLE_B IN ACCESS EXCLUSIVE MODE;
...

ていうかそういうのは読み込み側がトランザクション分離レベルやテーブルロックを適切に設定してないだけじゃん。

※nextval(text)とcurrval(text)について
PHPからDBにアクセスしIDを表示するようなシステムを作っています

CREATE TABLE test_tbl (id SERIAL PRIMARY KEY,memo TEXT);
というテーブルがあり
idカラムの番号を表示で利用する場合
１）が正しい気がしますが２）でも大丈夫なきもします
１）と２）どちらがpostgres(DB)としては安全で正しい運用方法
なのか教えていただけないでしょうか？

１）IDを前もって取得し変数($id)に入れて使いまわす方法
SELECT NEXTVAL('test_tbl_id_seq');
BEGIN
INSERT INTO test_tbl(id,memo)VALUES($id,'AAAA');
COMMIT

２）IDを後から取得して使う方法
BEGIN
INSERT INTO test_tbl(memo)VALUES('AAAA');
COMMIT
SELECT CURRVAL('test_tbl_id_seq');

>>135
ふつう、DDLにトランザクションは効かないか発行時点でcommitされてしまうと思うが。

と思って調べてみたら、SQLServerはDDLもrollbackできるんだな。

DB2 でも普通にできるが。

>>136
両テーブルのSELECTも、トランザクションで実行するもんだと思うが。

>>139
どっちでも完全に一緒。お好みで。
PostgreSQLの方言でよければ RETURNING もアリ。
INSERT INTO test_tbl(memo) VALUES('AAA') RETURNING id;

>>133-138
>>140-142

ご回答ありがとうございます！
大変参考になりました！

ただ、考えてみれば、
テーブルデータ更新中のアクセスに対応するための方法だったので、
更新失敗によるデータ破損や、自前の例外処理を心配するぐらいなら、
最初から1テーブルで、データ更新 (COPYで行う) 自体をトランザクションで行う方が安全だと気づきました…。
で、シーケンスを更新する、と。

ただ、空データ表示時間があったとして、その機会損失損害額を考えると
どういう方法がベストなのか今でも分かりませんが…。

ああ、SEへの道は遠いなぁ…。

皆さん、どうもありがとうございました！

Sun、MySQLを買収へ
http://www.itmedia.co.jp/enterprise/articles/0801/17/news005.html

Sun Microsystemsは、オープンソースのRDBMS「MySQL」を開発するMySQLを
総額約10億ドルで買収することを明らかにした。

　Sun Microsystemsは1月16日、オープンソースのRDBMS「MySQL」を開発する
MySQLを総額約10億ドルで買収することを明らかにした。
買収は3月末をめどに完了させる予定。MySQLはIPOを待たずして買収されることとなった。

　MySQLが自社サイトに開設しているブログに、同社のコミュニティ担当バイスプレジデント、
カイ・アーノ氏がその旨を伝えるエントリを投稿したのとほぼ同時期に、
Sunからも正式なプレスリリースが出されている。

　カイ氏はエントリの中で、「オープンソースをよく理解しているSunがMySQLを買収したことは、
MySQLコミュニティーにとっても有益なものになる」と述べている。

　一方、Sunのジョナサン・シュワルツCEOも自身のブログでこの件について言及、
MySQLの顧客に対して買収の完了を待つことなくサポートサービスの提供を開始する予定であると述べた。

　MySQLについては、過去にOracleが買収を試みたことが知られている。
今回Sunが買収したことで、OracleやMicrosoft、
IBMなどとデータベース市場で争うための基盤をSunは手に入れたことになる。

8.3で忌まわしきVACUUMが無くなるって話を聞いたんだが、これって公式にアナウンスされてる？
もしVACUUMが無くなるならば、ようやく使えるDBになるな
速度の問題だけじゃなくて、VACUUMの手間を避けたいがゆえにMySQLに行った人って多いでしょ

> 今回Sunが買収したことで、OracleやMicrosoft、
> IBMなどとデータベース市場で争うための基盤をSunは手に入れたことになる。

(　´д)ﾋｿ(´д｀)ﾋｿ(д｀　)

バキュムはどっかでやってるだけでしょ。
てか普通に空いた時間でやってくれてると思うが、
そんなに面倒かな。

>>148
My SQL使いは Auto VACUUM の存在を知らない人が多いよ
というか、もはやPostgreSQLに目もくれないご様子で…

なんでMySQLの話題ばかりなんだよ。
というわけで
SRA OSS、PostgreSQL 8.2をベースにしたHAクラスタソリューション
ttp://enterprise.watch.impress.co.jp/cda/software/2008/01/16/12025.html
なんて話題を振ってみる。

HAクラスタソフトにはLifeKeeperを使っているとのこと。

>> 150

> ベースとなるPostgreSQLが8.2.5へバージョンアップ。

最近のセキュリティフィックス入ってないじゃん。

まぁデフォルトで autovacuum が on になるから、
「設定不要になった」と言っても嘘にはならないだろう。

「VACUUMが無くなる」という発想そのものは、何も分かっていない証拠。
Oracle で UNDO セグメントを無くせないとのいっしょ。

どうでもいいから日本postgresqlユーザ会、8.3の日本語版早く出してくれ。

Postgre for windowsのチューニングについて教えてください。
64bit版の機器で16GBのメモリをつんだ機器でポスグレをインストール
したのですが、Shared Memoryを1GB以上にするとポスグレが起動できません。
16GBつんでるのでメモリを最大限まで使用したいです。どうしたらいいので
しょうか。教えてください。

>>154
バージョンいくつ？バイナリは32ビット版だよね？

サーバー用にOS:VISTA PC買ったらPOSTGRESQLインスト出来ねえでやんの；
その時オワタおもた。

失礼。ミスた俺は>154じゃない。

Windows 用の 64bit バイナリってあったっけ？
少なくとも配布されているのは 32bit 版だけだったような…。
必ずしも shared_buffers にメモリを全部割り当てる必要は無いから
(OSがキャッシュに使ってくれる) 1GB で我慢しれ。

http://japan.cnet.com/blog/hosting/2008/01/17/entry_25004180/

> 最近利用が増えてきているPostgreSQLと比較しても、約2倍ほど
> 高速に動作します。

デマが流れてるぞ

>>159
だれだ　さぁや　って知ったかライター（インチキ）か。

posgreSQLにもMySQLにも迷惑だ。潰してやれ。

>>160
迷惑だと言えば、MySQLスレに出張して、
やたらとSunの買収について不安を煽っている人間がいたね。
大人げ無いから止めとくれ。>>152=>>158

ゼイタクギガサーバーがあまりにも贅沢すぎるw

postgreSQLはMYSQLと比べて便利すぎる。

MySQLは商用版も出てたし、業界再編の波に飲まれているのでは？

安定して使えりゃどっちでも良いよ

ver.はPostgreSQL 8.2.6です。確かに32bit版のようです。出来る限り16ＧＢ
のメモリを効率的に使用して処理速度をあげたいです。shared_buffers以外
にもどの部分のチューニングをすればいいのでしょうか。教えてください。

アナウンスがまだだけど、PostgreSQL 8.3 RC2。
http://packages.debian.org/source/sid/postgresql-8.3

初心者です。

PostgreSQL のユーザ定義関数を使って和暦変換関数を作成したいのですが

IBMのICU4Cを使ってC++で和暦変換関数を作成しました

C++で作った和暦変換関数を組み込もうとしてるのですが、

C関数でないせいかうまくいきません。



誰か詳しい方教えてください。

C++で作ったって、外部向け関数だけをCリンケージでやったらいいんじゃないかな。

>>156
Vistaへのインストールはコツがいる。
ググってわからなかったらまたおいで。

>>168
まず、どのように「うまくいかない」のか書くべし。

postgres のヘッダで、typename や delete なんかの C++ のキーワードを
仮引数名として使っている箇所があったような気がする。
postgres と繋ぐ箇所だけを別のCソースで書くか、
include 前に #define typename typename_ で誤魔化すか。

c++和暦関数
(cal_func.cpp)↓↓↓↓↓
#include <iostream>
#include <stdio.h>
#include <stdlib.h>
#include "unicode/smpdtfmt.h"
#include "japancal.h"
#include "cal_func.h"

char* cal_func(int year, int manth, int day){

/**** 略 ****/

return target;
}
(cal_func.cpp)↑↑↑↑↑



(wareki.c)↓↓↓↓↓
#include <stdio.h>
#include "postgres.h"
#include "fmgr.h"
#include "cal_func.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(wareki);
Datum
wareki(PG_FUNCTION_ARGS){
int32 year=PG_GETARG_INT32(0);
int32 manth=PG_GETARG_INT32(1);
int32 day=PG_GETARG_INT32(2);
char *t;
t=cal_func(year,manth,day);

text *new_t=(text *)palloc(VARSIZE(t));
VARATT_SIZEP(new_t)=VARSIZE(t);
memcpy(VARDATA(new_t),
VARDATA(t),
VARSIZE(t)-VARHDRSZ);
PG_RETURN_TEXT_P(t);
}
(wareki.c)↑↑↑↑↑


こんな感じで作ってるんですが、
どこがうまくいかないかというと
C側からcal_func()を呼び出して、C++側で西暦から和暦へ変換して
和暦に変換した文字列が返ってこないんです。

わかりにくい文章で申し訳ありませんがよろしくお願いします。

>>172
extern "C"とかで解決する問題？

ヘッダファイルで
#ifdef __cplusplus
extern "C" {
#endif

#include "postgres.h"
#include "fmgr.h"
char* cal_func(int year, int manth, int day);

#ifdef __cplusplus
}
#endif

ってしてるので、そこは大丈夫だと思うんですが。

一応CREATE FUNCTIONってでてるので関数は作成できてるみたいです。

たぶん問題はC++の和暦変換関数から文字列を参照渡しで渡す際にダメみたいです。

ハードディスクがあふれないようにテーブルの行数に制限つけろと言われた。
100年以上運用しても余裕があるっていう資料出したのに仕様に入れてきやがった…
cronで定期的にcount取って制限に達したら古いデータを削除しようと思うんだが、
これぐらいしか方法無いよねぇ？

>>175
トリガ使って都度チェックとか

>>176
insertのたびにcountって重くね？

>>177
どれくらいの行数を想定しているとか分からないから、知らんがな。
そんなこと言われても、方法を提示しただけです。

>>175
行数より実際のディスク容量を監視した方がよくないか？
デッドタプルみたいに、行数には現れないが容量喰うものはあるわけで。

>>167
PostgreSQLの公式ページで正式にRC2が発表された模様

そういえば、8.3ってそもそもは昨年中にリリースするつもりじゃなかったっけ?

00000_1
00000_2
11111_1

といったデータがあった場合、
group byを使って00000をまとめることはできないものでしょうか。
もしありましたらご教示いただけますと幸いです。

GROUP BY SUBSTR(str, 0, 6) とか？

>>179
仕様で行数って書いてあるんだから気にしない

>>172
char *t に対して VARSIZE(t) しているのが間違い。
DirectFunctionCall1(textin) でもしておけ。

>>175
ちょっとトリッキーな方法かもしれんけど…

・serial型の列を作成して、それを主キーにする。
・自動的に作成されるシーケンスにMAX値とCYCLEを指定する。(MAX値は最大行数)
・主キーが重複したら削除するBEFORE INSERTのトリガーを作成する。
・取り出すときはINSERT時間でも入れといて、ソートする。

>>185
何度もすみません。

どんな感じでDirectFunctionCall1(textin)を使えばいいのですか？

DirectFunctionCall1(textin,CStringGetDatum(t));

みたいな使い方でいいんですか？

教えてください。

>>187
「DirectFunctionCall1(textin」でウェブを検索なり、Postgresのソースコードをgrepなりしてみなさい。
もし前例があれば、正しい使い方だと判断すればいい。
自分で考えるのを放棄して教えを請うのは「初心者」だからといって許されない。

自分で調べたり考えたりググったりしない人間は、
DBやらプログラミングやらには激しく向いてない気がしてならない今日この頃

何にも向いていない。
てかあるレベルで止まる。

pg_dumpしようとすると
pg_dump: query to get table columns failed: ERROR: kind mismatch between backends
といったエラーが発生するのですが、pg_dumpを使わずにとりあえず
バックアップできる方法があればご教示願います。

権限は一つのDBに対して与えられているだけで、postgres権限はありません。

>>191
pgpool使ってる？

>>192
はい、使ってます。
色々調べてみましたところ、oid等が2台のサーバでずれている
可能性があるみたいですが、ユーザ権限では今のところどうし
ようもないという結論に至ってます。

せめて定期的にバックアップだけはとっておきたいと書き
込ませていただきました。

pgpool経由じゃなくて、直接PostgreSQLにpg_dumpすればOK。

>>194
アドバイス感謝です。

pgpool経由ではないようにpg_dumpすると言うことは、
各種DBサーバへ入って直接pg_dumpを行うという認識
で宜しいでしょうか。

/usr/bin/pg_dump -h 127.0.0.1 DB > backup.db

DBサーバへログインし、上記で試してみましたが、
やはりどちらからやっても同じエラーが出てしまい
ました。
大変恐れ入りますが、ご教示いただけますと幸いです。

pgpoolへのポートじゃなくて、pgpoolが使ってるPostgreSQLへのポートを指定するのよ

>>195
同じエラーって、それpgpoolに繋がってるんじゃ？
そのエラーメッセージはpgpoolが出してるんだけど。
違うポート番号で立ち上げてるんじゃないの？

>>196-197

PostgreSQL指定ポートというものがわからなかったため、
とりあえず5433で以下実行しましたらいきましたので
取り急ぎご連絡させていただきます。

/usr/bin/pg_dump -h 接続IP DB -p 5433 > backup.db

本当に有難うございます。
また何かありましたら報告させていただきます。

この辺は身体でわかってないと、無理だね。
一歩ずつね。

便乗質問だけど、pg_poolってデフォルトでは5432
ポートで動いているんですか？
>>198は5433でPostgreSQLへ繋いでるみたいだけど。

>>200
そりゃあ、クライアント側の設定はそのままでいけるように、pgpoolは5432でしょう。

＋１するのはセキュリチやラッパでよくあるね。
おらくるでは１５２２とかw

initlocationってなくなっちゃったの？

横からすみません

私はpgpoolじゃなくslony1使ってるんですが、
8.1でpg_dumpしたデータを8.2にリストアするとプロセスが落ちてしまいます。

落ちるのは毎回決まった場所で、
リストア中に｢SET｣という表示が出るあたりで処理が固まってしまいます。
何か心あたりある方いないでしょうか。

漢字使ってる？

>>205

私ですか？
使ってます。

ちなみにpg_dumpのとき文字コードは何も指定してません
-Eつけずに実行しました

んー、Slony-Iの無い8.2に入れて、そこからまたdumpするとか。
8.2は文字コード厳しいんだよねえ、8.1と比べると。

SELECT * FROM table1 WHERE XXX...
という条件で table1 のレコードを絞り込んでいます。
このとき、この条件で絞り込んだレコードに flag=0 カラムを追加して
SELECT *, 0 AS flag FROM table1 WHERE XXX...
とし、それ以外のレコードに flag=1 カラムを追加して
SELECT *, 1 AS flag FROM table1 WHERE XXXじゃない条件...
とし、それを UNION するにはどうしたらよいでしょうか？

>>208
UNIONだろｗ
UNIONじゃなくてもCASEで1,2を分けてもいいかな。

SELECT *, CASE WHEN XXX THEN 0 ELSE 1 END AS flag FROM table1;

PostgreSQL 8.3.0

>>207
とりあえずやってみます

ただ、気になるのが2点ありまして・・・

1つ目はリストアしてるとき
「SET」ってレスポンスが出た直後に固まるところ。

2つ目はOIDが何とかってログ。
以下のログが出てるんですが・・・
-oのオプションとかいるんですかね？

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
LOG: server process (PID 541) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2008-01-23 21:41:45 JST
LOG: checkpoint record is at 0/487008
LOG: redo record is at 0/487008; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 0/597; next OID: 24576
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/487058
LOG: record with zero length at 0/70F1A8
LOG: redo done at 0/70F178
LOG: database system is ready

>>211
2008/02/01にダウンロードできるようになってるけど、
トップ更新もまだだしリリースノートもまだだねぇー。
最終チェック中？

記念あげ

ttp://www.postgresql.org/about/news.918

pgAdmin�Vでリストアするときに

pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 283; 2612 16386 PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: language "plpgsql" already exists
Command was:

というエラーが出るんですけど解決方法ご存知の方いらっしゃいませんか？

エラー出てもリストアはうまくいっているようです。
ネットで調べたところ「手続き言語plpgsqlは削除しておくこと」という文言を見つけたのですが
手順がわかりません。

どうかご教授の程を。

pgadminはバグが多いので、PostgreSQL本体の付属品ではなく、
単品の最新版をインストールすべき。それでもやっぱりある程度の
大きさのSQL流し込むとやっぱりダメなことが多いけど。

ってことで、大きなSQLを叩き込む場合は、素直にコマンドラインの
psqlを使ったほうがいい。

8.3キターー

すいません質問があるんですけど・・・

重複したデータを削除したいのですが記述の仕方がわかりません．．
条件が非常に縛られているので削除方法がわからないんです．．．

このようなデータベース
↓
○テーブル名：main1
・カラム：id, name
○テーブル名：main2
・カラム：id, info
○テーブル名：relation
・カラム：id, main1_id, main2_id, point

以上のような３つのデータベースを作りました．
relationはmain1とmain2の関連テーブルになっています．

ここで問題が起きたのですが，relationにはmain1とmain2のidを結びつけたインスタンスが
登録されています．
なのでmain1とmain2のidの組み合わせが重複することはないのですが，なぜか
存在してしまいました．
つまり，main1とmain2のidが等しいデータが違うidで存在しています．

この重複部分を削除したいのですがわからないのでカキコしました．
消すデータは後ろのidの方のデータを消したいと思っています．

このrelationのデータ数が非常に多いので，手動では時間がかかりすぎるのでSQLで解決したいんです！
どなたか教えてくれませんか？？

一度，重複しているidをすべて１度selectしてからdeleteしても全然おｋです．

>>219

truncate main1;
truncate main2;
truncate relation;

でいいよ。重複部分もちゃんと削除される。

TRUNCATE -- 1テーブルまたはテーブル群を空にする

ただでさえ人少ないのにさらに減らすようなことやめようよ。

>>219 重複が合った場合は relation.id の最小のものを残す
DELETE FROM relation WHERE id NOT IN (SELECT min(id) FROM relation GROUP BY main1_id, main2_id);

もっと効率の良い方法があるかもしれない。

>>221

重複部分「も」ちゃんと削除される
というのは正しいでしょ。やっぱりダメか(笑)

>>222 を参考にするとこんな感じでも書けるのかなぁ。
試してないけど。

create function delete_dup() returns int as $$
declare
r record;
begin
for r in select min(id) as min_id from relation
where main1_id = main2_id
group by (main1_id, main2_id)
loop
delete from relation where id <> min_id and main1_id = main2_id;
end loop;
end;
$$
language 'plpgsql';

select delete_dup();

>>223

あ。最後に return 0; とか書いておいてね。

>>223 むしろ遅くなるぞ。
>>222 がどういう実行プランに落ちるか、イメージできているのか？
relation へ SeqScan 1回で済むような方式でなければ、222を超えられない。

>>225

indexがあるかどうかもわからないのに、どちらとも言えないでしょ。

>219 8.2の初期バージョンはリストアしても完了にならないバグがあるから
何度もok選択するとデータがダブって入る。

今までAccessとSQL Serverしかさわったことがなく、
はじめてPostgreSQLを phpPgAdmin でさわっているんですが、
Accessでいうリレーションの設定は、外部キーの追加　にあたるのでしょうか？
また、リレーション図の作成といった機能、または外部ツールなど
ありましたら教えてください。
よろしくお願いします。

リレーションはSQL文発行するときに毎回作成するんだよ
リレーション図作るならACCESSからODBCでテーブルリンクしてやっちゃった方が手っ取り早いと思う

さっそくのレスありがとうございます。
なるほど、そういう手がありましたか　＞ACCESSからODBCでテーブルリンク
リレーションについてですが、すみません、おそらく
自分が言いたかったのは、参照整合性のことだと思います。
それはやはり外部キーのことでしょうか？

>>230
YES. 参照整合性 = 外部キー制約
http://www.techscore.com/tech/sql/03_04.html
>>229
リレーションって単にテーブルの意味で使うような。

SQLサーバーとアクセスで、年商100億以下の企業のシステムが組めるそうです。まさに、オフコンキラー・エンドユーザーキラーです。

>>231
ありがとうございました！
ようやく分かってきました

バージョン:8.3
costとactual timeを比較しながらSQLチューニング
を行っています。

そこで質問なのですが、「costが激減したがactual timeが早くならないという」
現象は普通起きるのでしょうか？
もし起きたとすれば、それはDBサーバの処理能力が低いことが
原因と考えても宜しいでしょうか？

宜しくお願いします。

どのDBでもそうだけど、コストっていうのは
こうしてそうしてああすればこうなるっていうのを数字にしたようなものなので
それを改善すれば必ず速くなるというものではない。

CostっていうのはSQL文の中のボトルネックを探すのに使うとよいと思う。

助けてください。

8.2.3です。

Debian上で動作しているPostgreSQLサーバの管理をすることになりました。
このＤＢは社内システム用に使っています。

これまでほとんど管理されてなかったサーバなので、まず色々調査しなければなりません。
しかし前任者が先日、事故で死亡してしまい、情報がありません。
ログインアカウントは現在、メモなどを探しているところなので、まだサーバに入れていません。
私はこれまで、単なるPGをしていたのでDBの管理なんて何をしたら良いのか分かりません。

まずは何をしたら良いのでしょうか？
HDDの容量見たり、topで確認したりはするつもりです。
バックアップがどうなっているかも調べるつもりです。
それと、バキュームしないとまずいよね、と聞いたので、まずはそれをするつもりですが、
他にすることはありますか？

止める時間と別マシンがあったら、鯖からHDDすっこぬいて別のマシンにつなぎ、別マシンのルート権限で
旧マシンの/etc/passwd の周り書き換えるなり移植するなり。
つーか全部ここで言うことじゃねえや。

>>237
アドバイスありがとうございます。
アカウントが見つからなければその方法で試してみます。
なるべく止めたくないので、もう少し頑張ります。

>>236
アカウントやパスワードは、
使用しているアプリケーションのソースを見たら検討つくのでは？
ログインのソースとか。
あと日次や月次などバッチ処理などが深夜などに、
別途、稼動しているかどうかも確認したほうがいいでしょう。
バキュームやバックアップもバッチで自動で行っているかもしれないでしょうし。

>>235
ありがとうございます。
必ず速くなるものではないんですね…勉強になりました！

ぱっと思いつくだけでも

rootになって全ユーザのcrontabチェックしてバックアップとバキュームしてないか確認。
ついでに、バッチでDBアクセスがないか確認。(時間も控えとく)
DBの設定みてオートバキュームしてないか確認。
リモートから接続許可してる場合、許可してるマシンからもバックアップ、バキュームしてないか確認。
資料探しまくって手動cronがないか確認。(○曜日に出社したらなにかするとか)
クライアントの利用時間帯やらをヒアリング。

わかったことは全部ドキュメントに残す。

>>239
あ、たしかにアプリケーションのソースを見れば分りますね。
会社に行ったら確かめてみます。

>>241
手動cronは私の発想からは出てきませんでした。
ありがとうございます！

PHPやってて
GETで持ってきた引数をデコードした文字列を問い合わせ文のWHEREの条件に入れるとうまくいかないんですがどうしてでしょうか？

アドバイスよろしくお願いします

相手に伝える言葉の使い方が分かっていないから

SQL文を勉強しましょう
SQL文を検証しましょう
二行ですむな

>>243
シングルクオートの処理が間違ってるんじゃないか？

MLのインストールの話題だけど普通は自前コンパイルなのか？
CentoOSな俺は ttp://yum.pgsqlrpms.org/howtoyum.php で入れてる

Solarisだから普通にビルドしてるなあ
最近も64ビットでビルドするからなおさら

とりあえず報告を。
バージョンアップうまくいきました。
原因はSlony-�Tでした。
207さんの言ってたことに近かったんでしょうか。

Slony1をアンインストールして、
その後pg_dumpallでバックアップ取る
8.2をインストールしてリストアしてから
再度Slony1をインストールとやって動かせました。

バージョンアップと冗長化一緒にやるのって
えらいめんどくさいものなんですね。。。

BOOL型をINT型に変換するような関数はありませんか？

どういう状況で使うか分からんが、case文でやっちゃってみてはどうか

単にキャストすれば？
=# SELECT true::integer, false::integer;
int4 | int4
------+------
1 | 0

2回レコード全体を検索するのは無駄なので、count(*) でレコードを数えるのとレコードのデータ取得を同時にやる方法ってないでしょうか
PostgreSQLは8.2.6、PHP5.2.4から操作しています
なるべくDB側の機能を使って対処したいです

>>253
pg_numrowsでは駄目？

>>254
それだとoffset limitで指定した区間のレコード数しか出ないので
全件中○〜○という風に表示するために総レコード数を数えたいのです
あとPDOでやってます

>>255

PDOだったらこれでどうよ

$offset = 0;
$limit = 10;

$sql = "SELECT * FROM hogehoge ORDER BY foobar";
$st = $conn->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$st->execute();
echo $st->rowCount() . " rows.\n"; // 件数
$i = $offset;
while($i<$offset+$limit && $row = $st->fetch(PDO::FETCH_BOTH, PDO::FETCH_ORI_ABS, $i)) {
$i++;
// 処理
}

>>255
よくわからないけど、こんな感じとか？

select
code as コード,
hinmei as 品名,
(
select
count(code)
from syohin
) as 総数
from syohin
where
code=1

offset と limit を使用するなら、こんな感じ？

select
code as コード,
hinmei as 品名,
(select count(code) from syohin) as 総数
from syohin
order by cocd
offset 0
limit 10

>>258
誤字訂正
×order by cocd
○order by code

offset と limit を使用するなら、こんな感じ？

select
code as コード,
hinmei as 品名,
(select count(code) from syohin) as 総数
from syohin
order by code
offset 0
limit 10

>>259
それでいいのですが、whereでの条件が多くなると遅くならないのですか?
検索結果をキャッシュとかしてるのだったら問題ないのですが

>>260
心配だったら検索結果をtemporary tableにでもキャッシュしてみたら？

>>260
詳しくないので、遅いかどうかはわかりませんが、
1つのテーブルで100万レコードからの抽出だと、
1秒ちょっとで、抽出できます。

気にするほどの付加増ではないようなのでサブクエリでやってみます
temporary tableとの比較もそのうちやってみたいと思います
ありがとうございました

vacuum verbose analyzeで出力されるログをjdbcで取得する方法はありますか？
javaプログラムからvacuum実行して結果を管理画面とかで確認したいのですが。

postgres.confでinfoログも出力するように設定してログファイルを
見に行かないと駄目？

BOOLをINTに変換する関数はありませんか？
TRUE→1
FALSE→0

MySQLではBOOL+BOOLでINT型に自動変換されますが、PgSQLでは変換されません

>>265
>>250-252じゃいかんのか？

case文使うか関数作る方が調べるより早いと思う。

うお、ちょっと前に同じような質問がっ！
ありがとうです

> MySQLではBOOL+BOOLでINT型に自動変換
キモすぎる…

VBとCを両方やったのでFA:LSEが0か‐1か今一得心が行くことがないのでcase文で対処

1000万件ほどのある行から全文検索を行いたいんだけど何かお勧めない？
`tbl1` || `tbl2` || 'tbl2' … `tbl10` LIKE '%検索ワード%'　とかだと、検索結果まで数秒〜数十秒まで時間がかかってしまうことがあります。

単一テーブルでLIKE '%検索ワード%'ならもうどうしようもないんじゃないかな。
Oracle+Times10にするとか、そこだけ全部メモリに持つかしないと。

つかうちだと500万件のテーブルをselect count(1)するだけで70〜90秒くらい
かかるんだけど、1000万件でそれなら優秀なんじゃない？

ちなみにshared_buffer増やしても、検索したoidに対応するレコードがキャッシュに
あったら引っ張ってくるだけで、select countには意味ないんだよね？

>>271
tsearch2というのがあるらしい。

googleのアプライアンスサーバでも導入するほうがよいんでは？

プログラムロジックを介さず、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)も許容しちゃうかな…

というのはさておき、何かもっとスマートな手法があれば…何卒ご教示お願いします。

>>275 nを厳密にしたい目的はなに？
表現の曖昧さを避けるだけなら、冗長化した形で CHECK(一方のチームID < 他方のチームID) とか。
チームIDで検索したいだけなら GIN 使う手もあるし。

>>275
チームIDはチーム情報のマスターに格納してあるんでしょ？

>>276
まさに、その何かの試合に関するテーブルを作ろうと思ってまして、
その際必ず試合1に対してチームが2になります。

カードを外に出したいのは、集計のためだったりします。
あるチームを基点に試合を検索する場合、一方か他方かがそのチームで、
検索されたデータからもあるチーム以外のチームを見つけなきゃいけない、
というのが面倒な気がしました。
一方外に出しておけば、おそらくクエリだけでなんとかなります。

検査制約とか配列を使うのはアリかもしれません。
ありがとうございます。


>>277
明示しませんでしたが、チームIDはチームテーブルのPKです。

>>278
サッカーなんて単語が出てたから、PKってそっちの方かと思ってたよ。
いや、なにやらごちゃごちゃ書いてあったから詳しくは読んでないんだけどね。

ソフトのロジックが複雑でも、
テーブル設計は、標準的でシンプルなほうがいいかもね。
メンテなどしやすい方が便利な事が多い。

>>271
>>273
tsearch2よりはludiaの方がいいんじゃないか。
tsearch2は形態素解析。ludiaはN-gram。
形態素解析だとLIKE %% と違った結果になるぞ。

LIKEやN-gramなら漏れが無いってわけでもないので、
割り切れるならば形態素解析も使いどころはある。適材適所で。

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でもユーザーが作れない理由と対処方がわかりません。
どなたか教えていただけないでしょうか？

>>283
>CREATE ROLE name WITH PASSWORD 'pass' LOGIN;
これはpsql等で実行するもの。

>createuser -P name
こっちはシェル(bash等)で実行するもの。
でこっちは実行出来たっぽいね。userつってもあくまでdb用userで
psql -U name -d dbname
と言う具合につかう。

ちょっと初歩的すぎるので、何か本でも買って読んだ方がよさげ。

>>284
丁寧な解説＆ご指摘ありがとうございます。
どこで使うかをわかっていないせいで、知識がごちゃ混ぜになってたんですねorz
一からきちんと勉強しなおします。ありがとうございました。

8.3.0 for Windowsです。

ふとログを見たら、plugin_debugger.dllというのが頻繁にロードされていました。
postgresql.confを見たら、
shared_preload_libraries = '$libdir/plugins/plugin_debugger.dll'
がデフォルトで有効になっていたんですが、このplugin_debugger.dllとは何者なんでしょうか？

プラグインのデバッガじゃね？

>>286 PL/pgSQL のデバッガだったはず。
使わないなら単なるリソースの無駄なので外すべし。

plpgsqlのデバッグって出来たんだ……！

pgpool使用時に、100回に1回くらい通常時1秒程度のplpgsqlによるストアドの返答に
数十秒、長いときだと数分かかるんですが、原因がまったく検討つきません。

アクセスは一秒数回ありますが、autovacuumの実行なども発生には関係ないようで
一度killしてから同じSQLを走らせると1秒程度で終わります。
また、ストアド実行中に同じストアドを別の引数で呼び出しても、全く問題なく実行できます。
ストアド自体にはみたところ問題はありません。
原因や解決策などに心当たりがございましたら、ご教示ください。

自分は、詳しくないので見当つきませんが、
アドバイスを受けるのでしたら、
少なくとも、バージョンや環境は提示した方がいいでしょう。

申し訳ありません。
FreeBSD6.3、amd64
PostgreSQL8.3
pgpool-II version 2.0.1
です。

また、稼動を続けるとCPU使用率が増加していき、postgresを再起動すれば治ります。

負荷が高くなっていくのは、運用では困りますね。
原因が見つかるといいですね。
8.2.x など旧バージョンと比較して試してみるのはどうでしょうか。

8.3もpgpoolII 2もまだまだ枯れていないからなぁ。

>>290
問題を切り分けるのに、PostgreSQLだけで同じ事象が
発生するか試したら？

そうだな。pgpool切り離しと、ネットの影響もあるからlocalhostだけでやるのも

ファイアウォールとＳＥＬＩＮＵＸを切ってみては？あとは
ＰＨＰとかから呼んでるなら、永続的な接続を使ってると色々
リソースを食い続けるので、非永続で。ＣＰＵ使用率はpostgreSQL
が使ってるのかな？　８．３に興味あるけど、まだ怖いね。

pgpool-II Ver 2は、こういった分野のソフトにしてはバグが多い印象。
ver 1　を使ったほうがいいかもww

すみません、SQLを改善したら解決しました
問題きりわけのためにplpgsql内のWHERE条件を分割して実行したところ
再発しなくなりました。
元のSQLが間違ってたようには思えないのですが…

pgpooｌをはずして、4BSD、ULEどちらのスケジューラーでも試しましたが
問題はpostgresql単体の問題のようです。

時間かかった時は不適切な実行計画が選択されちゃってたとか？

>>298
よかったですね。
旧バージョンでは問題なくても、
新バージョンで問題が発生する場合もあると思います。

>>298
参考のために、オリジナルと分割後のSQLを教えて。
もしPostgreSQLのバグなら、誰かが対処してくれるかもしれないし。

調査を続けていたところ、NULLが絡んだときにSQL自体が
PERFORM 1 FROM target_table
になっている場合があることに気がつきました。
これの応答に時間がかかっていたようです。
お騒がせしまして申し訳ありません。
SQL間違いとはお恥ずかしい…

すみません、920ではなくて290です。

http://www.postgresql.jp/
> 重要なお知らせ：
> 2月24日にwww.postgresql.jpサーバが部外者によりクラックされていた事が判明致しました。このため、サイトは安全な別サーバに移動し、トップページのみ表示されるようになっています。
> 現在、復旧作業中です。今のところルート権限を取得された形跡は発見されていません。 詳しい情報は後日公開致します。
> ご迷惑とご心配をお掛けする事をお詫びいたします。

うわぁ…
postgresql.jp って、SRAが事務局でサーバも提供してたのは、もう昔の話?

うげ、8.3って数値から文字列の暗黙の型変換がされないのか。
怖すぎてアップグレードできん。

2月27日 9:13　外部よりjpug.postgresql.jp(www.postgresql.jp)よりsshブ
　　　　　　　　ルートフォースアタックを受けていると連絡が入る
　　　　 10:20　pscan2が走行しているのを確認し停止させる
　　　　　　　　図らずもsshdのパスワード認証が有効になっていたため、ブ
　　　　　　　　ルートフォースアタックで作業用アカウントのパスワードが解
　　　　　　　　析され侵入を許していた。
　　　　　　　　sshdのパスワード認証を禁止し、RSA認証のみとした。
　　　　 17:00　改ざん等がないか確認開始
　　28日 14:00　改ざんと個人情報の流出が無いことを確認した。
3月 1日　　　　告知用サーバ運用開始

>>306
そんな怖くないだろ

>>306
俺はchangelogを読まずに酷い目に遭ったぜ・・・orz

>>308
どの言語出身かで分かれるだろうなｗ

もともと date_column = 2008-03-01 っていうミスをしたという話があって、
date_column::text = (2008-03-01)::text = 2004::text
と解釈されるのはまずいとして、暗黙の型変換は削られたらしい。

ミスらないためには悪くは無いと思うけどね。
必要なら自分でキャスト追加すればいいだけだし。

質問させてください。

pg_dumpall でデータベース全体をバックアップした後、
このデータを元に、部分的な復旧を行う事は可能ですか？

手元に pg_dumpall で取ったデータしかなくて、
それを別サーバに復元したいのですが、
全部ではなく一部分だけ復元したいです。

調べた所、丸々復元する方法は見つけたのですが、
部分的な復元方法を見つける事が出来なかったので
よろしくお願いします。

部分的な具体例を示した方がいいと思います。

テーブル単位なのかレコード単位なのか、
カラムや部分文字列なのか。

>>312
バックアップ時のオプションにもよるだろうけど
バックアップされたファイルは基本的にテキストファイルだから
必要な部分だけに加工してrestoreするか
一時的にどこかに全てrestoreして、必要な部分だけ
個別にpg_dumpし直すか、だね。

なんかCLUSTER発行しても並べかえてくれない気がするんだが…
バージョンは8.2.6で

ローカルでpgsql立てて全部入れてから改めて必要な部分を戻せばよいのでは

質問です。

カラムの並び順を変えるには、この方法しかないのでしょうか？

テーブル 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には、指定したカラムとカラムの間にカラムの挿入などができたと思うのですが、
ポスグレでは、そのような機能はないのでしょうか？

よろしくお願いします。

無いんじゃないかなあ
そういう時はテーブル丸々コピーして、新しい並びで空のテーブル作って
select insertで一括挿入っていうのをよくやるけど

VIEW 使えば？ そもそもRDBMSでカラム順に依存したSQLを書くのが間違い。

うん、俺もVIEW使うのが正解だとｵﾓ。
>>319の言うことは正論。

>>317
純粋に、なぜ列の並び替えが必要になるのか知りたい。

select * from tabの結果が気持ち悪いからじゃないの？
俺も後からカラムを追加するときに、一番最後じゃ気持ち悪いときが結構ある。

>>322
気持ち悪いのは人だけでしょ。
プログラムを実行するコンピュータには何の関係もない。

>>322
つか*で取るからそういうことになる。
列指定汁ｗｗｗ

>>323 実行する人の精神衛生状態は作業効率に直結する

>>325
なんて言っている人の作業効率なんてたかが知れている

すんません、vacuumに関して教えてくださいな。

vacuum -a -f -zは毎日夜中に一度やるとして・・・

・オンライン中にvacuumしていいのですか？
（極端に遅くなるとか、壊れる等はありますか？って意味が一番強い）
・いいのであれば、vacuumのオプションは何が最適かしら？

土日は更新メイン。平日は参照メインなシステムです。
バージョンは、8.2.6です。
よろしくお願いします。

MyISAM の ORDER BY 無しのデフォルトの並び順での取り出しは、
確かに挿入順であって結構速い気がする。
この動作に依存して組む人たちもいるんじゃないだろうか。

>>328
それ行の話でしょ？　いま問題になっているのは列の並び

vacuumは遅くなるから夜中にやってるなら
それで良いと思うけど。

うちは随時更新、参照入るシステムだけど、
処理重い時間にvacuumやると結構やばい。

vacuum１回で足りないと感じるのは何故だろう？
メモリが十分にあるならチューニングしたらどう？
ttp://www.thinkit.co.jp/free/marugoto/2/1/11/

autovacuumはどうなのか知らん

世の中にはソースのないプログラムとかもあるからなぁ。
データベースの列の並びがちがうだけで動かなくなる糞プログラムなんかに限ってソースが無かったりする。
俺は今またそれに引っかかったよ。　　　ｏｒｚ

そんなプログラムにこそ、カラムの挿入なんてやったらダメなんでは。

>>327
vacuum -a -z だけで十分。
VACUUM FULL は必要ないよ。

オンライン中の VACUUM で DB が壊れることはないし、
極端に遅くなることもないけど、負荷が気になるなら
vacuum_cost_delay とか VACUUM のコストを弄ってやればいい。

create table hoge( foo integer not null );
create table fuga( bar integer not null check( bar < foo ) ) inherits( hoge );

こんな感じに継承元と先とでの数値の検査制約って出来ないんだっけ？

>>334 まったく問題なくできるようだが。

>>330,333
ありがとう。

>vacuum１回で足りないと感じるのは何故だろう？
うーん、一個件数の多いテーブル（100万件ぐらい）があって、
そこが土日の更新の対象で且つ、結構他のテーブルとjoinしてるんですよ。
一応、explainで見て、indexの見直しと対象SQLのチューニングをして、
大分早くなったんだけど、１年で100万件程度増えるので、
いつかはまた遅くなるんだろうと感じてます。
なので、手軽？に早くする手法はないかと調べていた訳です。

オンラインのVACUUMで壊れないのであれば、
一度負荷と相談して試して見ます。
ありがとさんでした。

ところで、V8.x系のメモリチューニングで、1GB以上共有メモリを取っても、
メモリマネージメントの都合で逆に遅くなるってあるんだけど、
やっぱりそういう物なんですかね？
サーバー4GBにしたのに無駄？

>>336
パーティショニングすれば？

> サーバー4GBにしたのに無駄？
共有メモリ以外はOSキャッシュとして使われるから、
まったく無駄にならん。

>>337
レスありがとう。

パーティショニングを今度試してみるダス。
日々の売り上げデータに近い構造なので、
効果は大きそうな気がします。
大抵の集計自体も長くて３ヶ月ぐらいだし。

ありがとうございました。

共有メモリ１Ｇ以上だと遅くなるのってどっかに書いてある？

> 共有メモリ１Ｇ以上だと遅くなる
というよりは、単に増やせば増やすほど速くなるわけではないってこと。
DB/OS 2層でキャッシュするよう設計されているから。
他のデータベースでも似たようなことはよくある。

>>339

8.1のチューニングのページに書いてあった気がします。自分の8.1
での経験ですが、２０Ｇとか指定すると、小さなテーブルのdrop table すら
１秒くらいかかった記憶が、、、。


>>338

おなじく8.1での話ですが、パーティショニングしたテーブルをjoinす
ると、selectがシーケンシャルになって激遅になるケースがありました。
同じ構造で同じＳＱＬでも8.3はインデックスを使ってくれるんですけどね

>>341 たしかに drop table は遅くなるかも。メモリ全部舐めるから。
drop table を高速化する目的でチューニングすべきかは疑問だが。

temp tableしまくるなら効果ありそうだなｗ

>>341
> ２０Ｇとか指定すると、

実メモリはいくら積んでたの？

>>343 temp table はローカルバッファなので関係なかったりする。

>>339
http://itpro.nikkeibp.co.jp/members/ITPro/oss/20050402/158372/?P=2

ここ以外にも、具体的な数値を上げて書いてあったところが
あったと思うけど、おもいだせませんでした。

drop table だけじゃなくて、delete して insert する処理も、もっさり感が
ありました。０コンマ何秒の処理だったけど、共有メモリ１．５Ｇ設定時の
２倍くらいの所要時間だったイメージ。ＤＢ全体のサイズは６Ｇ程度です。

>>344

会社のサーバなので、たしか３０Ｇ台でした。

>>347 そんな豪勢な環境はめずらしいからなぁ。
共有メモリ20GBなんて大きな設定は、テストが足りてないのかも。
4GBを超えるとちょっと心配だな。
あまらせてもOSがキャッシュとして使うから、無駄にはならないし。

>>313>>314>>316
亀レスすいません。
>>316の方法で行く事にします。
どうもありがとうございます。

数百万件以上など、どんどん増えていくテーブルに対して遅くなっていく場合の対処ですが、
日付で管理できるテーブルなら、
例えば、
今月とそれ以前のテーブルに分割してみるとかは？
バッチでレコードの移行を行う必要が出たり、
プログラムの変更が必要になりますが。

その場合、veiwで対応ということが考えられますが、
例えば、レコードが少ないテーブルAとレコードが多いテーブルBの連結を記述しているveiwの場合、
テーブルAにINSERTなど変更が加わる際、
veiwのレコードが多いテーブルBの影響を受けて、多少遅くなるということはあるのでしょうか？
あったとしても、気になる程度じゃないのかな？

veiw

失礼。よく間違えるんですよ。(^^;

>>350
千万件ぐらいならclusterかければ直ぐに結果返ってくるよ
一億件は試した事ないから判らんｗ

>>353
ありがとうございます。
やったことはないので、試してみます。

現在、Group by など複数のSQLを一度に発行しているので、
それが完了するのに、20〜30秒くらいかかっています。
さらに早くなるとストレスなくなるなぁ。

view って、select など、参照する時だけにしか使用されずってことで、
insert や update には、全く影響はないのかな？

あ、view って、insert 時など、キーの重複などの整合性をチェックしてたっけ？（＾＾；
なんか、そんなことがあったような。。。

pgpool3.2のバグで、FreeBSD4.11上でmakeできないらしいので
3.1.2を入れようと思うのですがどこかでげとできますか？
bugfixした3.4.1をmakeることも考えたのですが、稼動中のサーバに
突っ込むので開発版は避けたいところです。

8.3.1 & 8.2.7

>>350
適切なindexを介してアクセスする場合は対数オーダーだから、それが可能ならば
1テーブルで済ませる方が絶対有利。
どうしてもfull scanが必要となる処理ではテーブル分割も考えられるが、
union viewを使う場合、ちゃんとプランナがそれを認識してあらかじめ不要な
テーブルを実行計画から除外することが出来ないとかえって遅くなる。

>>359
削除のことも考えた方がいい。
テーブルを分割する最大のメリットは、古いデータの一括削除だから。
絶対に削除しないならば、確かに分割する利点は薄い。

www.postgresql.jp 繋がらない(´･ω･｀)

>>361
未確認情報だけど、またクラックされたから復旧中らしいよ。

>>362
その話がMLに流れたって、スラドのアレたまで見たよ。

>>359-360

どうも。
１テーブルだと、どんどん増えて数百万レコード件以上になり、
１画面の表示に２０〜４０秒はかかってしまいます。
分割すると、数千レコードなので、１〜２秒以内で終わります。
将来は数千万レコードにもなり、もっとかかってしまいます。

ひとつのSQLで済むまない１０以上のSQLの処理を行っている情報てんこ盛り画面なので。。。

>>364

数千レコードで１〜２秒の処理って、、、どんなＳＱＬなんでしょう？
処理範囲分のデータを一時テーブルに取り出して使えば、それで
速くなったりはしないですか？

>>364
正規化されていないってオチじゃないよね？
複雑なデータ取得でカーソルすら使ってないとかだったら
単純に技量不足だと思うよ

>>365-366
どうもありがとうございます。

>>365
合計で１〜２秒ですから、
ひとつのSQLで、0.1秒〜0.2秒以下ということですが、それでも遅いですかね？
ほとんど全部が、Group by 使用のSQLです。

一時テーブルへ抽出ですか。
普通のテーブルへInsertでしょうか？
その方法はメモリなど特殊な方法でしょうか？
一時テーブルへ抽出しても、複数同時アクセスが頻繁にあるので、
同じテーブル利用にならないよう個別利用になるよう考慮する必要があります。
同じテーブル利用で重複しても数万レコードですから、
Delete & Insert が早ければ、効果あるかもしれません。
しかし、頻繁に画面更新を行うので、Insertが多くなり、意味があるかどうか。
頻繁にバキュームしないといけなくなるのかな？

>>366
カーソルは使っていません。
カーソル利用とSQLでどのくらいの差があるのか知りませんが、
１０倍以上と劇的に早くなるようでしたら、今後検討してみますが、
はっきり言って、管理と工数のことを考えると面倒ですね。
ただ、カーソル制御はわかりますが、
SQLをどう、カーソル使用で置き換えて利用するのか、勉強不足で、わかりません。

正規化とは、どの意味で、言われているかわかりませんが、
PostgreSQL固有で言われるものでしたら、正規化はしていません。
もちろん、冗長データはありません。

>>365-366
次に、ひとつのSQLを紹介します。（改行が多いので）
テーブル名やカラム名は、変えていますが、
生のSQLです。
まだ未熟なので、SQL自体を見直す点もあるとは思います。
テーブル詳細を紹介できないので、わかりづらいと思いますが、
table001が１００万レコード以上で、
約、1.2秒〜2.0秒かかります。
数千レコードでしたら、
0.2秒前後です。

このようなSQLが１画面に１０以上あると合計、２０〜４０秒以上になってしまうので、
よく使う該当レコードをテーブルを分割して数千レコードにすると、合計１〜２秒で収まります。
テーブル設計自体やインデックスの見直し、チューニング次第でまだ高速になるのは間違いないと思いますが、
将来的に、数百万、数千万レコード以上になる前提では、
テーブル分割で１〜２秒の現在と同様に早くなるとは思えないので、検討もしていない現状です。

（改行が多いので続き）
------------------------
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 コード

（改行が多いので続き）
------------------------
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
------------------------

>>369-370
これじゃ遅いだろうね。
code002のカーディナリティが低くてdata001に関数インデックスも
設定していないなら、そのクエリは table001 の seq scan が発生
するというのはわかるかな？
検討する気もないんだったらどうでもいいが。

> 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' にするか。

一般的な方針としては、文字列型を減らす（意味にあう他の型を使う）のが良い。

>>367

一時テーブルですが、

create template(?) table *** as select * from table001 where ***

とかで、必要な範囲のtable001のデータを持った一時テーブルをメモリ上に
つくれます。他の接続とは干渉しないので、複数処理とかは気にせず
でＯＫ。delete も必要ないです。pgpoolとか使ってるなら、使用後に dropが必要。
んで、一時テーブルを使って処理すれば、とりあえず簡単に速く
なると思うけど

>>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は使用していません。今のところも予定はありません。

>>374 の補記
>致命的欠陥ですね。

は、私の設計、SQLが欠陥という意味です。念のため。

>>375
大丈夫。その意味が分かる人間ならクエリを見た時点で分かっていると思うぞ。
まあ何だ。頑張れ。

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;

>>377

explain してみるといいのでは。

360万件のテーブルAをselect count(1) するのに
85秒もかかっていて悩んでいます。別の730万件のテーブルBは11秒でした。
テーブルAデータ量は1GBは超えていると思います。テーブルBは数十MBくらい。

環境:
OS:Linux(FC7)
PostgreSQL8.3
Core [email protected]
Mem:4GB (認識は3.6GB)
設定
shared_buffer 1500MB
work_mem 256MB

shared_bufferは大きくしすぎても遅くなるとかの情報があったのですが、
なんか速くする設定とかありますか？

>>378
explainの結果をどう見たらよいのか良く分からなかったのです(DB毎に違い過ぎますよね)。
検索していたらよさげな情報を見つけたので、explainしてみます。ありがとうございます。
SQLiteやMySQLのexplainよりも実用的なようですね。

>>379
primary key をはっていないのでは？？

>>379
単にテーブルサイズが大きくて、ディスク読み込みが大量に発生しているからでは？
キャッシュに乗った後の速度で良ければ、synchronize_seqscans = off を試してみると良いかも。
他に、更新を繰り返したせいでテーブルが太っているならば、
いったんCLUSTERなりVACUUM FULLなりをかけてみるとか。

>>380
何の効果も無いぞ > primary key

すいません8.2.5でした
>>381
synchronize_seqscans = offは試してみます。と思ったけど8.3からなのかな。
ディスク読込のせいなのはほぼまちがい無いんだけど、shared_buffers割り当てを
増やしてもほとんど解決しなかった。もしかしてpostgresのキャッシュって、
queryでoidを読み出して、そのoidに対応するデータがキャッシュされてれば
キャッシュを見るだけなんじゃないのかなあと思ったんだけど、それで合ってる？
そう考えるとメモリはOSのディスクキャッシュに回した方がいいんじゃないかなとも
思ったんだけど。

vacuum fullは一回途中までかけたんだけど、20分くらい返ってこないのでやめた。
365d24hサービス中なので厳しい。vacuum analyzeは毎日かけるようにしたけど。

365日24時間稼動しているなら、
まずは、テストできる予備機は必要ですね。

あとはサーバーの再起動とか。。。
365日24時間稼動で、それも不可能ならメンテ期間しか無理ということになりますね。

>>382
ぜんぜんちゃう。oidなど使わない。

テーブルBの「数十MBに11秒」でもものすごく遅いので、環境がおかしい気がする。
100MBで10秒ならば、1GBで100秒かかるのも仕方ないでしょ？

あと、VACUUM FULLはアホのように遅いので、やるならCLUSTER。

>>383
予備機はあるけどスペックが違うので大分遅いんです。
本番データは2GB強あるのでインポートするのも一苦労だし

>>384
そうなんだ。じゃあshared_buffersを全データが載るくらい増やせば
劇的に速くなると思うんだけど、postgresql8でも1GBくらいに性能ピークが
あるっていうのはなんでなの？

>100MBで10秒ならば、1GBで100秒かかるのも仕方ないでしょ？

select * じゃなくて select count(1)だから容量では単純比較できないと思う。
730万件が11秒ってなかなか優秀だと思ってたんだけどものすごく
遅いん？うーん。このスペックなら何秒くらいが妥当だと思う？
数十万件のテーブルから複雑な検索条件で検索かけるのとかは
軒並み1秒以内で終わるし、テスト環境(これもLinux)にくらべたら大分速いから、
環境のせいじゃないと思ってたんだけどなあ。

見当外れなこと書いてたらスマソだが、
select count(*)にはできないのかな？
Postgreは知らんけどOracle8iではえらい差がでるよ。
count(1)ではDistinctのカウントになるので重複値の検出でえらい時間がかかる。
全件count(1)なんてやっちゃうと全件の中間テーブルを作ろうとしたりして。

>>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

>>386 残念だが見当外れ。
count(*)とcount(1)はまったく同じ動作です。

>>388
あれ？同じ動作だったんだっけ？
昔なにかでcount(*)よりcount(1)の方が良いって記事をどこかで見た気がするんだが・・・。
勘違いか？

>>387
ごめんそのpg_size_prettyを知らなくて、データのバイト数(1レコード36バイト)に
730万をかけて20〜30ＭＢかなあと思ったんだけど、一桁間違えてた。
それやってみたら485Mだったよ。

360万件の方は3587MB
トータルのデータ領域が広すぎて遅くなってんのかな。
shared_buffersも1.5GBじゃ全然収まってないってことだね。
サービスインからしばらくVACUUMかけてなかったので、一回くらいは
VACUUM FULLかけたいんだけどね。

CLUSTERは調べたらテーブルロックしちゃうんだね。
夜中やってみるかな

>>389
ユニークインデックスを持った列名の方がいいという記述なら見つけた

http://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink6
http://oracle.se-free.com/tuning/B1_wild.html

でもって今日は325万件なんだけどやってみた
1. count(key) 76秒
2. count(1) 65秒
3. もう一回count(key) 63秒
4. selct count(*)が83秒

1の実行でキャッシュに載ったのかな。アクセスが割とある時間帯なので、
別要因で変動しているかも知れないけど

>>389
他のDBMSだと効果があるのかもしれないけど、PostgreSQLに関しては関係ないのですよ。
あんまり鵜呑みにしないほうが良いのですよ。

見当違いかもしれないが、HDD壊れてると遅くなったりしないか？

>>391

マシンの物理メモリ量と、DB全体のサイズの問題なのでは？メモリを
８Gくらいにすれば、あんましHDDにアクセスせずに済んで早くなると
思います

>>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)
　って載ってるし、バージョンアップをすすめます。

　参考
　ttp://www.postgresql.org/docs/8.2/static/release-8-2-7.html

・ＯＳ再起動も、１ヶ月に１回くらいしたほうが良いのでは？

ソフトやハードで力技で解決するよりは、
　・VACUUMをちゃんとする運用にかえる
　・count()を多用しない設計にする
ほうが重要だと思うのだが・・・

8.3にしてVACUUMの必要性をなるべく減らすという手もある。
んまぁ、>>396の言うとおり、時間がかかるからVACUUMはしないというのは
腐った運用としかいいようがないけどね。

テーブルは以下のようになっています。

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;

みんなありがじゅー。
VACUUM ANALYZEは毎日やってる。最初AUTOにしてたんだけど、
毎日10万レコードくらい追加/削除するのでデイリーでやるようにした。
count()するのは、一覧表示の時にまず全件カウントしてページング処理を
したいから避けられない。
HDDが遅いのはアレだと思ってたので、次のシステムではRAID0+1を提案した。

で、>>395の
・メモリを８Ｇくらいは積む。
・shared_buffersは１．５ＧのままでＯＫ

っていうのは、残りの6GBはOSのディスクキャッシュにおまかせするってこと？

>>398
idとup_idの関係がイマイチ分からんのでkwsk

>>398
てきとーに、
select up_id, max(updatetime) where tablename
gourp by up_id
と書いてみるテスト

あ、whereじゃなくて、fromだorz

あ、gourpじゃなくて、groupだ。
てきとーすぎるorz

ありがとうございます

>>401
idは主キーで、特に今回は使わないです（すみません）
up_idは他のテーブルと結合するためのidです（他のテーブルの主キーつまり一対多のテーブル）。
up_idはユニークではないので、up_id毎に複数レコードが存在します。

>>401
そのSQLでup_idとupdatetimeのリストは作れるのですが、
dataのカラムが取れないので、困ってしまうのです。
他のDBだと（本当は文法違反であるものの）、
取得カラムにdataを追加するだけで欲しいものが取得できたりするのですが、
PostgreSQLだとgroup byにもdataを追加しなければいけないので、
dataまで同一じゃないとうまくグループ化されません。

>>404
up_id別の最新updatetimeを取得できるなら、
そのSQL(をVIEWにするなりして)と
元テーブルのJOINを取ればいいだろ。

まぁ、そういうことだな。

>>404
何に困ってるんだ？
select tablename.*
from tablename
join (
select up_id,max(updatetime) as updatetime from tablename group by up_id
) tmp using(up_id,updatetime);

>>401,405
ありがとうございます。

ということは、自分のテーブルに自分のテーブルを
up_idとupdatetimeで結合する感じですよね。

なんか、こう珍しくなさそうな検索条件なので、
もっと単純な解決方法があるのかと思いまして。

>>407
ありがとうございます（すれ違いました）。
>>408の通りですが、usingという書き方は知りませんでした。
勉強不足ですね…。

>>399
全件カウントなら・・・別テーブルでカウントしておいた方がよくないか・・・・？
引いてくる条件のバリエーションが多いと結局面倒になるだけだけど。

PostgreSQL初心者です。
idを主キーにして、データはidの昇順に登録しておきました。
WindowsなのでpgAdminIIIのデータビューで見るとidの昇順にデータが見えます。
一方
SELECT id from table
とするとid昇順になっていないところがありました。
order byを使えばいいことは分かったので問題はないのですが、こういうことは十分起こりうることなのでしょうか？

>>411
PostgreSQLに限らず一般的にそう。

>>411

レコードの順序は保証されない。順序が重要ならば必ず ORDER BY すべし。

>>412,>>413
わかりました。どうもありがとうございます。

>>410
結局はよく使う検索条件だけに絞って一瞬で検索できるようにしたんだけど、
それとは別に勉強のために数GBのデータを早くする方法無いのかなと
思ってさ。Oracleなら全部キャッシュにいれてしまえばいいんだろうけど、
PostgreSQLはキャッシュサイズ800MBにピークがあるって事だったので、
どうしたらいいかなと。

元々は操作する人が任意の検索条件を指定できるようになっていたので
レコード数を別テーブルで管理は無理だった。

>>415
PostgresとOSでキャッシュ管理がダブるので、中途半端にメモリの半分とかしないほうがいい。
少なめにしておくか、むしろ限界まで割り当てるか、どっちか。

pbbenchとか使って、テストをして決めればいいと思うが．．．

>>399

>っていうのは、残りの6GBはOSのディスクキャッシュにおまかせするってこと？

そのとおりです。ＨＤＤから読むから遅いのであって、メモリからなら
ＯＳキャッシュからでも、postgreSQLのメモリからでも大差ないと思います。

>>417
テストをして決めるときの、パラメータ値の探索範囲の話をしているんだと思うよ。

4GBのテーブルの全件カウントを速くしたいのでpgbenchは意味無いんです。
ベンチテストという意味ではそのselect count()の結果自体がテスト
なんだけど、本番稼働中にDB止めながらパラメータ変えつつ
テストするわけにもいかないし、とりあえずメモリ増設と設定パラメータ変更
(バッファ1.5GBとか)はやってみたので、みなさんの知恵を借りに来ました、
という感じです。

もしかして「キャッシュサイズ800MBにピーク」っていうのも
テーブルサイズとか無視した単なる指標に過ぎなくて
環境によっては大きく変わるのかなあ。
例えば今回の場合だったら4GBキャッシュに割り当てた方がいいとか

参考にしたのはここ
http://itpro.nikkeibp.co.jp/members/ITPro/oss/20050402/158372/?P=2

>>420
全部SSDにつっこめ。

ネタにマジレスするのはなんだけど
業務用のシリコンディスクっつーのもあるよね
http://www.newtech.co.jp/products/silicon_s/ramsan/index.html

16GBで570万て

>>421
全件カウントじゃ4GBがテーブル全部がキャッシュに乗っからないと劇的な変化はなさそうだし、
shared_buffersを4GB強にして試してみるのは悪くないと思う。

>>422
ランダムアクセス性能が非常に重要になるので、ランダムアクセスでは速いSSDは確かに
面白そうではあるけど、あっという間に寿命がくる罠…

pgperlのソースを探してるのですが
フォーラムにも見当たりません。。
どなたかご存知ないでしょうか？

4GBのテーブルってデータ保存期間が14日間だったんだけど、
30日に延長されたよ。今後はこのテーブルだけで8GBになる。
ちなみにデータ領域をdfでみると今10GBくらい。

>>426
だからSSDに配置汁。

トリガで常に件数どっかに置いておく、とか

SSD買う金があったら、その金でメモリ買ったほうが良くね？

>>429
でも、8GB以上メモリを積めるマシンはそれなりの価格になるぞ。

SSD載せれるサーバもそれなりな価格になりそうだが^^;

最近とったメモリ大目の見積もりだと
クアッドコアのSMPでメモリ16GでSASの73GB(15000回転)でRAID10で
(保守とかその他オプションつけて)だいたい100万〜150万くらいだったかな

結局のところ、426がどれだけ予算をかけられるのかによるからなぁ。

telnetでつないでpsqlでselect文だして、ちょっとした確認をすることが結構あります。
で、結果をマウスで選択、コピーして、使ったりしてるのですが、結果が多い時コピペが大変です。

そんな時、コマンドの>みたいにテキストに書き出すことはできないでしょうか？

例えばtest.sqlというファイルにSQLを書いておいて

psql -U postgres test < test.sql > test.result

こうすればtest.resultに結果が書かれると思うんだけど
これじゃだめ？

>>433
psql を -o オプション付きで起動するか
psql で \g ファイル名とするか。

こういう時は psql --help するか
psql で \? すれば大概分かるかと。

\o

teratermかpoderosaで繋いでログファイル指定しとく、とか
Windowsじゃなかったらアレか

>>434
>>435
>>436
>>437
レスありがとうございます。

おっしゃる通り、ヘルプの存在をすっかり忘れてました。
失礼。

ログっていうのも手軽でいいですね。

手がかかる方法としてはsshでつないでport forwardしてPgAdminつかうという手も

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

>>440

date列にindexを張るとか?

久しぶりに来たら進んでる・・・
>>306
そんなのSQLサーバ側で変わったら使えないと思ったんだが、
見た目文字列リテラルなのか、日付リテラルなのかは、コンテキスト次第だ
った・・

>>440
やっぱり、dateにINDEX張ってないと遅くなるね。って441と同じ意見であった。
データが膨らむからその時点でディスク容量が増えているか・・・。

INSERT系なら、
ttp://www.postgresql.jp/document/pg831doc/html/using-explain.html
で、幾分か速くなった。

補足：　INSERT構文を最適化するのはこっちだった。。。
ttp://www.postgresql.jp/document/pg831doc/html/sql-prepare.html

>>442
他には月ごとにパーティショニングしてみるとか。

引っかき回すようで申し訳ないんですが
>>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
とやるのよりはやいの？

状況によりけりとは思うんだけど、どちらのほうがより安定しているのかな、とおもって。

>>441-442
アドバイスありがとうございます。
date にインデックスをはったら大分早くなりました。
というかめちゃくちゃ早くなりました。

失礼します。
pg_dumpallから生成されたファイルから、ある一つのDBだけリストアしたい
場合、コマンドとしてどのように実行すればよいでしょうか？
テンパってます。。。

-d オプションで指定してもだめでしたっけ？
最悪、ダンプファイルを開いて必要なとこだけ残してリストアする？

あまりお力になれず申し訳ない。

>>448
ありがとう、試してみます。

いい案が思い浮かばないままですが、

postmasterのインスタンスをもう一個起動して、そっちに放り込んだあと
必要なDBだけのダンプファイルを作成するのはどうでしょう？

ダンプファイルを直接編集するよりは楽かもしれません。。。

>>450
ありがとう、私もそれしか思い浮かびませんでした。
いったん別マシンのpostgresにフルで戻した後、必要なdbだけをdb_dumpして、
それを戻すようにしました。手間ですが、何とかなりました。

>>451
何とかなったようでよかったです。
おつかれさま。

テーブルの主キーを変えるのってできませんか？

主キーを削除できればできそうですけど・・

お願いします。

616 名前：NAME IS NULL[sage] 投稿日：2008/04/18(金) 03:12:10 ID:???
MySQL、新機能追加は有償版の「MySQL Enterprise」だけを対象に
http://www.technobahn.com/news/2008/200804172000.html

Linuxを代表するオープンソースベースのリレーショナルデータベース管理システムのMySQL
が近くソースコードの公開を停止する方向で準備を進めていることが16日、米カリフォル
ニア州サンタクララで開催中のMySQLコンファレンスの席上で明らかとなった。

>>454
スレ違い

MySQL vs PostgreSQL Part2
http://pc11.2ch.net/test/read.cgi/db/1123011800/
こちらへどうぞ

嫌気がさした人はこっちに来そうですねw

ライセンスの問題からしてもこっちに移住する人は多いだろう

呼び込み運動をさりげなくしましょうw

『なんか大変ですね。よかったらこっちも試してみませんか。』

PostgreSQLの方がシンプルで好きなんだが・・・。
XOOPSが動けば、PostgreSQLに乗り換えたい。

>>457
つーか、ライセンスのこと気にする人は最初からこっちにいるでしょ。

>>459
へ？　動かないの？　XOOPS コミュニティってそんな低レベルな世界だったんだ。

>460
PostgreSQLで動かしたことがあるのか？
推奨環境には、MySQLしかないようだが。

>>461
何言ってるの？
XOOPS って MySQL に依存しまくりのコーディングしてるの？
なんという前時代的な。

にしたって、それくらい改造出来るだろって話。
実際やってる例もあるみたいだし。

既知外ってなんでイキナリ喧嘩腰なんだろう。

共有ロック/排他ロックってどうやんの?

ロックなぞわざわざ「やる」ものではない。

>>464
行ロックなら SELECT ... FOR SHARE/UPDATE
表ロックなら LOCK tbl IN SHARE/EXCLUSIVE MODE
>>465
MVCCあるからあんまり必要ないのは同意。
でも SELECT FOR UPDATE くらいは使わないといけないこともよくある。

>>466
というより、SELECT FOR UPDATEはOracleやPostgresのように直接RRレベルを
サポートしないDBMSでRRが必要な場合に使うものと考えた方が良い。
下手にロックとか考えると、同期制御とごっちゃに理解して後々困ることになるからね。
OracleのRead Commitedは間違ってる、とかw

RRって何？

たぶん REPEATABLE READ

>>425
>pgperlを使う方式(旧称 pgsql_perl5)
>1995年のpostgres95の時代からPostgreSQL7.2.3まで PostgreSQLのソースの含まれていた Perlインタフェースで，
>libpqに似せたシンプルなインタフェースデザインとなっています．
>libpqライブラリが必要です．

FETCHコマンドで
FETCH [ direction { FROM | IN } ] cursorname
とありますがFROMとINの違いってなんですか？

「INを使用するオプションはPostgreSQLの拡張です。 」としか書いてないし
実際に使ってみても同じ結果しか得られないし・・・

>>471
FROMが標準語で、INはPostgreSQLの方言ってだけかと

webページの言語をutf-8で作成してる場合は
PostgreSQLでもutf-8を使ったほうがいいんですか？
utf-8にしたことにより発生するエラーというものも有るんでしょうか？

今は外字も入るし、utf-8にしてもデメリットないんじゃないかな。
基本的にはドライバが吸収するから、webサイトとDBの言語は
別々に考えていいよ。そして、どっちもutf-8にすべき。
諸事情でwebをSJISにする場合でもDBはutf-8で
いいんじゃないかな

>>474
ありがとうございます！
統一できると、ローカル作業で言語切替の事を気にしなくていいのでよかったです

DBのエンコードがなんだろうがクライアントと関係ないんじゃ？

そうだな
まあ日本語以外のマルチバイト文字も考えなきゃならんときはUTF-8はありがたい

思いつきだけど UTF-8 とそれ以外じゃソートしたときに結果違わね?

ひらがなさえ同じならいいけどな
個別にソート用に表持たせることもあるかな

将来、PostgreSQL のバージョンアップで、
int 型を検索するときに
c = 1 ではなく、' で括った
c = '1' がエラーになる可能性はありますか？

PostgreSQL 8.3 にしたところ、text 型のカラムを検索する場合

where foo = 1;

ではエラーになりました。

int 型は括っても括らなくても使えているのですが、
SQL の標準とか、将来的にどうなのかな、と疑問に思って。
ご存じの方、よろしくお願いいたします。

>>480
↓が参考になるかも？
http://www.postgresql.jp/document/current/html/typeconv.html

個人的には
bigintとintとかtextとcharみたいにサイズが違う同じ型ならDBにまかせるけど
intとtextみたいに別物は明示的にキャストしてあげたほうがいいと思う。

>>480
その書き方で大丈夫。
クォートされていると型の決定は文脈依存で、適当に変換される。

ちなみに、若干似てるけど、
Java で PreparedStatement#setInt() の代わりに setString() するのはダメよ。

>>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) がエラーになることはありますか。

>>483
エラーにはならない。安心して (2) を使って大丈夫。

>>8.2 までは select * from test where text = 1 は問題なく、

これが、問題なく使えていたのを初めて知った。
気になったので確認したら、数字だと通ってたんだな。

>>485
正確には、型の不一致があったとき、両辺を勝手に text にキャストして比較していた。
where timestamp = 数字 とかも通っちゃってた。

いろいろと危険なので、変更されてよかったと思う。

>>484
数字は 1 でも '1' でも危険はないから
今後とも大丈夫、ということなのでしょうか？

将来のことなので誰もなんとも言えないと思うけど、
可能性の話をするならエラーになる可能性はあるんじゃない？
もしくはエラーにはならなくてもtextで検索しにいくので
条件にマッチしない可能性とか

確かに将来は分かりませんよね。
ありがとうございます。

SQL:1999 や SQL:2003 などの
SQL 標準的にはどうかご存知の方は
いらっしゃいませんか。

それを知ってどうするつもりなんだろ。
ちゃんと型あわせるようにしておけば大丈夫なだけじゃん。

どんな言語でも「やっぱ暗黙のキャストやーめた」となる可能性は
0じゃないよ

実行計画とると、解析後のSQL見られるけど
内部的にはかなりキャストしてるのが分かる

たいしたことは無いんだろうけど、表の結合条件でキャストが多いと
性能が落ちるかもしれんね

>>491
リテラルなら、SQLパースのあと1回だけキャストされて、処理中はすでにキャスト後になっている。
性能を心配する必要は無いよ。

そもそも「数字」といったって、int2/4/8, float4/8, numeric と
いろいろあるんだから、'' で括らなくても何かしらの変換が挿入される可能性はある。

8.3系で運用してる人いる？
過去バージョンと比較して使用感はどんなもんでしょうか

ユーザ会からpostgresをダウンロードしようとすると

Internet Explorer ではこのページは表示できません

になってしまいますが、サーバがこけてますか

他のミラーから取ってきたら？

ftp://ring.ocn.ad.jp/pub/misc/db/postgresql/

>>495
いけました。ありがとう。

>>493
8.2でパフォーマンスが凄く悪かったサーバが見違えるようによくなった。
他のサーバもそこそこ改善してるし今のところ人為的なのを除きトラブルはない。
無茶といわれてた大量UPDATEに耐えられるかな？
できるならそういうコードも書きたいのだが・・・

>>497
是非試していただければと思います。

Windows版のPostgreSQLを自動インストールする方法はありますか？
Windowsの他のプログラムから、インストーラを起動して自動的にイン
ストールできるようだといいのですが。

>>499
http://pginstaller.projects.postgresql.org/silent.html

>>497

具体的に8.2ではいけなかった処理ってなんとなく尻尾つかめてたりするのでしょうか・・？

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の制限に引っかかっているようなことがありませんでしょうか？

>>500さま
英語の方に解説があったのですね。
もう少しぐぐり方を工夫する事を覚えようと思います。
ありがとうございました。

>>502
min(book.fromdate || book.name) の部分で、日付が yyyymmdd に変換されると
期待してそうだけど、ちゃんと動作している？
単に、group by に book.name を含めるべきなのでは？

> minの結果が変
「変」とか「おかしい」とか言われても、助けられない。
具体的にどうなったのか、説明して欲しい。

>>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なのですが。。

>>505
unionとか関係無しに単純に文字列のminとmaxをやったらどうなるの？
encodingは？

>>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です。

すみません。>>505に誤りがありました。
誤：ちなみにmax()にすると"20080502書籍02"が返ってきます。
正：ちなみにmax()にすると"20080501書籍01"が返ってきます。

進展がありました。book.nameの値から全角文字を除去してみたところ
書籍01→01
書籍02→02

min(book.fromdate)　min(book.name)　min(book.fromdate || book.name)
20080501　01　2008050101

正しく集計されました。
もしかしてmin()関数の引数に全角文字が含まれるとおかしな動きをする、とかありますか？

book.fromdate || book.name
20080501書籍01
20080502書籍02

min(book.fromdate || book.name)
この場合、先頭の８文字で大小比較が完了するので、全角文字までの評価は行われないだろうと期待していましたが、
>>509の結果を見ると、そうでもなさそうですね？

>>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の代替手段がなくて困っています。

DBのEncodingはどうなってるの？

>>512
>>507

--no-locale付けた？

>>514
分かりません。Windows版なんですけど、付けたかどうかを確認する方法ってありますか？
でも、なんかビンゴっぽいですね。

>>515
Windowsかあ。createdbはどうしたの？インストール時ならチェックする項目あったかなー。

SELECT name,setting FROM pg_settings WHERE name ~ '^lc';
とかどう？

>>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"
でした。ダメ？

あとは、pg_controldata コマンドでもいい。

>>517
noにすれば、そこは C になるはず。
noにしないとソート順狂うから、それだろうね。

>>519
ありがとうございます！
ちなみにinitdbからやり直さないとダメなんですよね。キッツイなぁ。createdb毎に指定できないものか。。

pg_dumpall ですぐだべ

>>521
やってみます！

ロカールの話で盛り上がっているようですが、
そもそもそれに依存しない抽出方法があるような気がします。

それぞれの本ごとに、一番古い貸し出し日および冊数を出したいのですよね？

切れてしまいました。

以下のような頻出事例と似てるような気がします。
http://pc11.2ch.net/test/read.cgi/db/1193486961/35-36

>>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サービスが立ち上がらなくなってしまった!?

>>523
>それぞれの本ごとに、一番古い貸し出し日および冊数を出したいのですよね？
一番古い貸し出し日は抽出対象ではないのですが、
bookテーブルが世代管理されているので（同一idでfromdateとtodateで管理）
一番古い貸し出し日の時のbook.nameを世代を見て抽出したいのです。

>>525
postgresql.conf を data-old から data にコピーすればいいと思う。

>>525
そういうときは、ログとpostgresql.confをよく嫁

>>527 >>528
お騒がせしました。パニクって再インストールしてしまいましたｗ そしてその再インストールでも小一時間ハマってました。

ロケールをCにして再度SQLを実行したら期待通りの結果が返ってきました！！アドバイスをくれた皆さん本当にありがとうございました。

合わない locale と encoding を使ってしまうのは、頻出ミスだった。
v8.3 からは適合しない組み合わせはエラーが出力されるようになったので、
うっかりミスも減らせるはず。

ついに対策されたんだｗ
石井さん乙？

8.3リリースノートより：
> サーバのロケール設定と矛盾するデータベース符号化方式を不許可としました。(Tom)

テーブルの中の特定カラムを、AESとか、desとかで、暗号化する事って出来るんでしょうか？
プログラムで行えば出来るのは解っているのですが、
一部一致検索で、ひっかからなくなって困ってしまいました。
そこで、PostgreSQL自体で暗号化する方法があれば、部分一致検索も
正しく動作するのか？と考えました。
だれか、こんな事をやった事ないですか？

これ？
ttp://osb.sraoss.co.jp/PostgreSQL/Manual/PostgreSQL-8.0-ja/encryption-options.html

特定の列に関する暗号化

/contrib関数ライブラリ pgcryptoにより、あるフィールドを暗号化して保存することができます。データの一部が極秘データであるような場合に有用です。クライアントが提供した複合化用のキーで、サーバ上のデータを複合化し、クライアントに返されます。

複合化されたデータと復号化用のキーは、復号処理中およびクライアントサーバ間の通信中サーバ上に存在します。このため、データベースサーバへの全てのアクセス権限を持つユーザ（例えばシステム管理者）によって、データとキーが盗まれる瞬間があります。

>>532
トム。。。

>>533
部分一致検索したいのなら、ファイルシステムレベルで暗号化しないとだめ。
ただ、SELECT * で普通に元の値を取得できてしまうのが難点かも。

pgcryptoのような方式で、部分一致検索できるようにしてしまうと、
暗号化の強度としてはゼロに等しい。難読化くらいの価値しかない。
大小の順序を保つ必要があるので、二分検索で簡単に特定されちゃう。

>>536>>534

こういう話出す人はもう少し暗号の勉強をして、表現力を養ったほうがイイよね。
権限を持つ者がデータを持っていけるのは、暗号とは相関性が無いんだよ。
無駄に暗号強度とか持ってくるから、「説得力があると騙される無知な人も居る」と覚えておこう。

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 だと エラーだし、
どうしたらいいんでしょう?

何がしたいのかによる。
birthとgenderでグループ化したらそのグループに複数のidが含まれる可能性があるから
そのまま出すのは無理。
最大値 max(id)　とか、一つに特定できるのならばそれが使えるけど。

>>539
ですよねー
やりたいのは、まさにグルーピングであり、複数のidをグループごとにとりだしたいんです。
でもSQLではなんかうまくできなくて...すごく不自由しています。
こんなとき、みなさんどうしてますか。やっぱりPL/SQLとか使わないといけないんですかね。

複数idが必要ならグループにする理由がないのでは？
具体的に、どんなデータからどんなデータを抽出したいのか書いてくれればわかるかもしれん

select id, birth, gender from members order by birth, gender
とか？

元のデータ
　id　|　name　|　　birth　　|　gender
------------------------------
1 | test1 | 2008-05-09 | M
2 | test2 | 2008-05-10 | F

抽出結果
　??　|　??　|　??　
--------------
x | xxxx | xx
x | xxxx | xx

こんな感じに

>>537
で、あなたはいったいなんの役に立っているの？

>>543
抽出結果が伏せ字だらけ、、、はっ！これが暗号ってやつDESか？

>>538
idを配列として取り出してみては?
http://www.postgresql.jp/document/current/html/xaggr.html
のarray_accumを参考に。

>>537

>>534はドキュメントのコピペですよ。

どの経路をどういう目的で暗号化したいかによって全然変わってくると思う。
>>533の文面から察するに、ディスク上のデータを暗号化したいだけで、サーバ上のメモリとか通信路の上では暗号化されなくてもいいみたいだし。

>>547

> >>534はドキュメントのコピペですよ。
それは見れば解る。

> どの経路をどういう目的で暗号化したいかによって全然変わってくると思う。
> >>533の文面から察するに、ディスク上のデータを暗号化したいだけで、
> サーバ上のメモリとか通信路の上では暗号化されなくてもいいみたいだし。

キミに知識があるのは理解したけど、
具体的な部分を引用で誤魔化しただけじゃ意味が無い。

>>533みたいな質問を恥ずかしくなくできる程度だと、
その引用が役立つとは思えない。
暗号の基礎を学ぶ方が先決と感じるけどね。

>>547

部分一致検索が出来る方法を探していたので、
メモリー上や通信経路上での暗号は別問題って事で。

534や536のpgcryptoを試してみます。

postgresqlをlinuxにインストールしてみましたが、
jdbcがありませんでした。
jdbcのみインストールできるサイトはありませんか？

よろしくお願いいたします。

>>550
本家->downloadでDLのページいってみては？

複数のサーバに分散させてたアクセスログテーブルを一サーバにまとめる
作業をしてるのだけど、IDカラムをPRIMARY KEYにするために
数字を重ならないように振り直す良い方法ない？

連番である必要はないのだけど、
UPDATE id = nextval('foobar_id_seq');
だとトランザクションが完了までにストレージの容量不足であぼ〜ん。

データベースが500Gでストレージ容量が800Gな感じ。
行数はpgAdminの概算で10億のオーダー（実際にもたぶんそんなもん）。
Intel 8コア2Ghz、メモリ6Gで丸一日動かすぐらい以下で終わるとうれしい。

>>548
暗号の基礎というのが想像つかないけど、公開鍵暗号とか、ハッシュ関数？AliceとかBobがいて、…みたいなことかな。
よかったらポインタとかキーワードください。

部分一致検索「できない」ところに暗号化する意味があるといえばあると思うんだけど、暗号化されたままのデータに対するプログラムによる処理、という研究領域もあったような。
まだ実用段階ではないはず。

>>552
250GBぶん処理 → VACUUM → 残りの250GBを処理
で、750GBぎりぎりに収められるような気はするけど、効率的では無いね。

オリジナルの各サーバからデータをダンプすると思うのだけれど、
そのときについでにIDを再割り振りしてはいかがでしょ？
COPY (SELECT ID + <サーバごとのオフセット>, <ID以外の属性> FROM tbl) TO ...
みたいな。

>>554
レスどうもです。
すでにpg_dumpでテーブルを圧縮ダンプしてオリジナルを消してしまって
いるのですが、書き戻しつつまったりやってみます。

スタンドアロンで起動してチョメチョメするとUPDATEがトランザクションを
作らずできるとかあると良かったのですが。

>>551
このやり方だとすべてをインストールしなければならないですよね？
jarファイルだけほしいんですが・・

>>556
つ 本家 [ttp://jdbc.postgresql.org/]

新しい列を追加してサーバID＋IDカラムを主キーにすれば？

>>548

>>534はpostgres標準でカラムごとに暗号化する方法が用意されてるよ
というポインタを出しただけだろう。なんでそんな喧嘩腰なん？

>>543
せめてどの列に何のデータが欲しいかくらい書いて下さい。

>>560
>>543は質問者への例だよ。
質問者が>>542みたいなこと書くからさ。

>>561
ごめんよ('A`)

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)

Direct I/Oが効くからopen_syncでいいんじゃね?
たまにバグ持ちのOSもいるけど。

VACUUM FULLをやるとすごい時間かかると聞いていたけど、3分くらいで終わってしまった。
テーブルは200個くらいで、多いテーブルでもレコード数は20,30万レコードくらい、大半は数千、数万程度
これってだいぶ少ない感じだからかな？それともdumpを戻してからあまりデータが更新されてないから
汚れてないってことで早かったんですかね？
後、VACUUM FULL テーブル名って感じでテーブルのみ指定できるけど、これを
全テーブルやれば、VACUUM FULLやったときと変わらないのでしょうか？

>>565
>dumpを戻してからあまりデータが更新されてないから
> 汚れてないってことで早かったんですかね？

> これを全テーブルやれば、VACUUM FULLやったときと変わらないのでしょうか？

いちいちその通りでなにも返す言葉がない

sequenceの値を自分で設定することは出来ますか。
今は 1 から始まっているのをたとえば10000から始めたいんですが。

ttp://ash.jp/db/pg_use.htm
答えを知らない俺がググって10秒で分かる内容を質問する勇気に感心した。

自己レスです。setval()関数でできるようです。

>>568
すみません

使用環境はXPなんですが
COPYでテーブルのデータを全てCSVに落としたいのですが
文字列が２重引用符で囲まれなくて困っています。

COPYの説明読む限り、デフォルトは２重引用符となっています。
QUOTEで直接指定しても変わりありません。

>>571
FORCE QUOTE

FORCE QUOTEってカラム指定しないと駄目ですよね？
文字列だけ指定した引用符で囲いたいんですが
単純にQUOTEだとNULL文字だけしか引用符で囲ってくれないんです。

文字列型のカラムを全部指定すればいいじゃん。

集約関数のパラメータとなるような複数行のデータを表現する式というのはありますか？

こういう感じのことをしたいのですが

SELECT * FROM TABLE1 ORDER BY MAX(COL1,COL2,COL3); // COL1〜3のなかで最大の値を取りたい

しまった。
習慣でsageてしまった・・・

>>575
greatest() かな。max(greatest(col1, col2, col3))

2chのような掲示板を作ろうと、以下のテーブル構成を考えています。
この場合、連番はどうやって取るのがスマートでしょうか？

板（板No、･･･）
スレッド（板No、スレッドNo、･･･）
レス（板No、スレッドNo、レスNo、･･･）

板Noはシーケンスでいいとして、スレッドNoとレスNoの取り方で迷っています。
Max(レスNo) + 1で取るのがいいか、発番テーブルを作るのがいいか。
あるいはもっとスマートなやり方があるのでしょうか。

アドバイスお願いします。

スレッドも(big)serial型でいいんじゃ？規則正しく連続してる必要ないよね？
insert 〜 select 〜でいいんじゃない？
発番号テーブルとか作ってわざわざ整合とりにくくする必要性無いと思う

あいうえお<br><h1>かきくけこ</h1>

というデータが入っていたとしまして、SQLでこのタグ(<>で囲まれている文字列)を省き、

あいうえおかきくけこ

として検索できるようにする方法はありますでしょうか。
もしありましたらご教示いただけますと幸いです。

宜しくお願いします。

>>580
> SQLでこのタグ(<>で囲まれている文字列)を省き、

regexp_replace(データ,'<.*?>','','g')

取り出してからの切った貼ったですかね。

よろしくお願いします。

金額を入れるカラムは、精度の高いnumericを使用した方がよいと聞いたのですが、
マニュアル等で調べたところ、そのカラムに整数しか入らない場合はinteger等で十分のような気がするのですが
この考え方で合っていますでしょうか？

10/3*3とか？

>>584
確かにその場合は小数点があるのでnumericが必要ですよね。
必ず整数しか入らない前提の場合はintegerでも良いと思ってよいでしょうか。

「精度」の意味がよく分かっていないかもしれませんが、
整数しか格納しないのであれば、numericもintegerでも計算で誤差は生まれないと
思っているのですが。。

SQLで計算はしないのか？

>>586
SQLで計算はしないOR計算しても小数点が出ないという前提です。

>>583
numericが「何と較べて」精度が高いのか聞かなかった？

真数値： numeric integer
概数値： real float

あと、「精度」といっているのが誤差の少なさなのか
有効数字のことなのか。

「金額〜」という文脈で精度のことを言うときは、上記の
真数値である(誤差がない)ことを言う場合が多い。

そもそも、通貨って円だけじゃないし
日本でも銭まで必要なこともあるし

そういうのを踏まえて必要かそうでないか判断しては

>>588,589
レスありがとうございます。

元々、素人DB設計者がnumeric(10,0)としていたので
突っ込んでやりたいなと思って質問しました。

＞numericが「何と較べて」精度が高いのか聞かなかった？
金額の計算をする時、integer等と比較しての話として精度が高いと聞きました。

なので、「精度」は
＞真数値である(誤差がない)ことを言う場合が多い。
こっちのことだと思います。

＞日本でも銭まで必要なこともあるし
情報小出しですいません。
私も、numericを使うことに異論は無いのですが、
もともとのカラムがnumeric(10,0)だったんです。。

「精度が高い」と人から聞いただけで、「精度」の意味も考えずに
numeric型を採用しているDB設計者に対して
「小数以下格納しないならintegerでいいじゃないですか」という理由が欲しかった次第です。

低レベルな話ですごく申し訳ないです。。

整数としてしか扱わない、というのなら整数でもいいんじゃない？
金額を扱う場合はnumericと決めている、のならnumericでもいいし。

numericだのintegerだの云々言いたい583は、当然
ttp://ja.wikipedia.org/wiki/%E4%BA%8C%E9%80%B2%E5%8C%96%E5%8D%81%E9%80%B2%E8%A1%A8%E7%8F%BE
あたりの知識はあるんだよな?

まあ、ヲレはPostgreSQLでnumericがどう実装されているか知らないけどさ。

整数を扱うとき、numeric型とinteger 型と選ぶ基準は
扱う桁数でいいだろ。
お金だと桁数10桁で足りない事もあるし、そういうときはnumericでいいんじゃね？

俺は、Java上で金額計算するから格納時には桁数roundしてるから
入れ物さえあれば問題ない。

>>590
逆になんでnumericなのか聞けば？
俺は桁数が分かるという利点でnumericにしてるんだと思うがね。
DBのポータビリティを考えて、もう1層上でテーブル設計してるんじゃないか？

>>590

うちも同じようなテーブル設計があったので、直せるところはintにしま
した。物の個数ですらnumericでやってあったから、さらに低レベルかも。
intかbigintの方がコンパクトで高速と思ってます。

>>591-594
レスありがとうございます。

とりあえず、DB設計者になぜnumericなのか聞いてみた結果
→「これがいいって聞いたから」

あとは愚痴スレ行きます。。

>俺は、Java上で金額計算するから格納時には桁数roundしてるから
>入れ物さえあれば問題ない。
私も同じようにしようと思います。

ご助言いただき、ありがとうございました。

>>565
ごめん嘘書いちゃった
テーブル指定無しで実行しないとシステムカタログがvacuumされないらしい
http://www.thinkit.co.jp/cert/marugoto/2/1/13/2.htm

>>596
いや、システムカタログ名を指定すればできるだろう。
　VACUUM pg_class;
とか。面倒だから普通は autovacuum に任せるけど。

結合条件にLIKEなどを使って
あるカラムが別テーブルのカラムを含んでいる行と外部結合ってできないでしょうか？

SELECT o.foo, u.bar FROM hoge o LEFT OUTER JOIN huga u ON o.foo LIKE '%'||u.bar||'%';

じゃだめでした。
なんかうまい方法ないでしょうか？

ONのあとは結合条件、o.foo LIKE '%'||u.bar||'%'はWHERE句に

すいません。
↑のでできてました。

結合するテーブルにINSERTするデータが間違ってるだけでした。
お騒がせしました。

日本語データをinsertし、その後格納データ確認のためselectしてみたところ、
以下のようになってしまっておりました。

<BD><BE><CD><E8><CA><FC><C1><F7><A4><B5><A4><EC><A4><BF>
<C8><D6><C1><C8><C5><F9><A4><CF><A1><A2><A5><C6><A1><BC>
<A5><D7><C7><DE><C2><CE><A4><C7><CA><DD><C2><B8><A4><B5>
<A4><EC><A4><C6><A4><A4><A4><BF><A1><A3><A4><DE><A4><BF><A1><A2>

しかしながら、プログラムから同様のSQL文でデータを出力してみたところ、
正常に日本語が表示されました。

本現象に関して何かアドバイス等ございましたらいただけますと幸いです。
宜しくお願いします。

select文を発行したときと
プログラムからのselect時との
client_encodingの違いを意識してるか？

>>601
その<BD><BE><CD>・・・を出力してるのはページャー（more とか lessとか）
そのページャーがその文字コードに対応していない。
ページャーを何とかするか、ページャーにあわせてクライアントエンコードを変更する。

どこかにWinでpgAdminからSlony-Iの使い方解説したサイトはないものか

HELPでいいやん

>>597
いやできない。試しにやってみるとこうなる。
WARNING: skipping "pg_user" --- cannot vacuum indexes, views, or special system tables


autovacuumまかせにできるのは小規模だけだろ。
数GB程度のデータ規模でも、アクセスが少ない時間をねらって
毎日vacuumしないとvacuum時間が長くなって大変。

autovacuumを頻繁に行い、一回あたりの時間を短くする

>>606
pg_userはVIEWですよ。VACUUMできるわけが無いし、する必要も無い。

あと、最近のバージョンだと、vacuum_cost_delayを調整して
vacuum時間を「長くする」側にチューニングしたほうがよい。
レスポンスに影響が無いなら、時間が延びてもデメリットは無い。

「毎日vacuumしないと」も間違い。テーブルによっては、それこそ数分間隔で
VACUUMが必要な場合もあるし、年単位で十分なテーブルもある。
手作業でチューニングするくらいならば、autovacuumのほうが楽だし効率も良い。

VACUUMの運用法は、ここ数年で大きく変わってきているので、
あまり古い考えにこだわり過ぎないことをお勧めする。

特に8.3ではHOTのおかげでvacuumする必要性がだいぶ低くなってきたからねぇ。

8.3て、致命的問題なく動いてる？

>>608
そっか。確かにpg_typeならvacuumできたわ。

>>607
autovacuumって頻度指定できんの？

8.2でautovacuumだけで運用してたらあんまりvacuumされなくて
手動でやるようにしたんだけど。
一日数万件のinsert/deleteがあるのに何日間かvacuumされて
なかったんだよ。んでpgAdminで繋いだらautovacuum設定しろしろうるさい。
してあるのに。

バッチで実行すれば「アクセスが少ない時間帯にvacuumが完了」するように
指定できるし、vacuum実行時間のログもシステム側に残る。

autovacuumが効率よくvacuumかけてくれる事を説明してるURLある？

>>611
autovacuum_naptimeで最小間隔の指定は出来るし、
autovacuum_vacuum_thresholdやらautovacuum_analyze_thresholdやら
いろいろ設定可能

>>612
stats_row_level = onにはなっているよね?

>>612
そのテーブルだけ pg_autovacuum.vac_base_thresh で設定すると良いかも。
別に、バッチVACUUMとautovacuumは排他じゃないから、
むしろ両方とも設定した方が問題が少ないことが多いかな。

今は両方セットしてあるんだけど、とあるテーブルだけ8GBくらいあって
日中にvacuum走ると使い物にならなくなってしまうので、
autovacuumは切ろうかと思ってる。
てか毎日vacuumしてて空き領域が良好な状態だったら
autovacuumは走らないものなの？

>>615
とりあえず、autovacuumが行われるタイミングに関しては
ttp://search.net-newbie.com/pgsql/routine-vacuuming.html
参照。

サンクス
閾値を超えなきゃvacuumされないのね。
とりあえず数万件の削除は日毎バッチで行われていて、
手動バキュームはその直後に行うようになってるから、
よりベターなタイミングっていうのは無いと思うのよ。
とりあえずこのまま行く

クライアントからODBCを使ってサーバのデータベースに繋ごうと思って
ttp://www.advancesoft.co.jp/wiki/?PostgreSQL%B3%B0%C9%F4%C0%DC%C2%B3%CA%FD%CB%A1
のサイトに記載してある通り、pg_hba.confとpostgresql.confを編集して接続を試しても、
Could not connect to the server.
No connection could be made because the target machine activety refused if.
とエラーが出てしまい繋げません。
サーバのlocalhostでの接続は可能でした。

何か原因わかる方いませんでしょうか？
ちなみに環境は
サーバ Windows 2000 Server SP4
クライアント Windows XP SP2
です。

>>618
せめてnetstatの結果ぐらい確認しろよ

>>619
調べてみたら5432のポートが開いてない感じがしたのですが、
その場合どういった対策をすればいいのでしょうか？
postgresql側での設定が必要なのでしょうか？
宜しくお願い致します。

>>620
感じがした、って…オマ…
火壁とかどうなってんの？ Windows知らんから、これ以降は誰か頼む。

まず、pgAdminIIIで接続できるか確認してごらん。
つながるならODBCの設定があやしいし、
つながらないならpingとかでネットワークで到達できるかからやってごらん。

>>622
そんなことせんでも telnet で 5432 に繋げればすぐ分かる。

pgAdminIII使ったほうがあとあと役に立つと思ったんだけどな。

>>621-624
ありがとうございます。
pingは無事に到達します。
Windows 2000 Serverにファイアーウォールはないと思うんで特に設定はしていません。
とりあえず今は触れる環境にないので、明日もう少し試してみます。

>>625
お前が"postgresql ポート 設定"でぐぐることすらできないのと、
telnet foobar 5432すらできない知能なのは良くわかったから、
きちんとお勉強してくるまで出入り禁止。

netstatで調べたら5432が開いてないとなったんでしょ？
なのに火壁が！pingが！とか言ってるのはナニ？

pg_hba.confの、この部分もコピペしたとかじゃなくて？
# ローカルのみの設定
host all all 192.168.1.0/24 trust

LANの設定にあわせて記述してますか？
192.168.0.0/24 とか。

>>627
開いてない感じがしただけで
開いてないとは誰も言っていない罠ｗｗｗ


>>625
ま、実際にLISTENポートとしてリストされなかったなら、
設定が悪いだけだから、見直して再設定すればいい。

馬鹿は放置しとけよ…

feelingで話を進めた段階でアウトです。

分からない事は分からない。
それを素直に出せないのは、
眼鏡をかけた新人の女の子までしか許されません。

postgres.confのlistenだろ

>>628
しまった！＞＜

>>631
別のポート番号にしたいとき、そこ変えて、service postgresql startとやっても
反映されなくてハマったよー

>>615
>>612の通りだ。
pg_autovacuumテーブルでそのテーブだけ除外してやんな。
で、夜間にvacuumすればよろし。

>>634

ミスったｗ
>>615 →　>>614

吊ってくる

ちょっと質問させてください。
インデックスを複数同時に張るのって、１つ張り×複数とは別のときに効果を発揮する……のでしょうか？
前任者が張りまくっていたものを整理しているのですが、どうなんだろうと思いまして。

１．id, name, dateが指定されたインデックス
２．nameのみが指定されたインデックス
３．nameがインデックス指定＋where id=10　と記述

が存在。
１は、その３つを同時にwhere使用する場合にしか使われない？　それともidだけをwhere指定する場合でも使ってくれるのでしょうか？
教えて頂ければありがたいです。

aというカラムに「テスト1 テスト2 テスト3」や「テスト1 テスト3」、「テスト2 テスト3」といったようにスペース区切りでキーワードが入っていたとして、条件をテスト1もしくはテスト3に設定し、各種レコードでヒット数を表示するようなことは不可能でしょうか。
本条件の場合、はじめのレコードはヒット数2、2番目は2、3番目は1となれば期待通りとなります。

不可能かとは思いますが、もしもやり方がありましたらご教示いただけますと幸いです。
宜しくお願いいたします。

>>636
8.1だか8,2だかあたりから個別にidだけ使用しても
見るようになった。8からかもしれん。
8のリリースノート見てみて。

一般的にはインデックスを貼った順序通りにWHRE句に書かないと
使ってくれない。その組み合わせでハッシュコード生成するから。
貼ったインデックスより長い条件なら見てくれるけど。

カラムA,B,Cがあって、(A,B)にインデックスが貼ってあったらこうなる。
○WHERE A=x AND B=y
×WHERE B=y AND A=x
○WHERE A=x AND B=y AND C=z

で、新しいバージョンなら個別に貼ってあっても(効率は落ちるだろうけど)
インデックスを見るようになった

>>637
postgresの質問じゃねーけど。
こんな感じでできないかなあ。

SELECT A.ID,
(CASE B.ID WHEN NULL THEN 1 + CASE C.ID WHEN NULL THEN 1)
FROM
テーブル A
LEFT OUTER JOIN テーブル B ON B.ID = A.ID AND B.カラム LIKE '%テスト1%'
LEFT OUTER JOIN テーブル C ON C.ID = A.ID AND C.カラム LIKE '%テスト3%'

SELECTのところはこれじゃ動かないかもだから工夫してみて。
できたらここで報告よろ

ん？
カラムA,B,Cがあって、(A,B)にインデックスが貼ってあったら
○WHERE A=x AND B=y
○WHERE B=y AND A=x
○WHERE A=x AND B=y AND C=z
×WHERE B=y
じゃない？

で、最近は最後のパターンもOKになったんじゃ？

>>637
スペース区切りでキーワード入れちゃってる時点でrdbmsとして終わった感が･･･

create temp table test(id int primary key,a text);
insert into test values(1,'テスト1 テスト2 テスト3');
insert into test values(2,'テスト1 テスト3');
insert into test values(3,'テスト2 テスト3');

select id,count(a)
from (
select id,a from test where a like '%テスト1%'
union all select id,a from test where a like '%テスト3%'
) as test
group by id order by id

>>639-641

ご教示感謝です。

641さんのSQLでうまくいきましたので、こちらを使用させていただきました。

本当にありがとうござした。

分離した上で正規化したほうがいいと思うけど

実際はキーワード羅列なんじゃなくて商品説明文とかなんじゃないの？

>>610

8.3.1、キャスト問題でなかなか8.1からバージョンアップに踏み切れない
状態です。うち、意外に日付や数値の自動キャストに頼ってる場所があった。

もう8.3運用してる人って居るのかなあ？

してるよ
というかDBは8.2だったんだけどjdbcドライバで8.3を
使っちゃって自動キャストで引っかかった。
日付で検索したときに条件に引っかからないだけだったから
大して実害なかったけど。

>> 645
数値はともかく、日付の自動キャストって怖くない?
日付のフォーマット書式って、環境依存だったような。

うちはPreparedStatementを使ってた関係で、
既に型チェックが厳密だったので、特に問題なく8.3に移行できました。

>>636>>640
ご教示ありがとうございました、理解出来ました。
ざっと見8.1からっぽかったですが（これは『個別に貼ってあっても見る』の方かも？）、
ちょっと詳しく調べる時間が無くて提示出来ずに申し訳ないです。

複数インデックスが多い割には何も考えてないSQL文だらけだったので、ちょっと書き換えてきます……。
本当にありがとうございました。

うちもPreparedStatement限定だけど
日付をsetString()してた

>>640
2番目は駄目だと思うな。複数のカラムを組み合わせてハッシュコードを
作るわけだから、順序入れ替えをOKにしたら偉い数のインデックスを
つくらにゃいけなくなる。少なくともOracleは駄目だ。
だからApache TorqueみたいにWHERE句の順序を入れ替えられない
ORMが嫌われてたんだし。

>>650
8.1.11でexplainで確認したら
>>640のパターンは最後のも含めて
全部インデックススキャンになったよ。

>>650
手元にある一番古い環境(7.4.19)で試してきた。

create temp table test(a int,b int,c int);
create index i_text_1 on test(a,b,c);
#適当にinsert
SET enable_seqscan TO 'off'

・indexスキャンになる。(INDEX COND: a = 1 and b = 1)
explain select * from test where a = 1 and b = 1;
explain select * from test where b = 1 and a = 1;

・indexスキャンになる。(INDEX COND: a = 1 Filter: C = 1)
・INDEX COND + Filterになる
explain select * from test where c = 1 and a = 1;
explain select * from test where a = 1 and c = 1;

・seq scanになる(Filter: b = 1 and c = 1)
explain select * from test where b = 1 and c = 1;
explain select * from test where c = 1 and b = 1;

>>650
芸術的な妄想しったかw

>>653
昔のOracleがそうだったいうだけで今は違うみたいだね
ここには順序も入れ替えるなと書いてある
ttp://oracle.se-free.com/tuning/B1_index2.html

torqueでwhere句入れ替えられない事に対する文句は昔MLかなんかで見た。

>>654
いやいやいや、そんなこと書いてないw

インデックスを「使うこと」はできたとしても、
その「効率」がだいぶ違うことには注意が必要ですよ。

なんにせよ、Bitmap Index Scan は結構なアドバンテージだと思われ。

>>655
突っかかりたいだけで日本語も読めないのか
「●　複合INDEXは先頭列から指定しないと、INDEXが使用されない」

ttp://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink9
--８．複合インデックスの場合に、列の順粕ﾔを間違えている
　col_1, col_2, col_3 に対して複合インデックスが張られているとします。
　その場合、条件指定の順番が重要です。

まあpostgresには関係無い話みたいだし悪かったな

pgpoolを止めずにpgpoolの設定ファイルの値を変更することってできますか？
知っている方いれば、コマンド教えてください！

出力される実行計画も同じですし、実行時間もほぼ同じですので
使う事ができるのではなく、オプティマイザが等しく扱ってるように見えます。
(３回ずつ実行してみました)
・explain analyze select * from test where a = 1 and b = 1;
　0.075 ms/0.074 ms/0.074 ms
・explain analyze select * from test where b = 1 and a = 1;
　0.074 ms/0.073 ms/0.073 ms

8.2で652と同じテストを試してきましたが
どの場合もindex ScanになりFilterは発生しませんでした。
また順序を変えたパターンでは実行計画、実行時間に変化はみられませんでした。

oracleはよく分かってないのですが･･･見よう見まねで10gで同様のテストをしましたが
a = 1 and b = 1もb=1 and a=1も同じ実行計画になってるように見えます。
(oracleスレではないので気になるようでしたらそちらで検証してください)

>>658
設定の項目によるんじゃない？
項目によっては要pgpool再起動

indexの順序が関係あるのはOracleのルールベースで
インデックス貼ってた時までだよ。10gでも設定することは
できるけどもうサポートされてない

http://www.shift-the-oracle.com/inside/optimizer.html

どなたか教えてください
トリガで特定のカラムの値が任意の条件を満たす場合のみトリガを発動って出来ますか？
例えば、
status というカラムが　0 という値にupdateされた場合のみトリガ発動　
みたいなことをしたいんです　
可能なら具体的な設定方法をご教授下さい

教授　じゃなく　教示でした。。

>>662
0という値以外にアップデートされたときは何もせずに終わったらよくない？
トリガが実行されることのコストを気にしてるのかな。

>>647

たしかにまずいですよね。substr(日付)とやってる処理があって、いま
手直ししてます。ぜんぜん意識してなかったです。。

>>659
その程度の最適化は今時どんなDBMSでもやってくれるよ。
同じで当然。

>>666
流れが読めない人だなあ

select 文を書く時みなさんはどちらを使いますか？
またその理由を教えてもらえると助かります。
ちなみに自分は�@派です。

�@select column from table where integer = 1;
�Aselect column from table where integer = '1';

スクリプトの人はわからんが、Cやってる人なら文字列と数値を
ごっちゃにするのは嫌うんじゃ

>>668
明確に違うものをなぜ同一視する。
文字列である数字と、数値を表す数字では、意味が違う。

>>668
�A　昔はこれしかなかった。

そういえば、7.4 の時代には、�@で int8 を検索してインデックスが使われない〜
ってのは頻出だったな。今は大丈夫だが。

ちなみに '1' だけでは、まだ文字列ではない (unknown) 。
何かしらの型への変換が要求された時点でパースされる。

=# select foo(1);
ERROR: function foo(integer) does not exist
=# select foo('1');
ERROR: function foo(unknown) does not exist

いろいろレスありがとうございます。
自分は>>669さんと同意見なんですが、
絶対 '1' にしろという人が身近に入るもので気になりました。

あと>>671さんの意見でなんとなく理由がわかりました。

暗黙の型変換が行われると速度が遅くなるから
元のカラムの型がintなら1を使うべきだと思ってたんだけど
そう単純な話でもないんだな

リテラルであれば別に型変換があっても実行速度は変わらんしな。

>>671
私はORACLEのバージョン3から使ってますが、当時から
数値はシングルクォーツなしで参照できたが・・・

>>676
私もその少し前からだと思う。マニュアルでは
WHERE DEPTNO = 30 　であるのに 処理系では
WHERE DEPNTO = '30' でないとエラーになった記憶がある。
それで '30' とする習慣になってしまった。
もしかして、私の勘違いだったのかな。

PostgreSQL 8.3.3, 8.2.9, 8.1.13, 8.0.17, 7.4.21

DB２台で冗長構成にしたいのだが、
pg_poolはシーケンスの整合性を取る場合にはテーブルロックが必要なのね。
テーブルロックしない方法は何か無いかしら？
Slony-IIとかpgClusterも同じでしょうか？

難しいなら障害時にDB1からDB2に自動的に切り替わらるといった障害耐性が
無くてもよいかな。単純にレプリケーションが取れていれば。

>>677
そのカラムがvarcharで定義されてたんじゃなくて？

>>679
drbdとか使ってミラーリングしとくとか
自動で切り替わらなくていいならPITRでリモートにバックアップとか

>>679
Slony-I ならテーブルロックしない。(-IIはリリースされてないよ)
pgCluster は、そもそも更新性能が 1/10 になるという噂もあるので微妙。
冗長化だけで良いなら、>>681のとおり、warm-standbyかDRBDが良いかも。

ここで愚痴ってもしょうがないけど･･･

signed/unsigned修飾子とtinyint型がほしいorz

今時誤差の範囲かもしれないけど貧乏症なので
smallintだと入らないけどunsigned smallintなら入りきる時とか
tinyintで十分な時とか悔しくてしょうがないんだ

>>683
DBの実装はよくわからないけどC言語の時は、
shortで宣言してもCPU内ではintで確保して半分未使用になるだけなので
実は速度はintの方が早いっていうのがあったよ。

大量のデータを構造体にしてネットワークでやりとりするときは
packed宣言すればデータ量を節約することはできたけど。
構造体へのポインタ(アドレス)からsizeof(構造体)分をbyte[]にキャスト
して使うような太古の作り方の話だけどね。

今でも一番パフォーマンスが出るのはintだと書かれているよ

http://www.postgresql.jp/document/pg831doc/html/datatype-numeric.html#DATATYPE-INT
参考までに。

>>684
C言語は、intがその処理系で一番パフォーマンスが出るように、と選ばれます
なのでintが速いのは当然です。

>>683
さぁ、ユーザ定義型を自作するんだ！

>>684
DBはそのネットワークと似たようなもんだよ。結局ディスクIOが最大のネックだから。
何億レコードにもなるようなテーブルは1byteでも減らしたくなるのが人情。

>>686
サイズと性能の釣り合いがとれてる（=コストパフォーマンスがいい?)のはintだと
書かれているけど、小さいサイズで構わないときにsmallintよりintの方が
速いとはかかれてないね。

>>689
ごもっともで

みんなポスグレのレプリケーション何使ってる？

レプリケが必要なプロジェクトではポスグレなんか使わない。
１日１，２回のpg_dump

普通はSlony、同期が必要ならpgpool、
バックアップ用ならwarm-standbyで鉄板じゃないの。

>>690
なるほどなるほど。確かに。
では、たとえばsmallintのほうがintよりも速い場合、以下のような記述をするでしょうか？
> smallint型は一般的にディスク容量に制限が付いている場合にのみ使用します。
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　~~~~
どうせならこう書きそうじゃないですか？
> smallint型に収まるデータを扱う場合はsmallintを使用することを推奨します。
> なぜならずっと速いからです。

あえて先の記述をするということは対して速度に差がないのであろうと想像します。
実際に測定するのが手っ取り早いんですけど、ドキュメントから読み取れることも多いと思います。

キモイキモイナニモカモキモイ

>>694
状況によるだろ。
100GBのテーブルと200GBのテーブルのfull scanを比較したら小さい方が速いだろうし。

>>691

うちはpgpool II 使ってます。ハード障害対策で使ってるんだけど、
意外にハード障害が起こらなくて、今のトコ手間だけ２倍以上な印象。
２重化してるせいで、大量更新集中時の読み出し処理とかは、片側から
読むようにしたりとか、変なコツが必要だったりする。

>>693
>>697
やっぱりpgpoolが多いのですねぇ。。。うちはPGclusterのロードバランシング機能はずした状態で使ってんだけど
ほかにそういう人いないかな。選んだ理由はデータ復旧がコマンド一発で楽だからなんだけど。

今PC用でpostgresでデータベースのみ使っています。
今度携帯用データベース作ろうとしたとき
一緒にするか分けるかどっちがいいんですかね…？
mysqlとpostgresを併用するのは速度的にはどうなんでしょ？

質問させてください

vistaにposgreをインストールできたのですが、postgresとつけたアカウントのパスワードで、データベースpostgresにアクセスしようとすると
フリーズしてしまいます。
どうすればvistaでフリーズしないで使えるようになるのでしょうか？

>>700
フリーズってPC(OS)丸ごと? クライアントアプリだけ?
アクセスしようとしたクライアントはナニ?
postgres以外のアカウントだとどぉう?

っていうか、posgreってなんだよ。

>>699
別にPostgreSQLで困りはしないだろ。

>>699
どっちでもいいので、DBは統一しておいた方が楽。
特にこだわりがなければPostgreSQLでいいでしょう。

http://www.postgresql.jp/落ちてる？

>>704
そうみたい

>>699
併用しても問題ないけど、管理の手間を考えたらposgres一本の方がいいよ。
キャッシュの割り当てとかも一括だし。運用に携わる人はpostgresもmysqlも
どっちもプロフェッショナルなの？両方混ぜると似たようなコマンドで混乱すると思うんだけど。

あとマスタとか共通利用できないの？できるんだったらマスタテーブル一個の方が
更新も楽だしいいと思うけど。ものすごい負荷の高いシステムで将来的に
DBをわける可能性が大きいなら、今からインスタンスを分けておく意味も
あるかもしれない。

もし分けるとしても同じDBMSのほうがよさげに思う。
同じシステムのPC版、携帯版という違いであれば同じDBMSにしといたほうがいいよ。

>>701
DOSからアクセスしたのですが、passを入力してenter押すとOSごとフリーズしてしまいます。

>>708
ローカルマシンのPostgreSQLインスタンスにpsqlで接続しようとしたらOSごとフリーズってことですか？

>>702、703、706、707
みなさん、ありがとうございます。
postgresで統一したいと思います。

PCと携帯で違うデータを見せたいのですが
マスタテーブルも一つにした方が良さそうですね・・・。
ありがとうございました。

統一にしても、DBクラスタを別にしてPostgreSQLを複数起動するとか、
DBを別にするとか、スキーマでわけるか、テーブルで分けるか、キーで分けるか、

>>709
そうです、ネットで調べた通りにやったのですが無理でした。

>>712
まずメモリをチェックしなさい。
次にＨＤＤをチェックしなさい。

Ｌｉｎｕｘ(Unix)ならこんな事で落ちない。
うんともすんとも言わない。メッセージが出ない。
保護違反とかのエラーは全てメモリエラーと思っていい。

メモリエラーの結果、誤動作して保護違反ということはあるかもしれないが
保護違反とメモリエラーとは直接の関係は無い。

保護違反じゃなくて、理由のわからないフリーズはハードを疑ったほうがいいけどな。
フリーズってのが全体が止まるのならな。

ハードだろうね。
保護違反ならメッセージ出るか再起動するかする

ということは、自分のパソコンを修理に出すべきなんでしょうか？
普通に使っている分には、まずフリーズしないのですが・・・SP1入れようとするとフリーズしますが・・・。

ノートで熱暴走してるとか

ないか

>>717
だめじゃん

アンチウィルスソフトで引っかかってUAEの警告だそうとしてるだけとか

ないか

pgAdminではつなげないの？

sp1入れてフリーズって普通じゃないよな。
ｐｃ新規購入をお勧めする。

PC買い替える前にOS再インストールだな。

の前にH/Wいろいろチェック

Explaining Explain日本語版ドラフト ? NPO法人 日本PostgreSQLユーザ会
http://www.postgresql.jp/blog/56
にある Explaining Explain の日本語版読んでみたいんですが、
パーマリンクの変更があった影響なのか
単にもうファイルがないのか読めないでおります。
どなたかファイル持ってる方いらっしゃればどこかにあげていただけないでしょうか？
JPUG公式ぽいんですが、コメントのSPAM荒れっぷりを見てもメンテされてない可能性があるので
こちらにきてみました。

コリコリにDBチューニングしたいわけではなくて、SQLかく時にある
ちょっとした迷い（結果同じだけどどっちの書き方がいいかな？）
をクリアする助けになればなぁと思ってる感じです。

>>724
困った時の、Web Archive
http://web.archive.org/web/20060429195622/http://blog.postgresql.jp/images/Explaining_Explain_ja.pdf

ぐぁ・・・基本的なことが抜けておりました。
大感謝です！

すみません
質問させて下さい。

PostgreSQL Ver 8.3
WindowsServer2003
でDBを運用しているのですが、
現在dataフォルダが初期インストールパスのままになっております。
別ドライブにdataフォルダを移動しようと試みて、
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.3\ImagePath
を変更してみたのですが、
変更先がCドライブ以下なら変更可
他のドライブは変更不可（サービスが起動しない）
となってしまいます。

この場合、dataフォルダを退避して、PostgreSQLの再インストール
しか方法は無いのでしょうか？
何とかdataフォルダの移動だけで済ませたいと考えております。

アドバイスを宜しくお願いします。

上げてみました

うちのWinXPでは実行ファイルはC:にインストールしてあって、
-Dで指定するデータ領域は別のドライブにしているけど、普通に動いてる。

>>729さん
ありがとうございます。

多分initdb.exeで -D 以降に移動させたいファイルパスを指定
してあげればいいのかなと思うのですが、initdb.exeの使い方が
いまいちわかりません・・・。

新規にデータ領域を作成して、そこに既存のdataフォルダをコピー
するような感じでいけるんでしょうか？

initdb.exeはコマンドプロンプトで実行するのでしょうか？

もしわかれば教えてください。
宜しくお願い致します。

既存のデータ領域があるなら丸ごとコピーして-Dでそっちを指すようにすれば動くんじゃないかな。
initdb はコマンドプロンプトで --help とかつけて動かせば説明が出るけど、
>>1のドキュメントを読んだ方が良いかも。

>>731
アドバイスありがとうございます。

取り合えず調べて、空の別ドライブのdataフォルダにinitdbの実行し
成功するまでは出来ました。

その後、新規dataフォルダを既存のdataフォルダに上書きして、
サービスの実行・・・・・・起動不可

再度、空のdataフォルダを作成して、initdbを実行。
今度はそのままサービスの起動・・・・・・起動不可

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.3\ImagePath
の値はinitdbの指定パスと同じにしているのですが、他に設定
が足らないのでしょうか？

>>732
コントロールパネル -> コンピュータの管理 -> サービス を開いて
PostgreSQLのプロパティを見て、[全般]タブの「実行ファイルパス」が
レジストリと同じ値になっているか確認して味噌。

っていうか、レジストリを直接弄るってはじめて知った。
scコマンドを使うものばかりだと...。

システムのログでも見れば。

>>733
>>734
ありがとうございます。

>レジストリと同じ値になっているか確認して味噌。
ここは同じになっております。
多分、レジストリの値を参照して、ここに表示しているようですね。

>システムのログでも見れば。
今見てみました。
postgres cannot access the server configuration file "F:/data/postgresql.conf": No such file or directory

とのエラーメッセージを発見しましたが、該当の
F:\data\postgresql.confは存在します。

なぜでしょう？

>>735
元Unix系でWindowsに移植されると、あるのにNo such file... って出て悩ませるときがあるよねぇ。
ユーザpostgresに読み取りアクセス権限が設定されていないとか。
そんときゃpostgresql.confだけじゃなくて、/dateディレクトリ下の全てのファイルとディレクトリを
設定し直さなきゃだが?

psql (PostgreSQL) 8.2.4　を使ってますが、バックアップとリストアをしようとしています。
pg_dumpallでファイルにバックアップした後リストアすると文字化けが発生してしまいます。
登録データーがSJISなのですが、OSはUTF-8　CJKになってる為と思われますが文字化けを回避する方法はありますでしょうか。
また、上記状態でpg_dumpallしてあるファイルを文字化け無しにリストアする事は出来ないでしょうか。
宜しくおねがいします。

つ nkf

バックアップ前・後のDBの文字コードは同じ？
FTPとかを利用してファイル移動とかする際に
文字化けが入ってしまう可能性は無い？
使用しているOSは？

もうちょっと情報プリーズ

>>738

早速のご回答、ありがとうございます。
バックアップ前・後のDBの文字コードは同じです。
ｐｏｓｔｇｒｅｓはfedora上で動いており、XPからwinSCPとputtyを使ってアクセスしてますが
バックアップしておいた時点のDBを再現したいので最悪はputtyでログインして
fedoraのディスクにそのままバックアップとリストアが出来れば良いです。
出来ればwinscpでローカルのXPにバックアップ出来れば嬉しいですが…

nkf、ググって見ました。pg_dumpallしてしまったファイルに適用しても元に戻せるのでしょうか。ちょっとやってみます。

>>738
>FTPとかを利用してファイル移動とかする際に
>文字化けが入ってしまう可能性は無い？

記憶が曖昧なのですが、fedora上でputtyでログインし
バックアップとリストアしても文字化けした様に思います。

×　記憶が曖昧なのですが、fedora上でputtyでログインし
○　記憶が曖昧なのですが、fedoraにputtyでログインし

>> 737
そもそもDB作るときに文字コード何にした？

$ psql -U postgres -l
List of databases
Name | Owner | Encoding
------------------+-----------+----------
***** | ******** | UTF8

この一番右のEncodingの値

>>742

-bash-3.2$ psql -U postgres -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
test | postgres | EUC_JP
test1 | postgres | SQL_ASCII
(5 rows)

となってます。test1が目的のDBなのでSQL_ASCIIで良いでしょうか。

>>743
テストしてみた

test1が元データだよな？

## 吸出し部分
# DB作成
createdb -U postgres -E SQL_ASCII test2

# test2にSJISデータを流し込む（手動）

# データ吸出し
pg_dump -U postgres test2 > test2.sql

# SJISからUTFに変更
cat test2.sql |nkf --ic=sjis -w > test2.sql.utf

# ここでFedora上で読めるようにUTFに変換されている
# winscpでwindowsに転送してみた
test2.sql => SJIS
test2.sql.utf => UTF8
の文字コードであることを確認

# 戻すテスト
createdb -U postgres -E SQL_ASCII test4
cat test2.sql.utf |nkf -s |psql -U postgres test4

これで無事に戻せた。

特に問題なさそうだが、何処でダメなんだ？

>>744
実験までして頂いてお手数掛けます。

>test1が元データだよな？
そうです。pg_dumpは部分的ににかバックアップしない様なので使ったことが無いのですが

pg_dumpall > backup.sql
とした時にbackup.sqlが既に壊れているのでは無いかと考えていて
今、実験中なのですが自信が無いです。

単純に
pg_dumpall > backup.sql
psql -f backup.sql
とした時に標準出力（linuxのUTF）で化ける事って無いのでしょうか。
backup.sqlはUTFに変換されてしまいますよね。
それとも、実験して頂いた手順に
># SJISからUTFに変更
とあるのでSJISのままなのでしょうか。

>>745
とりあえずbackup.sqlの文字コードを調べてみ。

こんなかんじで。

$ nkf --guess test2.sql
Shift_JIS
$ nkf --guess test2.sql.utf
UTF-8

ここまで書いて思ったんだが

test | postgres | EUC_JP
test1 | postgres | SQL_ASCII

ってことはEUC_JPのやつとSJISのデータを混ぜて出力してる？

>>746
やってみました

-bash-3.2$ nkf --guess backup.sql
BINARY

でした。

ちなみにbackup.sqlの先頭の方で

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET escape_string_warning = 'off';
　｜　｜
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT ALL ON DATABASE template1 TO postgres;
DROP DATABASE test;
CREATE DATABASE test WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'EUC_JP';
DROP DATABASE test1;
CREATE DATABASE test1 WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'SQL_ASCII';

となっていてエラーも出ていない様なのですが、多種類の文字コードだと出力出来ないのでしょうか。



backup.sqlの容量が900Mバイトもある為かサーバの反応が遅すぎるので、テスト用のサーバーを買ってこようと思います。

>>747
>多種類の文字コードだと出力出来ないのでしょうか。
DBごとにdumpしてみたらどうなのさ？ もうちょと自分で思考錯誤しる。

もしかして: 試行錯誤

>>748

DB毎にdumpしてみましたが文字化けが発生している様でした。
別サーバーを立てて文字化け部分を切り出そうとしている所ですが
昨日の初書き込みから、お蔭様でだいぶ進展した感じです。
暫く自分でやって見てまた判らない事がありましたら質問させて頂きます。
本当にありがとうございました。

環境変数のPGCLIENTENCODINGとか？

DB毎に文字化けってどういうこと？
例えばEUC_JPで出したものをEUC_JPとして見ようとしても化けてるの？

文字化けしてると勘違いしてるだけじゃね？

DBをASCIIでつくっちゃったんならクライアントの
文字列をそのままバイト配列として格納してくれるんだっけ？

新しいDBをUTFで作ってプログラム組んでSELECT->INSERTしたほうが
早そうだけなあ

現在レンタルサーバを借りているのですが、データベースのバックアップをしようと思い、pg_dumpをした所「WARNING: ignoring unconvertible UTF-8 character」と言ったメッセージが表示されました。
ただ一応ダンプデータは作られたので、そのデータを元に復旧を試みた所、一部データが欠落していました。

ググッてみて文字変換の問題のような気がしたのですが、ユーザーレベルで解決する方法が分かりませんでした。
ttp://www.google.com/search?num=50&hl=ja&lr=lang_ja&q=WARNING:+ignoring+unconvertible+UTF-8+character&revid=994610375&sa=X&oi=revisions_inline&resnum=0&ct=broad-revision&cd=1

その為何か良い手ががございましたら、アドバイスをいただければと思います。
よろしくお願いします。

>>754
DBがUTF-8でシェルのLANGがUTF-8じゃないとかじゃないの？
繰り返しになるけど、
環境変数のPGCLIENTENCODINGとか？

PostgreSQLって基幹業務でも安心して使えますか？

>>756
管理者が詳しければ安心
管理者が詳しくなければどのRDBMS使っても安心できない。

DB本体はともかく、基幹業務となるとバックアップとレプリケーションかなぁ。
一番の問題はPostgreSQLを扱えるSIの質と数だろうけど。

1万ユーザーで同時使用は1000人くらいですが
パフォーマンス的には問題ないでしょうか？

使用状況や環境にもよるでしょ。

具体的なトランザクション数とまでは言わないけれど、一口に同時使用が
1000人といっても、それはフロント部分の作りに大きく依存するでそ。
それだけじゃなんとも言えない罠。ただ、今現在そのシステムがOracle等で
動いているのなら、パフォーマンス的にはPostgreSQLでも動くはず
というのはいえる。

>>755
ありがとうございます。
確認した所下記のようになっておりましたorz。
LANG=ja_JP.eucJP
PGCLIENTENCODING=EUC_JP

現時点でレンタルサーバの設定は触れないので、
自分で似たような環境を作り試してみた所、
UTFからEUCに変換できない文字(?など)があると現象が発生するような感じでした。

多分データベースがUTF-8なのに対し、PGCLIENTENCODINGがEUC_JPの為、
pg_dump時にEUC_JPにコンバートしてしまい、EUCに変換できない箇所でWarningが出ているのかなと思いました。
なおLANGは無関係そうな感じでした。

その為、PGCLIENTENCODINGの設定を変更してもらう方向で検討してみたいと思います。
どうもありがとうございました。

>>762の上から8行目は文字コード0xE28094のUTFの文字を貼り付けたのですが、文字化けしてしまいました。
多分pg_dumpでも同じようなことがおきてWARNING表示になったのだと思います。

>>762
自分の環境変数は自分で変えられるよ
export PGCLIENTENCODING=UTF-8
ってやってからpg_dumpしよう

連投ごめんです。
記憶違いでなければ、PGCLIENTENCODINGが設定されていない場合はLANGが使用されたと思うよ。

んで、ダンプファイルを突っ込むときにエンコード周りのエラーが出た場合なんだけど、
ダンプファイルの中の set client_encoding という行がある場合はそれが
優先されているので、そこがおかしくないかチェックしてみるとよいです。

>>764
レスどうもです。
一応自分で構築したサーバーで>>764の記述と
export PGCLIENTENCODING=EUC_JP
でのpg_dumpの出力結果を元に>>762のような感じかなと推測しました。

実際に使用しているレンタルサーバーの方は、
自分の判断だけでは手がつけられないため、試してないですが。

>>765
私の構築したサーバーの設定は下記ですが、問題なかったのはLANGにUTF-8を指定してたからということですね。
LANG=ja_JP.UTF-8
PGCLIENTENCODINGはなし

危うく、PGCLIENTENCODINGの設定を無くすだけで終えるところでした。
データベースは複数ありますがどれもUTF-8なので、PGCLIENTENCODINGをUTF-8にする方向で対応したいと思います。

後、手元に問題のダンプデータがないので、明日Warningのでる場合と出ない場合のset client_encodingの記述をチェックしてみます。
どうもありがとうございました。

pg_dumpに-eって無かったっけ？

-E または, --encoding= でしょ、あるよ。

>>751
今回は関係なかった様です。ありがとうございました。

>>752
EUC_JPのＤＢは化けてませんでした。
と言うか、SQL_ASCIIのDBを削除する為に仮に作っただけだったので、データーが殆ど入ってなかったと言うのもありますが。


追伸
SJISの文字化けで質問していた者です。他の業務が入ってしまい追試が遅くなりました。
色々やってみましたが、ＤＢから携帯からのメールのみ削除してバックアップした所文字化けがなくなりました。
携帯の特殊文字に関連しているのかも知れません。とりあえずプログラマにその旨を伝え、対応してもらう事になりました。
時間が取れたらこちらでも詳しく調べようと思います。
ご教示頂いた皆様、本当にありがとうございました。

追伸２
４GのメモリとQuadのマザー買っちゃいました。素人が試行錯誤するには反応が早くて良いですね。

>>765
set client_encodingチェックしました。
すべてのダンプがEUCでしたorz
どうもありがとうございました。

それと、私が試した限りではPGCLIENTENCODINGが設定されていない場合、
LANGではなく、データベースのEncodingが使用されているようでした。
その為、下記のaaaaというデータベースのダンプはEUC_JPで作られ、
bbbbというデータベースはUNICODEで作られていました。

　List of databases
Name|Owner|Encoding
-----+-----+--------
aaaa |ccccc |EUC_JP
bbbb |ddddd |UNICODE


>>767、>>768
使用しているバージョンが7.4.19なのですが、このバージョンでは-Eと--encoding=はないようです
(pg_dump --helpで調べましたが、そのオプションは存在せず、試してみてもエラーになりました)。
出来れば既存の環境には手を加えたくないので、PGCLIENTENCODINGには手をつけず、
pg_dump -Eで対応できれば良かったのですが。
何か良い案があるようでしたらアドバイスいただければと思います。
今はまだPGCLIENTENCODINGを変更すると、既存の環境に悪影響が出ないか検証できていない為、
変更できていないので。

なおpg_dumpのマニュアルは下記を参考にしました。
ttp://www.postgresql.jp/document/pg812doc/html/app-pgdump.html
ttp://www.postgresql.jp/document/pg746doc/html/app-pgdump.html

>>765
PGCLIENTENCODINGが設定されてない場合の補足ですが、
下記のサイトに以下の記載がありました。

> -E encoding
> --encoding=encoding
> 指定した文字セット符号化方式でダンプを作成します。デフォルトでは、ダンプはデータベースの符号化方式で作成されます
> （PGCLIENTENCODING環境変数を好みのダンプ時の符号化方式に設定することで、同じ結果を得ることができます）。
ttp://www.postgresql.jp/document/pg812doc/html/app-pgdump.html

この文章の「デフォルトでは、ダンプはデータベースの符号化方式で作成されます」というのが、
>>770のEncodingの部分を指していると思われます。

テーブルに登録している「folderpath」を
SQLのWHERE句にて
Like検索したいのですが、
以下結果となりました。


1. folderPath = "c:\\test" OK （データ取得成功）参考
2. folderPath like "c:%" OK
3. folderPath like "c:\%" OK
4. folderPath like "c:\\%" NG （取得データ0件）
5. folderPath like "c:\t%" NG
6. folderPath like "c:\\t%" NG


\が入っているフォルダパスを
検索する良い方法はないですか。

よろしくお願いします。

>>772
フロントエンド(psqlなりプログラミング言語なり)がエスケープする分とlikeがエスケープする分が必要。

folderPath like 'c:\\\\%'

c:\% がOKなのは、 c:\% → c:% → c:% だからだな。

MYSQLからPostgreSQLに乗り換えたいと思いインストールしてみたんですが
phppgadminから起動が出来ません

環境は
apache2.2/PHP5/PostgreSQL8/WindowsXP
php.iniの　php_pgsql.dllの部分の；は解除してますが
phpinfoにもPostgresが出てきません
インストールが失敗しているんでしょうか？
一応　ユーザーアカウントを新しくrootとして権限をかけて作りました
Linux環境のサイトが多くWindowsの情報が無く困ってます

Phppgadminのエラー詳細は
データベースをサポートするように
PHP のコンパイル・インストールがされていません。
configure の --with-pgsql
オプションを用いて PHP を再コンパイルする必要があります。

phpinfoに出てこないんじゃ、そもそも無理でしょ。
PostgreSQLが使えるようにビルドしなおすか、
ビルドしたバイナリ持ってくるしかない。

ちょっとググってみたけど、extension_dirは設定してる？
そして、extension_dir にちゃんと php_pgsql.dll は入ってる？

>>775
phpinfo();
でpgsqlのセクションが表示されなければ
たぶん、extensions_dirの設定ミス。

つか、Win32版のバイナリなら再コンパイルとか不要で、
ほとんどのオプションはphp.iniの設定変更のみで使用できる。

>>775
Bug #44905 PHP 5.2.6 fails to load PostgreSQL related libraries 
http://bugs.php.net/bug.php?id=44905

これかな?

>>775
Windowsなら大人しくxampp使っておけ
http://www.apachefriends.org/jp/xampp-windows.html
MySQLとかゴミが一緒に入るけど起動しなければ無害
そしてPostgreSQLは別途インストール