PostgreSQLのエスケープ事情

備忘録的にアップします。

かなり昔に作られたシステムのメンテで学んだこと
pg_queryを使用してSQLを処理していましたが、自前のエスケープが効いてなくてエラーが発生していました。

addslashesで処理をしていましたが・・・

Googleティーチャーに聞いてみると記事を発見

standard_conforming_strings がOnの場合はエスケープが文字として認識される。
\nと改行コードがバックスラッシュとnの2文字と認識されるようです。

PostgreSQL9.1以降はこの値のデフォルトがOnということ
それで自前でエスケープしてもエラーになったと。

standard_conforming_stringsの説明

pg_queryを使っていたのをpg_query_paramsに変更することで関数でエスケープしてくれるので、これで回避!

pg_query_params

エスケープがバックスラッシュではなくなったってことですね。

参考:徳丸浩の日記

EC-CUBE2の規格登録でエラー

EC-CUBEの規格登録をしようとしたら下記のエラーが出た件

*—————————————————————*
SQL: [INSERT INTO dtb_classcategory(name,class_id,creator_id,rank,create_date,update_date,classcategory_id)
SELECT $1,$2,$3,$4,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,$5]
PlaceHolder: [array (
0 => ‘テストデータ’,
1 => ’92’,
2 => ‘2’,
3 => 3,
4 => 96,
)]
MDB2 Error: unknown error
_doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_pgsql_2977d07f3499f15f5f700e51693c3dfbbe8b622f1d (‘テストデータ’, ’92’, ‘2’, 3, 96)]
[Native message: ERROR: 重複キーが一意性制約”dtb_classcategory_pkey”に違反しています
DETAIL: キー (classcategory_id)=(96) はすでに存在します]
*—————————————————————*

EC-CUBE 2.x
PostgreSQL

classcategory_idがかぶっているよというエラーです。

PostgreSQLで状況を確認します。(phpPgAdmin)

まずはシーケンスの状況を確認します。

last_valueの値がclasscategory_idの最後の値です。
これが96になっています。

classcategory_idのMAXの値を調べます。

240と値が返ってきました。

last_valueの値を変える必要があります。

下記のエラーが出ました。

「リードオンリーのトランザクションでは setval() を実行できません」

phpPgAdminでは実行ができないようなので、SSHでサーバーに繋げて直接PostgreSQLから実行します。

最後に規格登録の動作確認をして登録できればOKです。

phpPgAdminでデータのインポート

phpPgAdminでインポートするときに困ったこと。

テーブルのデータ入れ替えでクライアントよりデータを頂きました。
それをCOPYコマンドでデータを作ってインポート

「SQL実行完了」と表示されてもデータがインポートされていない。

色々調べてみたらどうやら\マークがNGだったようです。

「お米\1300」といった感じのデータがあると×
エスケープ文字なので当然といえば当然ですね。

これをエスケープしてしまえばOKです。
「お米\\1300」

これでインポートできた!

PostgreSQLのoid使用時の注意

PostgreSQLのoid使用時の注意

サーバー移転時の話
7.4→9.2にpsqlもバージョンアップ

SELECT時にoidを使っていたものがカラムがないというエラーが出ました。

マニュアルを調べてみるとoidを使う場合はテーブル作成時に指定しないと駄目らしい。
バージョン8.1以降からデフォルトでのoidは無効のようです。

下記のようにテーブル作成します。

今回はoid使わなくてもOKなところだったのでプログラムを修正しました。

PostgreSQLのバックアップを取ってみる

PostgreSQLのバックアップを取ってみる

pg_dumpで実行する場合パスワードなしだと下記のエラーが出る
たぶんpg_hba.confでローカルならパスワードなしOKって設定ができると思いますが…

pg_dump: [archiver(db)] connection to database “hogehoge” failed: fe_sendauth: no password supplied

オプション -w でもスルーできない場合に困る。
cronで実行したいときとかにね。

そのときは「.pgpass」ファイルで回避可能ですよ。

上記ファイル名でファイルを作成し、パーミッションを600にします。
そうしないとエラーで怒られます。

.pgpassの中身は下記

パスワード以外はワイルドカード(*)のしてもありです。

ファイル作成後に下記のように実行するとOK

PostgreSQLのWhrere時の型チェックの厳密化

CakePHPでDBはPostgreSQLのサイトがある。
そちらを移転作業したときに起きた問題

動作チェック中に下記のエラーが発生

HINTから情報を検索するとPostgreSQLのバージョンで検索(Where)のキャスト(型)のチェックが厳密化されたようです。

今回はPostgreSQL7.4.23 → 9.2.10

厳密化は8.3からのようです。
キャストの厳密化とは検索するときにフィールドの型を厳密にチェックするようです。
今までのバージョンは型を自動で変換してくれていたようですが、8.3からはそれを行わないとのこと

それらがNGとのこと

私のプログラムのエラーはdate型にLIKE検索をしているのがNGでした。

対策としてはFunctionとCastを作成してそれで型の変換をさせる方法があるようですが、
次の移転時にまた困るような気がしたので、今回はプログラム側の修正を行いました。
(プログラムの修正箇所が少なかったため)

CakePHPを使っているのですが下記の修正ではエラーでした。

array(“date::text”=>”2017-11%”)

ということで、ちゃんと関数で変換します。

エラーなく検索結果が返ってきたらOK

プロセスの停止方法

PostgreSQLは使い慣れていません。

DB管理ツールからSQLを投げたらずっとウェイト状態になってしまいました・・・

SQLでプロセスの停止ができるようなので試してみました。

プロセスIDの確認

プロセスを停止させる。

上で駄目なら

上で駄目なら直接Kill

この場合はroot権限が必要だったりするので使えないことがあります。