PostgreSQL 2テーブル目

PostgreSQLについて語って2万テーブルをめざしましょう。
関連アプリケーション特有の話題は、以下のスレッドへ。
pgsql-mlのヲチもここで。

前スレ
[PostgreSQL]
http://pc2.2ch.net/test/read.cgi/php/989375812/

☆関連スレッド
[PHP + PostgreSQL]
http://pc2.2ch.net/test/read.cgi/php/983128806/
[PerlでPostgreSQL]
http://pc2.2ch.net/test/read.cgi/php/999249463/
[MySQL vs PostgreSQL]
http://pc2.2ch.net/test/read.cgi/php/989341364/

>>321
突破口って。。。
マニュアル読めばいいだけと違うの？

CREATE TABLE account_table ( expire_date TIMESTAMP, name TEXT );

のようなものがあって、

SELECT * FROM account_table WHERE expire_date > now() AND name = 'foobar';

のような問い合わせを頻繁に行なっています。実際にはテーブルも問い合わせ
ももっと複雑です。

ここで、上記SELECT文を、1引数('foobar' 部分)のPL/pgSQLの関数にしてしま
いたいのですが、そういうことは可能でしょうか?

CREATE FUNCTION get_account( TEXT ) RETURNS ??? AS 'BEGIN ???; END' LANGUAGE 'plpgsql' ;

のような概観になるかなとは思うのですが、戻りの型などがわかりません。
EXECUTEを使うのでしょうか? 教えてください。

# Viewのほうが無難でしょうか? プログラム側にwhere句を書き忘れる可能性
# があるのが嫌なんですよね…

CREATE FUNCTION get_account( text ) RETURN SETOF RECORD AS '
[...snip...]
' LANGUAGE 'plpgsql';

すこしスレ違いかもしれませんが、どちらに質問していいかわからなかったので、
ここで質問させてください。

ある大きな２つのテーブルをJOINしたいのですが、そのままJOINすると
時間が掛かりそうなので、片方を一時テーブルに書き出して、それをJOINしようと
思ったのですが、一時テーブルを作った後、違うページで一時テーブルとJOINしようと
するとテーブルが存在しないと言われてしまいます。
PHPから一時テーブルを作成した場合、その一時テーブルにアクセスできるのは
どのタイミングまでなのでしょうか？
マニュアルにはセッションの終わりまでのようなことが書いてありますが、
PHPのセッションは終了してません。
それとも、一時テーブルを作成しないでもいい方法などありませんでしょうか？
よろしくお願いします。

環境は、PHP 4.2.3 PostgreSQL 7.2.3です。

すいません、自己解決しました。

>325
pconnect使わなければ、PHPからPostgreSQLへのセッションは
毎回切った張ったされますが。

>>325
一時テーブルをVIEWにするかサブクエリにするのが一般的だけど、
性能上の問題からその一時テーブルの結果を使いまわしたいなら
名前をつけてテーブル作成するしかないかな。

pg_pconnect使ってもApache/PHPの状態によってはDBセッションが同一にならないから、
ページを跨いだ一時テーブルの利用はできない。

PostgreSQL 7.2 のPL/pgSQLについて教えてください。
CREATE TABLE reg ( d TIMESTAMP );
なるテーブルがあり、d には「現在からn秒後の時刻」をINSERTするようにし
ています。現在はプログラムから
INSERT INTO reg VALUES ( NOW() + INTERVAL ''100 sec'' );
のようにしているのですが、これをPL/pgSQLで書きなおそうとしています。
で、
CREATE FUNCTION f( BIGINT ) RETURNS BOOL AS '
BEGIN
INSERT INTO reg VALUES ( NOW() + INTERVAL ''$3 sec'' );
RETURN TRUE;
END
' LANGUAGE 'plpgsql';
としてみたのですが、$3 が展開されないのか、足し算が行なわれません。
どのように書いたら望む結果になりますでしょうか? 教えてください。

>>327, 328

ありがとうございました。
pconnectを使って、ページをまたいで一時テーブルを使おうとしてたので、
テーブルが残ってるのか残ってないのか、よくわからなくて混乱してました。

pg_connectを使って、なんとか同じページ内で一時テーブルを使って処理してます。

VIEWは、まだ勉強中なので試行錯誤してみます。

教えてください。
DISK障害でテーブル内データのほとんどが消えました。
実体の/usr/local/pgsql/data/base/内は無事のようです。
/base/DB名からなんとかリカバリできないでしょうか？
postgresql6.5.3です。

ZDNetの誤字ひどいな。
『SRA 最新PostGreSQLをベースにした「PoerGres on Linux」「PowerGres Plus」を発表』
http://www.zdnet.co.jp/news/0308/07/nj00_srasql.html

age

perl、DBIでのトランザクションについてなんですが、
今日のレコード数を持ってきて、それを+1した値で、
新たにINSERTするというときは、
↓みたいな感じでOKなのでしょうか？
（他のユーザが登録している場合でも、値のずれが生じないですよね？）

よろしくお願いします。

$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;
eval {
$val = get_value("SELECT count(*)+1 FROM a WHERE 今日のもの");
$ret = insert_value("INSERT INTO a (foo, ...) values ($val, ...)");
$dbh->commit;
};
if ($@) {
warn "Transaction aborted because $@";
$dbh->rollback; # undo the incomplete changes
}

７．４の自動バキュームって使えそう？
フルバキュームまで自動でやってくれるの？

何を勘違いしたか今までポスタージャSQLって読んでたよ…

私信に近い雑談をMLに流すな〜！

>>334
ズレはしないかもしれないが、カチ合う可能性はあるよ。
それを防ぐには、SERIALIZABLEにするか明示的にテーブルロックする。

cygwinインストール中に落ちてしまって、インストールできないのですが
どうすれば。。あっここで聞くことじゃないですか。。そうですか。。

>335
contrib/pg_autovacuum/README.pg_autovacuum より。
vacuum fullに関しては、xid使い切りに関しては考えてるけど、
普段はvacuum止まりということかな。

Vacuum and Analyze:
-------------------

pg_autovacuum performs either a vacuum analyze or just analyze depending
on the quantity and type of table activity (insert, update, or delete):

- If the number of (inserts + updates + deletes) > AnalyzeThreshold, then
only an analyze is performed.

- If the number of (deletes + updates ) > VacuumThreshold, then a
vacuum analyze is performed.

deleteThreshold is equal to:
vacuum_base_value + (vacuum_scaling_factor * "number of tuples in the table")

insertThreshold is equal to:
analyze_base_value + (analyze_scaling_factor * "number of tuples in the table")

The AnalyzeThreshold defaults to half of the VacuumThreshold since it
represents a much less expensive operation (approx 5%-10% of vacuum), and
running it more often should not substantially degrade system performance.

PostgreSQL 7.2.1使ってるんですけど、
運用中にREINDEXとかDROP INDEX〜CREATE INDEXとかの
INDEXの再生成って行って良いんですかね？

念のため運用を一時的に止めた方が良いんでしょうか？

やってもいいっすよ。ロックかかるけど。

バッチグーage

MLヲチスレでつか?(藁

>>344
1をみてくらさい。

おまえら、7.4 は高速化が図られてるようですよ。

【PostgreSQLウォッチ】第1回 PostgreSQLの次期版7.4の性能を検証
http://itpro.nikkeibp.co.jp/members/SI/oss/20030911/1/

select hoge,hogehoge from table group by hoge;
をしたいのですがどのようにしたら実現できますか？
グループ化してないhogehogeもselectしたいのです

select hoge,hogehoge from table group by hoge,hogehoge;

>>347
hoge|hogehoge
aaaa|bbbb
aaaa|cccc

とあったら、何を出せバインダー

おもろい
http://www.39001.com/cgi-bin/cpc/gateway.cgi?id=dolphin

>>350
「メル友みっけ」か．．．

ここが一番上にきとる。
移転したんじゃなかったっけ？

PostgreSQL 2テーブル目
http://pc2.2ch.net/test/read.cgi/db/1056944337/

バイナリデータの挿入の仕方がマニュアルに書いてない
気がするんですが、どうやるんでしょう。
create table images (imagname text, img bytea);
このimg列に /tmp/hoge.jpg を挿入したいです。

>>352
しらんかった。
っていうか、ドラゴンボール板さわぎのどさくさで、気付いてなかった。

ということで

--- 糸冬 了 ---

tes

適当な文字列が入っているcolumnにたいして、
大文字小文字関係なく、検索文字をマッチさせるにはどうしたらいいの？

select * from foo where text_column ^ lower('BAR') or text_column ^ upper('BAR')

とかだと、「Bar」みたいのはヒットしないんだよなぁ。

あげとく

>>356
text_column ~* 'BAR'

DBのみのネタはDB板へ
ttp://pc5.2ch.net/db/

>>358
THX

~が^になっていた orz

postgres7.3を使用予定ですが、REINDEXの使用方法を教えてください。

REINDEX対象のテーブル名をpg_stat_user_tablesより
取得しながら、REINDEXを全テーブルに順番にかけるシェルを作ろうと思っています。
その際、pgbashを使用し、ｃｒｏｎで自動実行を予定しています。

もしソースコードがわかれば教えて下さい。
またみなさんはどんな方法で、行っていますか？

postgres7.3を使用予定ですが、REINDEXの使用方法を教えてください。

REINDEX対象のテーブル名をpg_stat_user_tablesより
取得しながら、REINDEXを全テーブルに順番にかけるシェルを作ろうと思っています。
その際、pgbashを使用し、ｃｒｏｎで自動実行を予定しています。

もしソースコードがわかれば教えて下さい。
またみなさんはどんな方法で、行っていますか？

ネットワークから接続しているユーザのIPを
抜き出してログを取りたいのですが
SQLコマンドで、自分はどこから接続しているかを
確認できる関数ってPostgresにはありませんか？

とにかく、接続しているクライアントのIPさえ分かればOKです。
宜しくお願いします。

管理人が拳銃発砲!!!
http://bbs2.orange-e.net/?id=binbin

apache2.0.5とPostgreSQL7.4の環境でmod_auth_pgsqlを使ってBasic認証を
行なっているのですが、認証するたびにpostgresユーザーで下のようなプロセスが
溜まっていきます。
postgres: apache database 127.0.0.1 idle
そして挙げ句の果てに
mod_auth_pgsql database connection error reset failed FATAL:
connection limit exceeded for non-superusers とメッセージが出てサーバーエラー
になってしまいます。多分mod_auth_pgsqlがパスワードのデータベースへ接続した後、
切断してくれないからじゃないかと思うのですが、どこか明示的に設定しなければ
ならない場所があるのでしょうか？

>>365
Auth_PG_connection_reuse がonだとコネクションプーリングするみたいですよ。
デフォルトはoffのようだが。

レスありがとうございます。早速 Auth_PG_connection_reuse off を設定して
みました。(それ以前は、Auth_PG_connection_reuseは何も設定していません。)すると
Invalid command 'Auth_PG_connection_reuse', perhaps mis-spelled or defined
by a module not included in the server configuration
となりサーバーエラーが返ってきました。別の原因は考えられますか？
ちなみにmod_auth_pgsqlは2.0.1、ディストリは fedora core 2です。

>>367
ざっとソースを眺めてみた限りにおいて、2.0.1では切断をしていなさそうだ。
postgresqlの設定を変更して接続許可数を増やすか、
2.0.2b1にUpするしかないんじゃないか。

わざわざ調べて頂いてありがとうございます。バージョン特有の問題なのですね。
当面の間は、max_connectionsを増やして、cronで定期的に service httpd graceful
を走らせてしのごうと思います。お世話になりました。

>>368
fedore core 2 の mod_auth_pgsql-2.0.1-4.1.src.rpm のSPECファイルの
バージョンを書き換えたらそのままmod_auth_pgsql-2.0.2b1.tar.gzから
rpmをbuildできました。早速アップデートしましたところ、当該不具合は
めでたく解消されました。重ねてお礼申し上げます。

行き詰まってしまって解決できなくなってしまったので、
質問させて頂きます。

TESTというテーブルの中に、A,Bというカラムがあり、
Aでgroup byしてから、Bをdistinctしたものの
カウントを取りたかったくて、下のようなSQLを組んだのですが、
Bをdistinctできてないものをgroup byした結果が、
出てきてしまいます。
私が意図したSQLはどうすれば実現できるかご教授おねがいします。

SELECT A,count(distinct(B)) FROM TEST group by A；

>372
意図したこととは違いましたが、そのSQL見てて、
ふと思いつき、解決できました。ありがとうございます。

データが8000件位入ったテーブルtestに
さらに8000件近いレコードを追加したいのですが
insert文をエクセルで8000個作ることまではできたのですが、
これをポスグレに読ませる際、一括でコピペするとパースエラーになってしまいました。
10件位づつこまめにコピペするとエラーは起きないのですが
時間がかかりすぎるのでファイルから読み込ませたいのです。
ｸﾞｸﾞった所"copy"だと扱えるデータがタブ区切り形式らしいのですが
insert文が書かれたファイルを読み込ます事はできるのでしょうか？
PostgreSQL7.2.3です。

>>374
psql -f filename

>>375
レスありがとうございます。
早速ｸﾞｸﾞりましたが
psql -f filename db名で
http://jpug.postgresql.jp/document/pg721doc/reference/app-psql.html
にありました。
ありがとうございました！

不安なのでちょっと質問して宜しいでしょうか。
timestamp型のmodify_dateというカラムがあったときに
たとえば、2004年11月4日のデータを取りたいというときは
where to_char(modify_date,'YYYY/MM/DD) = '2004/11/04';
という風にしてるのですが、これよりもっと最適な検索法？というのが
ありましたら教えて貰えますでしょうか。
to_charで一度charに変換してるから速度的にどうかなぁと思ってるのですが…。

>>377
date型にキャストした方が速いかも。
WHERE modify_date::date='2004/11/04';

>>378
おお！なるほど、そういうやり方があったんですね。
感謝ですm(_ _)m

>>378
あ、この方法だと、'2004/11/04'もdate型に変換するだろうから、
結局>>377とあまり違わないのかな?
俺はこの方法を用いているのだけど。

>>380
377では速度の最適を求めるような書き込みになってしまいましたが
to_charでYYYY・・・・と変換するのに抵抗を感じてしまって他のやり方が
あればと思って質問したので、十分満足しています。もっと最適なものが
あれば尚いいとは思いますが、378さんのやり方を使っていきたいと思います。

ポストグレスキューエルそろそろ囓ってみようと思うんだけど
こんなボンクラはまずどんな本を読めばいいですか？
教えて下さい ＩＳＢＮコードのみで( ´,_ゝ`)

初めてこのDBに挑戦したら
initdb: command not found
でいきなり死亡しました。
http://www.terra-intl.com/unix.html
↑に書いてある通りにやったら
7. のinitdb でだめだったんですが、
このような経験のある人いませんか？

>>383
せめて、使ったCygwinとPostgreSQLのバージョンくらい載せるべし。
このページと同一のバージョンが入手できた訳じゃないよね？
あるいは、PostgreSQL と Cygwin でぐぐって、新しめのページをを探すべし。

同じバージョンしか落とせなかったんですが、古いみたいですね。
新しいので朝鮮します。

http://discypus.jp/pukiwiki/pukiwiki.php?Cygwin%2F%A5%A4%A5%F3%A5%B9%A5%C8%A1%BC%A5%EB#content_1_15
今度はこのページ見てやったんですが、「Cygwin-X」なんてできないし、
デスクトップのcygwinを起動しても一瞬黒いウインドウが出るだけで
何も起こりませんでした。
昨日はできたbashもできなくなって余計ひどくなってしまいました。
環境変数は本当にシステム環境変数でいいのでしょうか？
昨日はユーザー環境変数で設定したんですが。

>>386
Cygwinなんて使わずに8.0入れたらだめなの？

特定のレコード(たとえばＩＤがaaaのレコード)がなければインサート、
あればアップデートという処理をしたいのですが、
selectであるかどうかチェック＞insertかupdateに振り分ける
という方法以外に簡潔にやる方法はありますでしょうか？

>>386
cygserver や postmaster (postgres.exe) をサービスにしないのなら、
PATH や CYGWIN や TZ はユーザ環境変数で十分。

いま、Cygwinのデフォルトパッケージ + PostgreSQL を入れて、
cygservder, CYGWIN環境変数の定義、initdbの実行までやってみたけど、
特に問題なし。（OSはWindows2000 Pro SP4。作業時間は10分で完了）

もっとも、>>387 のいうとおり、8.0 beta 入れてもいいと思うけどな。

http://allabout.co.jp/career/database/closeup/CU20040813A/
結局ここの通りにやって成功しました。
これからの問題はJavaServletでどう扱うのかってことだけです。
なぜかPHPの解説サイトばっかなんだけれども・・・。
8.0は解説無しじゃ難しいと思ったので、辞めておきました。

質問させて下さい

PostgreSQL(7.2.3)を使ったWebサイトを作っているのですが、最新項目の表示のため
日付(date型)でorder byしてますとえらく処理に時間がかかっています。
そういうものなのでしょうか?
日付でソートしたい場合の効率的な方法などがありましたら教えてください

まず、vacuumdb -f dbname やってﾐｿ。

>>391
DATE型は内部的には4Byteの整数だから、DATE型にしてソートが遅くなると言うわけはないだろう。
一度、VACUUM FULL table;をしてみては如何だろうか。

>>391
型の問題ではないと思うが。そのカラムにインデックスつけてる？

>>392,393,394
す、すいません
みなさんのご指摘の組み合わせでした。
indexつけたあとvacuumしてなかったせいみたい。。。
ご指摘ありがとうございました！

.open()
でエラーがたまに出るけど、みんなどうやって処理してます？
自分は2回リトライさせてるだけなんですけど、
「普通はこうやるんだ」みたいな決まりってあるんでしょうか？

>>396
でたことないので、出たら出ないように調べると思う

ポストグレスキューエルを信じて裏切られることはありまつか？

PostgreSQL ベースの某社の製品にならあるけど。

Cygwinでselect文発行したら
<8B><64><A2>みたいな文字が白くなって大量に表示された
エディタみたいな画面になっちゃったことってあります？
ｑキー押さないと復帰できないし、selectの結果もでないし。

あとこれは別の環境でだけど、コマンドだと文字化けしちゃう。
WEB上ではちゃんと出てるのに、コマンドで飛び出すと文字化け。
ちゃんと↓入れて日本語入力もできるのに。
set kanji-code sjis
set convert-meta off
set meta-flag on
set output-meta on
Win2000のドメインに入った状態でセットアップして
そのままだと使えなかったからローカルで使ってるのが
いけないのかなぁ。

PHP4.39とPGSQL7.46で学習してます
トランザクションについて教えてください

BEGIN

SELECT テーブルAのid(SERIAL)のNEXTVALを取得

INSERT テーブルAに上で得られた値をidにいれて挿入

INSERT テーブルBに上で得られたidの値を挿入

COMMIT

以上のような流れがあったとしてテーブルAのidの唯一性は保証されてますか？
要はテーブルAにINSERTしたときのidを取得としてテーブルBにも入れたいんです。
NEXTVALを取ったあとに他のプロセスが参照してデータの整合性がなくなるのが心配です。

シーケンスの nextval で取得される値は
トランザクションとは関係なく
唯一性が保証されるように思う

(;´Д`)ハァハァ ポストグレスキューエルはマイエスキューエルにまだアドバンテージはあるのかい？

SELECTして出てきた行に1から始まる連番をつけるってことできますか？
データのランキングをだしたいんですが。

PostgreSQL 7.4.6 の環境で質問があります。

データベースtest_db内のテーブルtbl_aのパーミッション
を以下のようになっているのですが、、、

test_db=# \dp tbl_a
Access permissions for database "test_db"
Relation | Access permissions
----------+-------------------------------------
tbl_a | {"=","postgres=arwR","nobody=arwR"}
(1 row)

パーミッション未設定のテーブルを上記の同じ設定にするには、
どのようにすれば、いいでしょうか？

test_db=# \dp tbl_b
Access permissions for database "test_db"
Relation | Access permissions
----------+-------------------------------------
tbl_b |
(1 row)

>> 405 の件は、自己解決しました。

grant select,insert,update,rule on tbl_b to nobody;
以上で実現しました。

板汚し失礼しました。

>>403
Myに比べて多機能だしいろいろあるだろ
>>404
できる

>>407
具体的にどうやるんですか？マニュアル読み漁ってもわかりません。
サブクエリで連番つくる方法は検索して見つかったんですけど
もっと簡単にできないものかと。

カラム内のデータを置き換えすることはできないものでしょうか。

イメージとしましては、

update table_name set f =~ s/abcde/qwert/g;

こんな感じのようなことをやりたいのですが、説明がどうもうまく
なくて申し訳ありません。

ご教授いただければ幸いです。
宜しくお願いします。

お主の望みは正規表現置換かや?
ならば、PL/Perl などを使ってユーザ定義関数を作れ、とのご神託である

>>410
SQL内でなんとかできないものでしょうか。
フィールド値の内容を丸まる変更は当然出来ますが、
likeを使用しつつもどうにかできないものかと模索中で
ございます。

strpos() と substr() をうまく組み合わせればある程度までは行けるかもしれんが、
g オプションは難しそうだなあ。

お告げに従って PL/Perl で書くと、よくわからんが、こんな感じ?

CREATE FUNCTION chikan(text) RETURNS text AS '
my ($mojiretsu) = @_;
$mojiretsu =~ s/abcde/qwert/g;
return $mojiretsu;
' LANGUAGE plperl;

update table_name set f = chikan(f);

>>410、>>412
ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plperl'.
Recognized languages are sql, C, internal, and created procedural languages.

となったので、どうやらPL/Perlが入っていないみたいなので、入れる権限のない
私なりに何か他に方法がないか探してみました。

http://www.nanbu.com/~nanbu/keizi_show1_2.php?category1key=&keizikey=1041937855000000006&PHPSESSID=8087763c1060187e12476afe32e0a60d

これでなんとか凌ぎました。
グローバルオプションこそつけられないものの、置き換えは何とかできるので
助かってます。

ご教授有難うございました。

foo というテーブルで、hoge fuga hige moe
ちう２つのレコードがあります。

で、hoge fuga higeが３つとも同じレコードについては、まとめて１レコードとみなし。
fooテーブルが何レコードあるか調べたいのですが、
どうゆうSQL文を書いたらよいのでしょうか？
select sum(*) from favorite group by hoge, fuga, hige
とやってもだめでした（そりゃそうだよね。。。）

どなたか教えていただければ幸いです。

訂正：
＞foo というテーブルで、hoge fuga hige moe
＞ちう２つのレコードがあります。

foo というテーブルで、hoge fuga hige moe
という２つの「フィールド」があります。

すみません

回答ないようなのでDB板逝ってきます

ゲイツOSに対応した、postgreSQL8
ｷﾀ━━━━(ﾟ∀ﾟ)━━━━ｯ!!

フィールドが一個だけのテーブルが必要なんですが
こういうことって変ですか？

>>418
それだけの情報で何をどう答えろと？
そうですか、では私はｴｽﾊﾟｰですか

>>418
必要なのなら変ではない。
必要ないのにあるのなら変。

vacuum fullを実行すると2〜3時間ぐらいかかり、
しかもその間、httpdに完全に接続できなくなります。
vacuum fullって、そんなもんなのでしょうか？

vacuumしないと、3日もしないうちに重くなってDB接続エラーでまくります。
3日1回httpd止めて作業となると大変なので軽減させたいのですが、
何か良い方法があれば教えてくださいm(_ _)m

構成は、こんな感じです。
Xeon2.8GHzDUAL
メモリ2GB
HDD120GB
Linux-FedoraCore2
　・apache_1.3.33
　・postgresql-7.4.5
　・php-4.3.9