S-JIS[2003-07-19/2003-09-07]

PostgreSQLのSQL

とりあえずリファレンスを見れば何でも載っているけど、特殊なテーブルや知ってると便利かもしれない事柄についてもちょっと書いてみました。


SELECT

SELECT文で テーブルの項目を使わない場合は、FROM句(テーブル名)を省略できる。ORACLEならDUALテーブルを使うようなケース。

select 'abc';

UPDATE

UPDATEでも、「Cannot insert a duplicate key into unique index tbl_xxxx_pkey」というエラーが出る可能性がある。

キーにaaa,bbbという値が入っている2つのレコードに対し、

UPDATE tbl_xxxx SET key='aaa' WHERE key = 'bbb';

という更新を行うと、aaaというキーのデータが既に在るのでこのエラーになる。ただし、

UPDATE tbl_xxxx SET key='bbb' WHERE key = 'bbb';	←キーの更新だが、結果的に変わらないので問題無い

は大丈夫。


権限の付与・削除

権限の付与はGRANT・削除はREVOKEで行う。
権限の照会はpsqlの\zで行う。「ユーザー名=arwdRxt」といった形で表示される。

意味
a append(INSERT) INSERTの実行権限
r read(SELECT) SELECTの実行権限
w write(UPDATE) UPDATEの実行権限
d DELETE DELETEの実行権限
R RULE ルールの作成権限
x REFERENCES 外部キー制約
t TRIGGER トリガーの作成権限

ユーザー情報

CREATE USERcreateuser)で作成する。変更はALTER USER。削除はDROP USERdropuser)。
照会は以下の様なSQLで行う。

select * from pg_shadow;		←権限がある人のみ実行可能。パスワードも見られる。

pg_shadowで表示されるパスワードは、MD5形式の時は暗号化されている。文字列の先頭がmd5になっていたような…。

select * from pg_user;		←権限が無くても実行可能。パスワードは見られない。
select current_user, session_user;	←現在のユーザーIDを表示する関数を使用

パスワードの設定は、以下の様にして行う。

ALTER USER ユーザー名 PASSWORD 'パスワード';

グループ情報

CREATE GROUPで作成する。変更はALTER GROUP。削除はDROP GROUP
照会は以下の様なSQLで行う。

select * from pg_group;
   groname    | grosysid |    grolist
--------------+----------+---------------
 group_abcdef |      100 | {107,108,109}

grolistの数値は、pg_userビューのusesysid項目の値。


トリガー

トリガーはINSERT・UPDATE・DELETEの実行前後に呼ばれるもの。呼ばれた際にどういう動きをするかは関数で実装する。
例えば、外部キー制約はトリガーで実装されている。

CREATE TRIGGERで作成する。削除はDROP TRIGGER。変更は 削除+作成。
テーブルに付いているトリガーはpsqlの\dで確認できる。
トリガーの一覧は以下の様なSQLで照会できる。

select * from pg_trigger;

トリガーに割り当てられている関数の名称は、以下の様なSQLで知ることができる。

select tgname,proname from pg_trigger t, pg_proc f
where t.tgfoid = f.oid
and tgname = 'トリガー名';

関数

CREATE FUNCTIONで作成する。変更はCREATE OR REPLACE FUNCTION。削除はDROP FUNCTION
関数にpgsqlを使いたい場合は、createlangでpgsqlを使えるように指定しなければならない。
作成した関数の中身は以下のSQLで照会できる。

SELECT prosrc FROM pg_proc WHERE proname = '関数名';

組込関数

PostgreSQLには、文字列操作型変換日付時刻関連(現在時刻の取得や変換・演算)等の様々な関数が用意されている。

豆知識 備考
timestamp型に 単純に数値を足すと、時刻部分が消える。
select current_timestamp + 3;

3日後の日付が取得される

 
psqlの\encodingに該当する関数
# select pg_client_encoding();
 pg_client_encoding
--------------------
 UNICODE
(1 row)
# \encoding
UNICODE

関数には、末尾に括弧を付ける必要が有るものと無いものがあるらしい。


トランザクション

BEGINでトランザクションを開始し、COMMIT又はROLLBACKで終了。

FOR UPDATE」を指定してロックをかけたSELECTの場合も、COMMIT又はROLLBACKするまでロックしたままになる。

トランザクションの中で1回でもSQLエラーが発生すると、COMMITは出来なくなる。

トランザクション内の時刻は全て同じになる(COMMIT時の時刻?)。

↓2つのSQLでは連続して実行しても異なる時刻になる
update A set DATE1 = current_timestamp;
update A set DATE2 = current_timestamp;
↓同じ時刻になる
BEGIN;
update A set DATE1 = current_timestamp;
update A set DATE2 = current_timestamp;
COMMIT;

EXPLAIN

SQLの実行にどれくらいのコスト(時間)がかかるかをEXPLAINで表示できる。

EXPLAIN SQL文 ;
EXPLAIN ANALYZE SQL文 ;

ANALYZEを付けない場合、表示される時間は参考程度。costが小さいほど実行が早い。
ANALYZEを付けると、実際にSQLを実行して実時間を表示する。UPDATE文等だと本当に更新してしまうので注意。

SQLの実行計画は、同じDB構成であっても中に入っているデータによって異なるし、VACUUM ANALYZE(統計情報の更新)をかけているかどうかでも変わってくる。SQLの実行が遅いと思ったら、まずANALYZEをかけよう。するとしないじゃ大違いだから!

SQL文の最適化についてはこちらを参照。


VACUUM

UPDATEやDELETEによって更新を行うと、見た目はデータが無くなったように見えてもPostgreSQL内部では削除フラグを立てているだけで、データは消えていない。(単なる)VACUUMvacuumdb)を行うことによって、そのデータの領域が再利用されるようになる。
ただし、INDEX領域はVACUUMだけでは再利用されないらしい。

また、データの更新によって拡張したデータのディスク領域はVACUUMで減ることは無い。VACUUM FULLを行うと減らせる。


PostgreSQLへ戻る / 技術メモへ戻る
メールの送信先:ひしだま