PostgreSQL ＆ pgsql-jp ML 3テーブル目

すいません
立て続けですんません
もう一つ質問です＾＾；

prepareしてSQLを実行するときORDER　BYはプレイスフォルダに出来ないのでしょうか？

SELECT * from test order by ?
でprepareして、カラム名をバインド後実行してもソートされません。
やり方が違うのでしょうか？それとも出来ないのでしょうか？
（PHPのPDOで試してます。psqlでは試してません・・・）

お願い致します

PDOのプレースホルダーは?じゃ無かったような

?も使えるんだな。俺の思い込みか。

>>922
PHPのPDOがどういう実装になっているか分からないけど
SQLのPREPARE文に対応している形に変換されているなら
PREPARE文で出来ないから出来ない。
そんなのPHP側で?の部分に変数埋め込んで展開すれば良いだけじゃんか。
SQL injection が、とか言うなよ。そもそもカラム名だろ。
そこに injection 可能なパラメータが来ることがあるかもしれないなら
それは設計が間違っているんじゃないかと。

>>921
そりゃ違うぞ。間違いではないがちゃんと理解してない。

$sql = "SELECT substring(hoge from '\\([0-9]\\)') FROM Table";
もしくは
$sql = 'SELECT substring(hoge from \'\([0-9]\)\') FROM Table';

これで$sqlの中身は
SELECT substring(hoge from '\([0-9]\)') FROM Table
になってバックエンドに渡される。

>>922
たぶん
SELECT * FROM test ORDER BY 'カラム名'
となってて、'カラム名'という文字列でソートしてる。
この文字列は当然不変だから意味を成さない。
基本的に"データタイプ"を指定するので、カラム名そのものを
渡すのは無理じゃないかな?
試したことないのであれだが、そのあたりは>>925に同意

>>922
列名ではなく列番号なら出来るかも。
やったことないけど。

>>925
>>926
レスありがとう御座います。

>>925
確かに、カラム名をフォームでテキスト入力させることはないだろうし
メソッドもPOSTにするだろうしインジェクションの心配はないですよね。

また値じゃないからできないのかなあ、とも思ったんですけど
出来ないじゃなくて、必要ないって感じですか。

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

>>956
>$sql = "SELECT substring(hoge from '\\([0-9]\\)') FROM Table";
>もしくは
>$sql = 'SELECT substring(hoge from \'\([0-9]\)\') FROM Table';

そうですよね？最初そうだろうと思って、上の例のようにしたんですけど
エラーは出ませんでしたが意図した結果が得られなかったんです。
しかも、デバッグのために発行したSQLを画面に出してるんですけど
'SELECT substring(hoge from '\([0-9]\)') FROM Table'
と表示されました。

SQL上の正規表現の（）のエスケープ自体\\(2コ)にしなきゃならないのかと思って
921の例のようにしたら、意図した結果が得られたんですけど・・・

なんか勘違いしてるかな〜
時間があったらもう一度試して見ますね。

作成(create等)専用ユーザと、閲覧(select)専用のユーザを作成したいんだけど、 テーブルを作成するごとに GRANT SELECT ON createdtable TO viewonlyuser するのが面倒で困ってるんだけど、なんとかならない？

>>928
> メソッドもPOSTにするだろうしインジェクションの心配はないですよね。

POST とインジェクションってなんか関係あるのか？

> 出来ないじゃなくて、必要ないって感じですか。

カラム名を動的に指定することはあるけど、カラム名はシステム側で持って
るんだからから不正なカラム名ははじいておけばいいだけだ。

>>929
そんなことが面倒になるほどテーブルを頻繁に作成すると言う設計を見直す。

インジェクションとCSRFの区別がついてないと思われ。
そしてPOSTにしてもCSRFは防げない。

>>929
何のためにロールがあると思ってる？

少なくとも>>929が望んでるもののためではないな。

GRANT SELECT ON TABLE ALL TO viewonlyuser;
とかできないから無理なんだよね。

>>934
ストアドで動的SQLを使えばいいんじゃね？

7.4を使っとります。
ODBCでAccessから操作してるんですが、PostgresqlにどのようなSQLが送られたか
知るにはどうしたらよいでしょうか。どこかのLogに残ってたりするでしょうか。

具体的には、レコードの1カラムを変更したときにレコードまるごとUpdateしてるかどうかを
知りたいのです。たぶんしてないと思うんですが、CSEってまるごとだった気がしたので。
違ったらすみません。

よろしくお願いします。

>>936
server側でもODBC側でもログは取れるよ。
設定すればだけど。

わぉ、ちょっぱやレスありがとうございます！
ちょっと頭悪い質問だったんで自己フォロー入れようと思ったら遅かった・・

設定を調べてみます

>>938
サーバ側は
http://www.stackasterisk.jp/tech/sonomama/postgresql0b_01.jsp#5
かな。これだけで出来たっけ？　なんか自分はこれだけじゃ駄目だった記憶が…

ODBC側はCOMMLOGをオンにすればC:\直下にログが出来る。

できました！ありがとうございます！
とりあえず、ODBC側でログがはけましたが、サーバ側のほうが便利なので調べてみます。

ちなみにupdateは変更箇所のみでしたが、何故かwhere句で抜き打ち的に
主キー以外のいくつかのカラムをチェックしていました。

制作環境を全面的にUTF-8に移行させようと思っているのですが、psqlってUTF-8に対応してますか？
（今はMac/WinのSJIS＋LinuxのEUC-JP）
今はLinux環境がVine3.2なのでPostgreSQLは7.4なのですが、
7.1対応の改訂第3版のシーラカンス本では、psqlはEUCなら日本語を受け付ける、とあります。
UTF-8については言及がなく、そもそも7.1ではUTF-8ではなくUNICODEという名称ですね。
$ psql hoge_db < fuga.sql
なんてやるときに、fuga.sqlがUTF-8で書かれていてhoge_dbもUTF-8で作成しているとき、
$psql --encoding UTF-8 hoge_db < fuga.sql
という感じにできればと思うんですが。（7.4ではできないみたい）

>>941
こんな所にだらだらと書く時間があるならググればいいやん。
http://www.google.co.jp/search?q=PostgreSQL+UTF-8

テーブルを数十万行ほど定期的に消しては入れてを繰り返したら、
だんだんクエリが遅くなっていくんだけども、テーブル削除以外で直す方法ないの？
VACUUMしても全然元に戻らない。。

>>943
ﾜﾛｽ

windows　のポストグレス(現在の最新版（8.21?）)へLinux上(バージョンは7.43)のデータを復元したいいのですがうまくコマンドが使えません。
Linuxでpg_dumpを使ってバックアップしたファイル"a.out"を
Windows上のc:\に配置した場合
psql コマンドで
psql -e testdb > c:\a.out
としてもバックアップが復元されません・・・
ググってもコレでよさそうだし・・・・

上記のコマンド実行結果は
なぜかテーブル空間の一覧が表示されてしまいます。
もう訳ワカメラーメン

>>943
つ「VACUUM FULL」


>>945
つ「不等号の向き」

>>946
ﾄﾝｸｽ

ごめ、こっちに書き込んだ奴の不等号間違えただけだた

psql -e testdb < c:\a.out
これは
psql -e testdb < "c:\a.out"
こうするべきなんだろうか?
むしろ""でパスくくらないと　\aがコマンドに間違えられてしまって怒られるんだが・・・
画面では
postgres=# ｷｬﾚｯﾄ
てなってて
ｷｬﾚｯﾄの位置から上にあるコマンドを打ち込んだ結果（psql -e testdb < "c:\a.out"）
結果も何も出ずにまた
postgres=# ｷｬﾚｯﾄ
という状態です。

なにか俺カンペキに勘違いしている？？？？

>>947
＞postgres=# ｷｬﾚｯﾄ

これはすでにpsql実行中だろ
コマンドプロンプトから実行しなさい

>>947
つか>>948の指摘を解消した上で、
まだパスの問題が出るなら、

カレントドライブ、カレントディレクトリをC:\にしてから、
パラメタにドライブレターや\マークを使わないようにすれば良さ気だが？

C:\>C:
C:\>CD \
C:\>psql -e testdb < a.out

>>948->>949

ｻﾝｸｽ!でけた〜！！！
コマンドライン引数に入れてやったらでけたよ
パスは"D:\a.out"
としました。

しっかしこうやってコマンドライン引数に入れるのであれば・・・・Postgresインスコした時に一緒に入ってくる
'オーナー名'へのpsql
のショートカットの使い道がわからんっす(；´∀｀)
ほとんどはPgAdminでやっちゃってたから・・・・・

ともかく勘違いしてたんでスッゴイ助かりました！

>>950
> 'オーナー名'へのpsql 

DB管理には便利だと思うがなｗｗｗ

ん〜
やっぱりGUIツール使うよりコマンド覚えたほうが色々早いし融通もきくの・・・かな？

>>952
とりあえず、このスレは知能の足りないお前のボヤキを書くスレではない

前はコマンドだったけど今はアド民３どっぷり

コマンドラインを使うのなら、psql だけは cygwin で make したのを使った方が良い。
mingw の psql はタブ補間も効かないし、使い物にならん。

>>955
俺もWindows版のpsqlは補完が効かなくて、一時期cygwinを入れてたが、
結局pgAdmin3になっちゃった。
でも、テスト的にSQLを書くなら補完の効くpsqlは便利なんだよなぁ。
まぁ、LAN内に常時稼動しているLinux機があるから、そこにあるpsqlから
Windows機のpgsqlに接続することも可能なんだけどね。

CURRENT_TIMESTAMPの時間が20分程度ずれるのですが、これはDBが稼動しているサーバの問題なのでしょうか。

>>957
わかってるならサーバを時刻校正しろよｗｗｗ

>>958
や、サーバそのものは触れないのです。
確認のお願いをする前に確認しておこうかな..と（・ω・｀）

外部参照について質問です。
例えば、

テーブルA
id　|　name
--+------
1　|　ほげお
2　|　ほげこ

テーブルB
id　|　name_id　|　item
--+---------+-----
1　|　1　　　　　|　えんぴつ
2　|　1　　　　　|　ノート
3　|　2　　　　　|　消しゴム

の二つのテーブルから、
id　|　name　　|　item
--+--------+-----
1　|　ほげお　|　えんぴつ
2　|　ほげお　|　ノート
3　|　ほげこ　|　消しゴム

のような結果を取得したい場合に、テーブルBの name_id には外部参照の制約を
つけるとパフォーマンスが上がるものなのでしょうか？
それとも単にデータ整合性が保たれるというだけのものなのでしょうか？

現在、上記の例のようなテーブルで、テーブルAのレコードのみ非同期で削除
したいという案件がありまして、外部参照の有り無しがパフォーマンスに影響
するのかしないのかちょっと不安になったので質問させてもらいました。

>>957
\! でサーバ側の日時を確認すれば分かるでしょ。

アデレードにサーバがあるとか

>>960
上がらない。
外部参照制約をつけた状態でAのレコードを削除すると、
つけない場合より遅くなる。
B.name_idにindexをつけてやれば解決。

>>960
>>963
えっ？FKにしないとJOIN出来ないんじゃないの？
勘違いしてたっぽい？

ということはFKは何のためにあるの？
登録しようとしたときエラーを出すためだけ？

>>964
制約とJOINは関係ねーべ。

>>963
レスありがとうございます。
なんとなくそうじゃないかなぁと思ってたんですがこれですっきりしました。

>>964
登録のときと削除のときに、整合性がないデータはエラー判定するための
ものですかね。
そもそも数年前のバージョンでは外部参照制約使えませんでしたし。

MLネタですが、色々なところで有名なhasiru.netの人はなんと言うか…
ODBCドライバのメインメンテナを相手になんてことを書いているのか。

正直、あのドライバの出来なら言いたくもなる

>>966
> 登録のときと削除のときに、整合性がないデータはエラー判定するための
> ものですかね。

削除の時に関連するデータを削除することもできるよ。

ようは、整合性をデータベース側で維持するための仕組みだよ。

夜分失礼します。

vacuum analyzeをかけようかと思っているのですが、このオプションは
「vacuum＋analyze」という認識でかけてよろしいのでしょうか。

それともvacuumはvacuumで別にかけなければならないものなのでしょ
うか。

初心者的な質問で恐れ入りますが、ご教示いただけますと幸いです。

>>970
http://www.postgresql.jp/document/current/html/sql-vacuum.html