PostgreSQL には 行を 配列に変える ARRAY_AGG という関数があります。 これと配列を文字列に変える ARRAY_TO_STRING を組み合わせることで 行をカンマ区切りなどの文字列にすることができます。
例えば こんなテーブルに対して．．．
db=# SELECT id, name FROM fruit;
id | name
----+--------
1 | りんご
2 | みかん
3 | ぶどう
次のような結果を得ることができます。
db=# SELECT ARRAY_TO_STRING(ARRAY_AGG(name order by id), ',') AS names FROM fruit;
names
----------------------
りんご,みかん,ぶどう
これを Oracle でなんとかできなかと思っていたんですが Oracleでも11g R2 から LISTAGG という関数が追加されていました。
SQL> SELECT LISTAGG(name, ',') WITHIN GROUP (order by id) AS names FROM fruit;
names
----------------------
りんご,みかん,ぶどう
LISTAGG 関数は ARRAY_AGG + ARRAY_TO_STRING のような動きをします。
メモです。
（使用している PostgreSQL は PostgreSQL 9.3.12 です）
PostgreSQL で 配列をテーブルのように扱う方法です。 データの一括登録や、関数のテストなどに便利です。
まず、配列の定義は次のようになります。
'{value1,value2,value3}'
ARRAY 演算構文を使うと次のようになります。
ARRAY['value1','value2','value3']
今回はまず ARRAY 演算構文の方を SQL で使用します。
SELECT field1::VARCHAR
FROM UNNEST(ARRAY['value1','value2','value3']) AS field1;
使いまわしが良いように FROM 句で使用しています。
ポイントは UNNEST 関数です。 UNNEST 関数を使用すると 配列を行集合に展開することができます。
[参考]
9.18. 配列関数と演算子 - PostgreSQL 9.3.2文書
SQL の実行結果は次のようになります。
db=# SELECT field1::VARCHAR db-# FROM UNNEST(ARRAY['value1','value2','value3']) AS field1; field1 -------- value1 value2 value3 (3 rows)
これを使うと 次ように SQL で使用する関数のテスト用 SQL が簡単に作れます。
SELECT field1::VARCHAR, test_function(field1::VARCHAR) AS result
FROM UNNEST(ARRAY['value1','value2','value3']) AS field1;
ここまでは一次元配列でした。
配列をテーブルのように扱うのに 列が 1 つでは困りますね。
ただ、2 次元配列にすると扱いが面倒になるため 配列の文字列定義と ARRAY 演算構文を組み合わせて 次のように定義します。
ARRAY['{100,value1,2018/10/01}','{200,value2,2018/10/02}']
これを SQL で使います。
SELECT (fields::VARCHAR[])[1]::NUMERIC AS field1 , (fields::VARCHAR[])[2]::VARCHAR AS field2 , (fields::VARCHAR[])[3]::DATE AS field3 FROM UNNEST(ARRAY['{100,value1,2018/10/01}' ,'{200,value2,2018/10/02}']) AS fields;
ポイントは "fields" に配列の文字列定義が入るので それを "VARCHAR[]" でキャストします。
SQL の実行結果は次のようになります。
db=# SELECT (fields::VARCHAR[])[1]::NUMERIC AS field1 db-# , (fields::VARCHAR[])[2]::VARCHAR AS field2 db-# , (fields::VARCHAR[])[3]::DATE AS field3 db-# FROM UNNEST(ARRAY['{100,value1,2018/10/01}' db-# ,'{200,value2,2018/10/02}']) AS fields; field1 | field2 | field3 --------+--------+------------ 100 | value1 | 2018-10-01 200 | value2 | 2018-10-02 (2 rows)
値にカンマを使用する場合は 次のようにエスケープします。
ARRAY['{100,val\,ue1,2018/10/01}','{200,value2,2018/10/02}']
メモです。
（使用している PostgreSQL は PostgreSQL 9.3.12 です）
列の型が "NUMERIC(10,4)" のような場合 そのまま SQL でデータを取得すと "123456" が "123456.0000" のように取得されます。
今回は、小数点以下の 0 を削る方法です。
以下の SQL で テーブルとデータを作成します。
CREATE TABLE table1 (
field1 NUMERIC(10,4)
);
INSERT INTO table1 (field1)
VALUES (123456.0000)
, (123456.5000)
, (123456.0500)
, (123456.0050)
, (123456.0005);
SQL を実行。
db=# CREATE TABLE table1 ( db(# field1 NUMERIC(10,4) db(# ); CREATE TABLE
db=# INSERT INTO table1 (field1) db-# VALUES (123456.0000) db-# , (123456.5000) db-# , (123456.0500) db-# , (123456.0050) db-# , (123456.0005); INSERT 0 5
まずは 普通にデータを取得してみます。
db=# SELECT field1 db-# FROM table1; field1 ------------- 123456.0000 123456.5000 123456.0500 123456.0050 123456.0005 (5 rows)
小数点以下に 0 が 続いていますね。
可変精度の "DOUBLE PRECISION" 型に キャストします。
PostgreSQL のキャストは セミコロン 2 つです。
xxxx::DOUBLE PRECISION
次の SQL で そのままの取得（field1）と キャストして取得（field1+）を比較してみます。
db=# SELECT field1 db-# , field1::DOUBLE PRECISION AS "field1+" db-# FROM table1; field1 | field1+ -------------+------------- 123456.0000 | 123456 123456.5000 | 123456.5 123456.0500 | 123456.05 123456.0050 | 123456.005 123456.0005 | 123456.0005
小数点以下の 0 が 削れていますね。
これは プログラム言語でも よくある方法です。
ただし、"DOUBLE PRECISION" は 不正確な可変精度の数値データ型ですので 精度の問題には注意する必要があります。
メモです。
（使用している PostgreSQL は PostgreSQL 9.3.12 です）
PostgreSQL で できるだけ他に影響を与えないように 一時的に全てのトリガーを無効にしたいと思っていたら 次のような方法がありました。
[参考]
PostgreSQLで一時的にtriggerを全て無効する方法 - Qiita
SET session_replication_role = replica;
クライアント接続の変数「session_replication_role」を変更する方法で この変数は、トリガおよびルールの発行を制御するそうです。
[参考]
18.11. クライアント接続デフォルト - PostgreSQL 9.3.2文書
実際に試してみます。
まず、table1 と table2 を作成。
CREATE TABLE table1 (
field1 INTEGER
, field2 INTEGER
);
CREATE TABLE table2 (
field1 INTEGER
);
table1 の INSERT 時に field1 の値を field2 にセットする BEFORE トリガーを作成。
CREATE FUNCTION before_func1()
RETURNS TRIGGER AS $$
BEGIN
NEW.field2 := NEW.field1;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER before_trigger1
BEFORE INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE before_func1();
table1 の INSERT 時に table2 にレコードを INSERT する AFTER トリガーを作成。
CREATE FUNCTION after_function1()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO table2 (field1) VALUES (NEW.field1);
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER after_trigger1
AFTER INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE after_function1();
テーブルとトリガーを作成したら、次の SQL を実行します。
INSERT INTO table1 (field1) VALUES (1);
table1 に INSERT 。
db=# INSERT INTO table1 (field1) VALUES (1);
INSERT 0 1
table1 を確認。
db=# SELECT * FROM table1;
field1 | field2
--------+--------
1 | 1
(1 row)
BEFORE トリガーで field2 に値がセットされています。
table2 を確認。
db=# SELECT * FROM table2;
field1
--------
1
(1 row)
AFTER トリガーで レコードが INSERT されています。
次にクライアント接続の変数「session_replication_role」を試しますが まず初期値を確認。
db=# SHOW session_replication_role;
session_replication_role
--------------------------
origin
(1 row)
初期値は「origin」でした。
クライアント接続の変数「session_replication_role」に 「replica」を設定します。
db=# SET session_replication_role = replica;
SET
db=# SHOW session_replication_role;
session_replication_role
--------------------------
replica
(1 row)
ちゃんと変更されてます。
さっきと同じ INSERT 文を実行。
（テーブルのレコードはクリア済です）
db=# INSERT INTO table1 (field1) VALUES (1);
INSERT 0 1
table1 を確認。
db=# SELECT * FROM table1;
field1 | field2
--------+--------
1 |
(1 row)
BEFORE トリガーが実行されなかったため field2 に値がセットされていません。
table2 を確認。
db=# SELECT * FROM table2;
field1
--------
(0 rows)
AFTER トリガーが実行されなかったため レコードが INSERT されていません。
トリガーは実行されませんでした。
これは便利ですね。
もちろん他のセッションでは通常通りの動作をします。
ただし、整合性が保たれなくなる可能性があるので 当然ながら慎重に実行する必要があります。
前回、PostgreSQL のパスワードを環境変数で設定するのは PostgreSQL 的には非推奨と書きましたが 推奨されている方法について書いておきます。
[参考]
環境変数 - PostgreSQL 9.3.2文書
以下のような内容を「~/.pgpass」に記載します。
hostname:port:database:username:password
ファイル名は、環境変数 PGPASSFILE で変えることができます。
複数記載することができ、 上から順にマッチしたものを使用してくれます。 環境変数と違って複数設定しておけるのは便利ですね。
localhost:*:testdb1:testuser1:testpassword1 localhost:*:testdb1:testuser2:testpassword2 localhost:*:testdb2:testuser3:testpassword3
"*" も使用できます。 ホスト名は、Unixドメインソケットの場合 でも "localhost" で良いそうです。
[参考]
パスワードファイル - PostgreSQL 9.3.2文書
$ chmod 600 ~/.pgpass
ファイルの権限を 600 にしていないとエラーになります。
以前、PostgreSQL のパスワードを
環境変数で設定する記事を書きましたが
PostgreSQL 的には非推奨のようです。
（一部のオペレーティングシステムではroot以外のユーザで環境変数が見える場合がるためとのこと）
[参考]
環境変数 - PostgreSQL 9.3.2文書
一部のオペレーティングシステムがどれを指すのかわかりませんが、 プロセスが使ってる環境変数の見方が気になったので ちょっと確認してみました。
まず環境変数を設定します。
$ export PGPASSWORD=testpassword
PostgreSQL のデータベースに接続するプロセスを作成します。
$ psql -U testuser -d testdb
psql (9.3.10, server 9.3.12)
Type "help" for help.
testdb=#
[Ctrl]+[z] プロセスを眠らせます。
[1]+ Stopped psql -U testuser -d testdb
起動中のプロセスを確認します。
$ ps PID TTY TIME CMD 28796 pts/0 00:00:00 bash 29754 pts/0 00:00:00 psql 29834 pts/0 00:00:00 ps
参考サイトによると、環境変数は「/proc/%{PID}/environ」に 格納されているそうです。
[参考]
Linux/Unixプロセス起動時の環境変数をダンプする | ギークを目指して
確認してみます。
$ cat /proc/29754/environ | sed -e 's/\x0/\n/g' | grep PGPASSWORD PGPASSWORD=testpassword
たしかに確認できました。
使用している環境では root ユーザでなければ 自分以外の環境変数は見れませんでしたが、 自分の環境変数を（rootの権限が使えれば、他のプロセスの環境変数も）確認したいこともあると思うので 覚えておくと良いのかもしれません。
PostgreSQL を使っていて ロケールの設定によっては ソート順が想定とは違う結果になることがあります。
ソート順に関するロケールの LC_COLLATE は データベース作成時にしか変更できないと思っていました。
[参考]
PostgreSQLのロケール(lc collate,lc ctype)の確認と変更
次のように SELECT 時に設定したり。
db=# SELECT * FROM tab1 ORDER BY col1 COLLATE "C" DESC;
後から列単位で変更もできるようです。
db=# ALTER TABLE tab1 ALTER COLUMN col1 TYPE VARCHAR COLLATE "C";
マニュアルに登場するのが 9.1 からなので おそらくその辺りで対応されたのではないかと。
[参考]
SELECT - PostgreSQL 9.1.5文書
ALTER TABLE - PostgreSQL 9.1.5文書
以前、Linux の watch コマンドについて書きましたが PostgreSQL にも 9.3 から \watch メタコマンドが追加されました。
\watch [SEC] execute query every SEC seconds
psql=# SELECT CURRENT_TIME; timetz -------------------- 23:02:53.492804+09 (1 row) psql=# \watch 1 Watch every 1s Mon Oct 17 23:02:57 2016 timetz ------------------- 23:02:57.03674+09 (1 row) Watch every 1s Mon Oct 17 23:02:58 2016 timetz -------------------- 23:02:58.038192+09 (1 row)
このように 前に実行した SQL を一定間隔で実行してくれます。
終了は [Ctrl]+[c]です。
デフォルトは watch コマンドと同じように 2秒間隔のようです。
psql=# SELECT CURRENT_TIME; timetz -------------------- 23:01:20.790009+09 (1 row) psql=# \watch Watch every 2s Mon Oct 17 23:01:25 2016 timetz -------------------- 23:01:25.005323+09 (1 row) Watch every 2s Mon Oct 17 23:01:27 2016 timetz -------------------- 23:01:27.007919+09 (1 row)
INSERT や UPDATE も繰り返し実行できます。
psql=# INSERT INTO test_a VALUES ('a', 'b'); INSERT 0 1 psql=# \watch 1 Watch every 1s Mon Oct 17 23:09:25 2016 INSERT 0 1 Watch every 1s Mon Oct 17 23:09:26 2016 INSERT 0 1
psql=# UPDATE test_a SET field2 = 'c' WHERE field1 = 'a'; UPDATE 10 psql=# \watch 1 Watch every 1s Mon Oct 17 23:10:05 2016 UPDATE 10 Watch every 1s Mon Oct 17 23:10:06 2016 UPDATE 10
データの監視や 処理の繰り返しなどに 利用できそうですね。
PostgreSQL 9.3 から SQL に LATERAL というキーワードが記述できるようになっています。 また独自の拡張かと思いきや、この LATERAL は SQL標準 で規定されているそうです。 Oracle でも 12c からサポートされているようです。
[参考]
LATERALを使ってみよう ? Let's Postgres
使い方としては 遅延評価？ みたいな感じでしょうか。 PostgreSQL では、LATERAL を副問い合わせや関数に対してして指定できるのですが、 FROM に指定した問い合せの結果を、副問い合わせの中で使用することができます。
たとえば、次のような SQL はエラーになります。
psql=# SELECT table1.field1, subquery1.field2 psql-# FROM table1 psql-# LEFT JOIN ( psql-# SELECT field2 FROM table2 psql-# WHERE table2.field1 = table1.field1) AS subquery1 psql-# ON TRUE;
これは 赤字の部分を副問い合わせで使用しているためです。
本来は、次のように副問い合わせから外に出す必要があります。
psql=# SELECT table1.field1, subquery1.field2 psql-# FROM table1 psql-# LEFT JOIN ( psql-# SELECT field2 FROM table2) AS subquery1 psql-# ON (subquery1.field1 = table1.field1);
ところが LATERAL を使うと 次のような書き方ができます。
psql=# SELECT table1.field1, subquery1.field2 psql-# FROM table1 psql-# LEFT JOIN LATERAL ( psql-# SELECT field2 FROM table2 psql-# WHERE table2.field1 = table1.field1) AS subquery1 psql-# ON TRUE;
LATERAL を指定した副問い合わせは 後から評価されるので 副問い合わせの中で FROM 句の問い合わせの列を使用することができます。
これだけだと 単に変な書き方ができる だけなのですが 後で評価されることが チューニングに役立つ場合があります。
例えば 部署（100件）と経費明細（500万件）から 部署ごとの経費を取得します。
psql=# SELECT ALL psql-# 部署.部署ID psql-# , 部署経費.合計金額 psql-# , 部署経費.明細件数 psql-# FROM psql-# 部署 psql-# LEFT JOIN psql-# ( psql-# SELECT ALL psql-# 部署ID psql-# , SUM(金額) AS 合計金額 psql-# , COUNT(*) AS 明細件数 psql-# FROM psql-# 経費明細 psql-# GROUP BY psql-# 部署ID psql-# ) AS 部署経費 psql-# ON psql-# ( psql-# 部署経費.部署ID = 部署.部署ID psql-# );
おそらくこんな SQL になると思います。
試した環境では 5,391ms でした。
LATERAL を使うと次のように書けます。
psql=# SELECT ALL psql-# 部署.部署ID psql-# , 部署.部署名 psql-# , 部署経費.合計金額 psql-# , 部署経費.明細件数 psql-# FROM psql-# 部署 psql-# LEFT JOIN psql-# LATERAL psql-# ( psql-# SELECT ALL psql-# SUM(金額) AS 合計金額 psql-# , COUNT(*) AS 明細件数 psql-# FROM psql-# 経費明細 psql-# WHERE psql-# 経費明細.部署ID = 部署.部署ID psql-# ) AS 部署経費 psql-# ON psql-# TRUE;
実はこの場合は逆に遅くなって 21,614 ms でした。 1行ずつ評価する方が時間がかかるようです。
キー項目ではない部署名を条件に追加します。
まず LATERAL を使わない SQL から。
psql=# SELECT ALL psql-# 部署.部署ID psql-# , 部署経費.合計金額 psql-# , 部署経費.明細件数 psql-# FROM psql-# 部署 psql-# LEFT JOIN psql-# ( psql-# SELECT ALL psql-# 部署ID psql-# , SUM(金額) AS 合計金額 psql-# , COUNT(*) AS 明細件数 psql-# FROM psql-# 経費明細 psql-# GROUP BY psql-# 部署ID psql-# ) AS 部署経費 psql-# ON psql-# ( psql-# 部署経費.部署ID = 部署.部署ID psql-# ) psql-# WHERE psql-# 部署.部署名 = 'システム１部';
Hash Right Join (actual time=3314.571..3314.574 rows=1 loops=1)
Hash Cond: ((t_kj_keihi_meisai.部署ID)::text = (部署.部署ID)::text)
-> HashAggregate (actual time=3314.411..3314.441 rows=141 loops=1)
-> Seq Scan on t_kj_keihi_meisai (actual time=0.121..716.138 rows=5021645 loops=1)
-> Hash (actual time=0.089..0.089 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on r_kj_jigyousho 部署 (actual time=0.022..0.085 rows=1 loops=1)
Filter: (部署名 = 'システム１部'::text)
Rows Removed by Filter: 99
これは 3,314ms でした。
次に LATERAL バージョン。
psql=# SELECT ALL psql-# 部署.部署ID psql-# , 部署.部署名 psql-# , 部署経費.合計金額 psql-# , 部署経費.明細件数 psql-# FROM psql-# 部署 psql-# LEFT JOIN psql-# LATERAL psql-# ( psql-# SELECT ALL psql-# SUM(金額) AS 合計金額 psql-# , COUNT(*) AS 明細件数 psql-# FROM psql-# 経費明細 psql-# WHERE psql-# 経費明細.部署ID = 部署.部署ID psql-# ) AS 部署経費 psql-# ON psql-# TRUE psql-# WHERE psql-# 部署.部署名 = 'システム１部';
Nested Loop (actual time=221.481..221.523 rows=1 loops=1)
-> Seq Scan on r_kj_jigyousho 部署 (actual time=0.029..0.070 rows=1 loops=1)
Filter: (部署名 = 'システム１部'::text)
Rows Removed by Filter: 99
-> Aggregate (actual time=221.448..221.449 rows=1 loops=1)
-> Bitmap Heap Scan on t_kj_keihi_meisai 経費明細 (actual time=217.928..219.075 rows=4633 loops=1)
Recheck Cond: ((部署ID)::text = (部署.部署ID)::text)
-> Bitmap Index Scan on t_yj_keihi_meisai_idx1 (actual time=217.882..217.882 rows=4633 loops=1)
Index Cond: ((部署ID)::text = (部署.部署ID)::text)
こちらは 221ms でした。 10倍以上速くなっています。
このように場合によっては かなり速くなることがあります。
また LATERAL を指定した場合 FROM の結果の行に対して 1行ずつ評価されるため INNER JOIN を指定した場合でも外部結合のように 元の結果の行を全て返すので注意が必要です。
PostgreSQL でも 9.2 から インデックス・オンリー・スキャンが使えるようになっています。
この機能は データを取得する際に、インデックスだけで事足りる場合は インデックスのみを使用するというものです。 Oracle では昔からありましたが PostgreSQL では 9.2 から実装されました。
通常は、検索にインデックスを使用しても、テーブルからデータを取得するために 別の領域（ヒープ）を読みに行くわけですが、これが不要になるためパフォーマンスの向上に かなり期待できる部分があります。
ただ、PostgreSQLは追記型のデータベースなので インデックスだけでは 古いデータかどうか判断できないため、インデックス・オンリー・スキャンの実装は 難しいとされていました。 これを 8.4 でバキュームのため？に追加された ビジビリティマップ というデータを使って「インデックスだけで大丈夫か」を判断しているそうです。
なので、インデックス・オンリー・スキャンといっても、場合によってはテーブルのデータを読みに行く必要があります。 更新が多いテーブルでは難しいかもしれません。
三焦経だけでもありがたいですし、ビジビリティマップが綺麗に？なるようにバキュームも重要です。
実行計画を見ると次のようになります。
（ field1 がテーブルの主キー）
psql=# EXPLAIN psql-# SELECT field1 FROM table1 psql-# WHERE field1 > 300000 Index Only Scan using table1_pkey on table1 (cost=0.43.. Index Cond: (field1 > 300000)
Index Only Scan と出力されます。
PostgreSQL の 現在実行中の psql で、パラメータを表示するには "SHOW" というコマンドを使用します。
psql=# SHOW checkpoint_segments;
checkpoint_segments
---------------------
32
(1 row)
"SHOW ALL" だと一覧を表示することができます。
psql=# \x psql=# SHOW ALL; -[ RECORD 1 ]------------------------------------------------------------------- name | allow_system_table_mods setting | off description | Allows modifications of the structure of system tables. -[ RECORD 2 ]------------------------------------------------------------------- name | application_name setting | psql description | Sets the application name to be reported in statistics and logs. -[ RECORD 3 ]------------------------------------------------------------------- name | archive_command setting | (disabled) description | Sets the shell command that will be called to archive a WAL file.
メモです。
SELECT table_schema
, table_name
, constraint_name
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY';
さらに次のようにすると 解除するための SQLが生成できます。
SELECT 'ALTER TABLE ' || table_schema || '.' || table_name
|| ' DROP CONSTRAINT ' || constraint_name || ';'
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY';
今回はディレクトリ編の最後、ログなどのデータが格納されるディレクトリについて。
とはいえパッケージでインストールしているので
Ubuntu ベースの話になります。
Ubuntu では基本的にログファイルは /var/log の直下か 配下にパッケージ名のディレクトリで格納されます。 PostgreSQL の場合 /var/lib/postgresql になります。
/var/log/postgresql
設定ファイルやデータのディレクトリのように データベースクラスタのサブディレクトリはありません。 /var/log/postgresql の直下にログファイルがあります。
$ ls -F /var/log/postgresql
/var/log/postgresql/postgresql-9.1-main.log
代わりにログファイルのファイル名に "9.1-main" のようにデータベースクラスタと同じキーワードが入っています。 （ログファイル名は設定で変更できます）
今回はテーブルなどのデータが格納されるディレクトリについて。
とはいえパッケージでインストールしているので
Ubuntu ベースの話になります。
Ubuntu では、/var/lib/postgresql 以下に格納されます。
/var/lib/postgresql
Ubuntu の場合、このディレクトリの直下には 前々回の設定ファイルと同様に データベースクラスタごとのサブディレクトリがあります。
/var/lib/postgresql/9.1/main
$ ls -F /var/lib/postgresql 9.1/ $ ls -F /var/lib/postgresql/9.1 main/ $ ls -F /var/lib/postgresql/9.1/main base/ pg_stat_tmp/ postmaster.opts global/ pg_subtrans/ postmaster.pid pg_clog/ pg_tblspc/ server.crt@ pg_multixact/ pg_twophase/ server.key@ pg_notify/ PG_VERSION pg_serial/ pg_xlog/
今回はコマンド（実行ファイル）が格納されるディレクトリについて。
とはいえパッケージでインストールしているので
Ubuntu ベースの話になります。
initdb や vacuumdb、psql など PostgreSQL の主なコマンドは 以下のディレクトリに格納されます。
/usr/lib/postgresql/9.1/bin
$ ls -F /usr/lib/postgresql/9.1/bin
clusterdb* pg_basebackup* pg_upgrade*
createdb* pg_controldata* postgres*
createlang* pg_ctl* postmaster@
createuser* pg_dump* psql*
dropdb* pg_dumpall* reindexdb*
droplang* pg_resetxlog* vacuumdb*
dropuser* pg_restore*
initdb* pg_test_fsync*
複数のバージョンが 1 つのマシンで稼動することがあるため コマンドも複数のバージョンが格納できるように サブディレクトリが分かれます。
ただ、実際は /usr/lib/postgresql/9.1/bin には パスが通っておらず /usr/bin 以下にあるリンクファイルを起動することになります。
/usr/bin
$ ls -F /usr/bin (PostgreSQL 関連のみ抜粋) clusterdb@ pg_basebackup@ pg_lsclusters@ createdb@ pg_config@ pg_restore@ createlang@ pg_createcluster@ pg_upgradecluster@ createuser@ pg_ctlcluster@ psql@ dropdb@ pg_dropcluster@ reindexdb@ droplang@ pg_dump@ vacuumdb@ dropuser@ pg_dumpall@ vacuumlo@
このように /usr/bin/ 以下のコマンドはリンクになっています。
（例外として initdb はリンクがありません）
$ readlink -f /usr/bin/vacuumdb /usr/share/postgresql-common/pg_wrapper
また、各コマンドは直接 /usr/lib/postgresql/9.1/bin 以下の ファイルにリンクするのではなく 以下のファイルにリンクしています。
/usr/share/postgresql-common/pg_wrapper
pg_wrapper は Perl のスクリプトです。
$ file /usr/share/postgresql-common/pg_wrapper
/usr/share/postgresql-common/pg_wrapper: Perl script, ASCII text executable
前述したように 1つのマシン内で複数のバージョンが稼動することができるため 一旦この pg_wrapper で、コマンドを受けて、環境変数などから適切なバージョンの コマンドを起動するようになっているわけです。
今回は設定ファイルが格納されるディレクトリについて。
とはいえパッケージでインストールしているので
Ubuntu ベースの話になります。
Ubuntu では基本的に /etc の直下か 配下にパッケージ名のディレクトリで格納されます。
/etc/postgresql
ただし PostgreSQL の場合 /etc/postgresql の下には （インストール時点では） 9.1 というサブディレクトリしかなく さらにその中には main というサブディレクトリしかありません。
/etc/postgresql/9.1/main
そして /etc/postgresql/9.1/main の下に 設定ファイルが格納されています。
$ ls -F /etc/postgresql/9.1/main/
environment pg_hba.conf postgresql.conf
pg_ctl.conf pg_ident.conf start.conf
この 9.1/main ですが、 これらは PostgreSQL の“データベースクラスタ”を示しています。
[参考サイト]
データベースクラスタの作成 - PostgreSQL 9.1.5文書
データベースクラスタは、ロールやテーブルスペースを共有するデータベースの塊です。 Ubuntu では、1つのサーバ内に複数のデータベースクラスタを作成できるように あらかじめ設定ファイルなどのディレクトリにデータベースクラスタの サブディレクトリを作成してくれているわけです。
パッケージのインストール時に データベースクラスタを作成してくれるため 1つしかデータベースクラスタを使用しない場合でも 最初から 9.1/main のようにデータベースクラスタを識別するディレクトリになっています。
ちなみにインストール時にデータベースクラスタを自動で作成しないディストリビューションや コンパイルしてインストールした場合や、追加でデータベースクラスタを作成する場合は 上記参考サイトに記載してあるように initdb コマンドを使用して データベースクラスタを作成します。
$ initdb -D /usr/lib/postgresql/9.1/main
PostgreSQL を基本的なことから再確認していきます。
使用する環境は Ubuntu12.10 で PostgreSQL は 9.1 です。
今回はパッケージのインストールなので かなり Ubuntu 寄りの話になります。 （Ubuntu 好きなので・・・）
まず、Ubuntu12.10 で [OpenSSH Server] のみを選択して インストールした環境の場合 postgresql パッケージを apt-get すると 次のパッケージがインストールされます。
postgresql postgresql-9.1 postgresql-common postgresql-client-9.1 postgresql-client-common libpq5 sgml-base xml-core libxml2:i386 ssl-cert
sgml-base など PostgreSQL 専用じゃないパッケージ以外もあるので それらは先にインストールしてしまいます。
$ sudo apt-get install sgml-base xml-core libxml2 ssl-cert
この状態で postgresql パッケージをインストールします。
$ sudo apt-get install postgresql
すると、次のパッケージが入ります。
postgresql postgresql-9.1 postgresql-common postgresql-client-9.1 postgresql-client-common libpq5
このとき、次のディレクトリが作成されます。
/etc/postgresql /etc/postgresql-common /run/postgresql /usr/share/postgresql /usr/share/postgresql-common /var/cache/postgresql /var/lib/postgresql /var/log/postgresql
次のディレクトリにファイルやサブディレクトリが格納されます。
/usr/bin /usr/lib /usr/sbin /usr/share/doc /usr/share/man
postgresql パッケージのインストール時に 追加されたファイルとディレクトリの一覧は こちらを参照してください。
次回は、主なディレクトリについて見ていきます。
PostgreSQL で SELECT 文を実行した場合でも write failed というエラーが出ることがあります。 （というか出ました）
この場合、 PostgreSQL が稼動しているマシンの ディスクの空き容量がなくなってる可能性があります。
データを参照するだけの SELECT 文でも 再帰処理などを使って無限に回ってしまうと このエラーが出ることがあります。
PostgreSQL にはデータベースのデフォルトの表領域があります。 テーブルなどのデータベースのオブジェクトを作成するときに 表領域を指定しなければ、そのデータベースのデフォルトの表領域に作成されます。
次の場合、データベース database1 は tablespace1 を デフォルトの表領域として設定しています。
db=# \x Expanded display is on. db=# \l+ List of databases -[ RECORD 1 ]-----+-------------------------- Name | database1 Owner | postgres Encoding | UTF8 Collation | en_US.UTF-8 Ctype | en_US.UTF-8 Access privileges | Size | 51 MB Tablespace | tablespace1 Description |
このデフォルトの表領域の変更ですが PostgreSQL の 8.4 からは ALTER DATABASE 文で 行うことができます。
db=# ALTER DATABASE database1 SET TABLESPACE tablespace2;
これで表領域が tablespace2 になります。
db=# \l+ List of databases -[ RECORD 1 ]-----+-------------------------- Name | database1 Owner | postgres Encoding | UTF8 Collation | en_US.UTF-8 Ctype | en_US.UTF-8 Access privileges | Size | 51 MB Tablespace | tablespace2 Description |
データベースクラスタのデフォルトの表領域に変更する場合は 次のように表領域に pg_default を指定します。
db=# ALTER DATABASE database1 SET TABLESPACE pg_default;
8.4 までは createdb コマンドを使って次のように 変更したりしていたんですが、バージョンアップの内容も 追いかけておかないと 新しいバージョンを使ってるメリットを享受できないですね。
$ createdb -T database1 -D pg_default database2;
PostgreSQL の PL/pgSQL のトリガーで 以下のようなエラーがでることがあります。
ERROR: cross-database references are not implemented: test.table1.column1 CONTEXT: compilation of PL/pgSQL function "test_af_chg" near line 4
原因のひとつとして、PL/pgSQL プログラム内の 変数の定義で %TYPE が抜けてる場合があります。
DECLARE wk_column1 test.table1.column1;
この場合、上記のエラーがでます。
正しくは、下のようになります。
DECLARE
wk_column1 test.table1.column1%TYPE;
意外と気づきにくいです。
ORACLE には ROWNUM という擬似列があって 結果セットに番号を振って取得することができます。 （ORDER BY に影響を受けるので順番に番号が付かないこともありますが）
PostgreSQL ではウィンドウ関数（window function）を使うと 同じようなことができます。
次のような感じです。
db=# SELECT (ROW_NUMBER() OVER()) AS rownum, f1 FROM table1; rownum | f1 --------+---- 1 | A 2 | B 3 | C 4 | D 5 | E (5 rows)
ちなみにウィンドウ関数は 標準SQL に規定されているもので PostgreSQL のみにある考え方ではありません。
PostgreSQL には ORACLE の ROWID のように 行を素早く指定するための列 ctid があります。
db=# SELECT ctid, * FROM test.table1; ctid | f1 | f2 ----------+-------+--------------- (0,1) | A143H | test@test.com (0,2) | A393G | test@test.com (0,3) | A380E | test@test.com (0,4) | A147K | test@test.com
ctid は、行バージョンの物理的位置なので 行を更新したり、VACUUM FULL で変わってしまいます。
ただ、この ctid を使用するとかなり早く行を取得できます。 このときの実行計画に出てくる演算子が Tid Scan なのです。
実際に実行計画を取得してみます。
db=# EXPLAIN db-# SELECT * FROM test.table1 WHERE ctid = '(0,2)'; QUERY PLAN ------------------------------------------------------------ Tid Scan on table1 (cost=0.00..4.01 rows=1 width=218) TID Cond: (ctid = '(0,2)'::tid)
Tid Scan と出ました。
PRIMARY KEY で検索した場合の実行計画も取得してみます。
db=# EXPLAIN db-# SELECT * FROM test.table1 WHERE field1 = 1; QUERY PLAN --------------------------------------------------------------------------- Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=50) Index Cond: ((field1)::text = '1'::text)
Index Scan よりも上の Tid Scan の方がコストが小さいですね。
前回 psql の変数について書きましたが 変数への値の代入は psql を起動するコマンドラインからもできます。 というか、バッチ処理などを考えるとそっちがメインになると思います。 （ヒアドキュメントという手もありますが）
SQL は、前回と同じものを使用します。
SELECT * FROM generate_series(1, 100) WHERE generate_series between :start AND :end;
start と end という 2 つの変数を使用しています。
コマンドラインで指定するには、v オプションを使用します。
$ psql -f test.sql -v start=3 -v end=9 db generate_series ----------------- 3 4 5 6 7 8 9 (7 rows)
特定の日付や期間で SQL を実行したい場合などに使えます。
ORACLE の sql*plus などと同じように psql でも変数を扱うことができます。
変数は set コマンドで値を代入します。
db=# \set value 2
上の例では、value という変数に 2 を入れました。
前回の echo コマンドで、値を出力できます。
db=# \echo :value 2
SQL の中で使用することもできます。
db=# SELECT :value; ?column? ---------- 2 (1 row)
変数を使って SQL の条件を設定することもできます。
db=# \set start 3 db=# \set end 10 db=# \echo :start - :end 3 - 10
変数を設定して、WHERE 句で使用します。
db=# SELECT * FROM generate_series(1, 100) db-# WHERE generate_series between :start AND :end; generate_series ----------------- 3 4 5 6 7 8 9 10 (8 rows)
意味の無い SQL ですが、3 から 10 にしぼれています。
コマンドの結果も変数に代入できますが、 文字列を扱う場合、エスケープがちょっとややこしいです。
db=# \set today ''''`date +"%Y/%m/%d"`'''' db=# select :today; ?column? ------------ 2012/12/15
psql には \echo \qecho というコマンドがあります。
db=# \echo hello
hello
このように文字列を出力することができます。
またコマンドも実行できます。
db=# \echo `date +"%Y/%m/%d"`
2012/12/15
コマンドはバッククォートで囲みます。
db=# \echo `pwd`
/tmp
次のように psql を使用しているときに ログファイルに 色々と出力できるので便利です。
$ psql -f test.sql db > test.log
会社で聞かれた 「UNION が含まれるビューがあるんだけど、インデックス使ってくれる？」という 質問に対して 「データベースがそっちの方が効率が良いと判断したら使ってくれるし、実行計画を取ってみれば？」という 回答をしたんですが、念のため試してみます。
まず、テスト用のテーブルにデータを登録します。
INSERT INTO table1 (f1) SELECT generate_series FROM generate_series(1, 10000);
INSERT INTO table2 (f1) SELECT generate_series FROM generate_series(1, 10000);
generate_series 関数は、便利ですね。
まずお試しに単純な SQL で実行計画を取得。 もちろん事前にテーブルを Analyze しておきます。
EXPLAIN SELECT * FROM table1 WHERE f1 = 1
↓結果
Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
インデックス table1_pkey が使用されています。
次に副問い合わせで UNION ALL して実行計画を取得。
EXPLAIN
SELECT *
FROM (SELECT * FROM table1
UNION ALL
SELECT * FROM table2) AS t1
WHERE f1 = 1
↓結果
Result (cost=0.00..16.54 rows=2 width=8)
-> Append (cost=0.00..16.54 rows=2 width=8)
-> Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
-> Index Scan using table2_pkey on table2 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
これもちゃんと、インデックス table1_pkey と table2_pkey が使用されています。
ちなみに UNION ALL じゃなくて UNION の場合。
EXPLAIN
SELECT *
FROM (SELECT * FROM table1
UNION
SELECT * FROM table2) AS t1
WHERE f1 = 1
↓結果
Unique (cost=16.57..16.58 rows=2 width=8)
-> Sort (cost=16.57..16.57 rows=2 width=8)
Sort Key: table1.f1, table1.f2
-> Append (cost=0.00..16.56 rows=2 width=8)
-> Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
-> Index Scan using table2_pkey on table2 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
こちらもインデックスは使ってくれます。
念のため、ビューを作成して試してみます。
CREATE VIEW view1 AS
SELECT *
FROM (SELECT * FROM table1
UNION ALL
SELECT * FROM table2) AS t1
ビューに対して実行計画を取得。
EXPLAIN SELECT * FROM view1 WHERE f1 = 1
↓結果
Result (cost=0.00..16.54 rows=2 width=8)
-> Append (cost=0.00..16.54 rows=2 width=8)
-> Index Scan using table1_pkey on table1 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
-> Index Scan using table2_pkey on table2 (cost=0.00..8.27 rows=1 width=8)
Index Cond: (f1 = 1)
結果は副問い合わせと同じでした。
こういうのは、自分の手で試しておくと安心ですね。
PostgreSQL には generate_series という集合を返すことができる すごい便利な関数があります。
[参考サイト]
集合を返す関数 - PostgreSQL 8.4.4文書
次のように使うことができます。
db=# SELECT * FROM generate_series(1, 5); generate_series ----------------- 1 2 3 4 5
ステップも入れられます。
db=# SELECT * FROM generate_series(1, 5, 2); generate_series ----------------- 1 3 5
TIMESTAMP 型もいけます。
db=# SELECT * FROM generate_series( db-# '2013/01/01'::TIMESTAMP, '2013/01/03', '1 day'); generate_series --------------------- 2013-01-01 00:00:00 2013-01-02 00:00:00 2013-01-03 00:00:00
FROM 句に書かない使い方もできます。
db=# SELECT generate_series(1, 3); generate_series ----------------- 1 2 3
集合を返す、というところがすごいところで 次のように別の集合と一緒に使えば 直積などの集合の演算結果を簡単に取得することができます。
db=# SELECT f1 FROM tab1; f1 ---- 1 2 3 db=# SELECT f1, generate_series(1, 3) FROM tab1; f1 | generate_series ----+----------------- 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 3 | 1 3 | 2 3 | 3
テストデータを作るときなどかなり役に立ちます。
PostgreSQL 関連の記事をサーフィンしていたときに 下の記事に流れ着きました。
[参考]
POSTORO: postgresは Covering Indexによる高速検索ができないらしい
なんと。
index-only とか covering index というのは 『データベースからデータを取得するときに インデックスに必要な列が全て含まれていれば テーブルのデータを読まなくても済む』という パフォーマンスを高める方法です。
狙って使用したことはありませんが なんとなく PostgreSQL でも普通に使えると思っていました。
HOTもあるので、インデックスの設計するときには 気をつけなくてはならないですね。
PostgreSQL の PRIMARY KEY を削除する SQL が Oracle と違っていたのでメモ。
PRIMARY KEY の作成は PostgreSQL, Oracle, MySQL, SQLServer も同じで 次のようになります。
ALTER TABLE "テーブル名" ADD PRIMARY KEY ("キー名"...)
削除の場合は違います。 PostgreSQL と SQLServer では次のようになります。
ALTER TABLE "テーブル名" DROP CONSTRAINT "primary key 制約名"
PRIMARY KEY の作成でできた制約を削除することで PRIMARY KEY を削除します。
Oracle, MySQL は次のようになります。
ALTER TABLE "テーブル名" DROP PRIMARY KEY
こちらの方がシンプルですね。
[参考]
テーブル定義の変更（列の追加、変更、削除） - オラクル・Oracle SQL 入門
MySQLクイック・リファレンス
開発でのメモ:SQL Serverで主キーを追加するには？ - livedoor Blog（ブログ）
PostgreSQL の SEQUENCE には CACHE という設定があります。 (Oracle などにもあります)
この機能は高速化のためのもので、事前に SEQUENCE をまとめて 取得しておき、大量に SEQUENCE を発行するときに 何度も SEQUENCE を更新しないようします。
実際の動きを見てみます。
db=# CREATE SEQUENCE test_seq CACHE 3;
CACHE のみ設定して（他はデフォルトで）作成しました。
下のように設定されています。
db=# SELECT last_value, cache_value FROM test_seq; -[ RECORD 1 ]-- last_value | 1 cache_value | 3
nextval() で値を取得します。
db=# SELECT nextval('test_seq'); → 1 が返る
SEQUENCE は次のようになります。
db=# SELECT last_value, cache_value FROM test_seq; -[ RECORD 1 ]-- last_value | 3 cache_value | 3
1 回しか nextval() していませんが last_value は 3 になっています。 これは 1 を取得したときに 2, 3 をキャッシュとして取得しているためです。
もちろん nextval() を続ければ 2, 3 を取得できます。
db=# SELECT nextval('test_seq'); → 2 が返る db=# SELECT nextval('test_seq'); → 3 が返る
これでキャッシュした分を使い切ってしまったのですが さらに nextval() してみます。
db=# SELECT nextval('test_seq'); → 4 が返る
この状態で SEQUENCE は次のようになります。
db=# SELECT last_value, cache_value FROM test_seq; -[ RECORD 1 ]-- last_value | 6 cache_value | 3
このように、キャッシュを使い切ると新しくキャッシュを取得しなおします。 デフォルトでは CACHE が 1 なので取得するごとにキャッシュを使い切っているわけです。
キャッシュはセッション内でのみ有効です。 この状態は、このセッション以外からは 6 まで使用済みとして扱うため 別のセッションで nextval() すると次のように 7 が返ります。
[別セッション] db=# SELECT nextval('test_seq'); → 7 が返る [このセッション] db=# SELECT nextval('test_seq'); → 5 が返る
そのためキャッシュを使用すると SEQUENCE の値が時系列に 並ばないことがあるので注意が必要です。
PostgreSQL の SEQUENCE に値を設定するには ALTER SEQUENCE と setval() の 2 つの方法があります。
さらに setval() には 引数が 2 つのものと 3 つのものがあります。 それぞれの実行結果を見ていきます。
まず ALTER SEQUENCE 。
db=# ALTER SEQUENCE test_sequence RESTART WITH 1;
このとき、内部の値は次のようになっています。
db=# SELECT last_value, is_called FROM test_sequence; -[ RECORD 1 ]-- last_value | 1 is_called | f
last_value が RESTART WITH で設定した 1 になり、 is_called が f (偽) になります。
is_called というのは last_value が既に呼ばれたかどうかのフラグで is_called が偽であれば まだ呼ばれていないので nextval() したときに last_value の 1 を返します。
次に 引数が 2 つの setval() です。
db=# SELECT setval('test_sequence', 1);
このとき、内部の値は次のようになっています。
db=# SELECT last_value, is_called FROM test_sequence; -[ RECORD 1 ]-- last_value | 1 is_called | t
引数が 2 つの setval() では last_value は同じですが is_called が t (真) になりました。 last_value の 1 は既に呼ばれているという内容なので この場合 nextval() したときに 1 + increment_by (デフォルト 1) を返します。
同じように 1 を設定しても ALTER SEQUENCE と 引数が 2 つの setval() では 次に返す値が変わってしまいます。
そこで 引数が 3 つの setval() の出番になります。
db=# SELECT setval('test_sequence', 1, false);
上のように 引数が 3 つの setval() では 第三引数で is_called の値を指定することができます。
これで ALTER SEQUENCE したときと同じになります。
db=# SELECT last_value, is_called FROM test_sequence; -[ RECORD 1 ]-- last_value | 1 is_called | f
SEQUENCE はデータの一意性を守ってくれる大事な機能なので 移行時などにずれてしまわないよう注意が必要です。
PostgreSQL の SEQUENCE の実体はテーブルです。 CREATE SEQUENCE したときに SEQUENCE と同じ名前の 1 行だけの特殊なテーブルを作成します。
次のように \d で SEQUENCE の状態を確認することができます。
db=# \d test_sequence
Sequence "public.test_sequence"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | test_sequence
last_value | bigint | 9
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 26
is_cycled | boolean | f
is_called | boolean | t
SEQUENCE の実体はテーブルなので 次のように SQL の FROM 句に SEQUENCE 名を指定しても 中身を見ることができます。
db=# \x Expanded display is on. db=# SELECT * FROM public.test_sequence; -[ RECORD 1 ]-+-------------------- sequence_name | test_sequence last_value | 9 start_value | 1 increment_by | 1 max_value | 9223372036854775807 min_value | 1 cache_value | 1 log_cnt | 26 is_cycled | f is_called | t
PostgreSQL は view もテーブルとルールを使って実現していますが 少ない実装で多くの機能を実現するというのはカッコいいですね。
PostgreSQL の psql にはヘルプを表示する便利なコマンド \h があります。
使い方は簡単です。
引数なしで実行すると SQL のコマンド一覧が表示されます。
db=# \h
ABORT CREATE LANGUAGE
ALTER AGGREGATE CREATE OPERATOR
ALTER CONVERSION CREATE OPERATOR CLASS
ALTER DATABASE CREATE OPERATOR FAMILY
ALTER DOMAIN CREATE ROLE
ALTER FOREIGN DATA WRAPPER CREATE RULE
ALTER FUNCTION CREATE SCHEMA
ALTER GROUP CREATE SEQUENCE
ALTER INDEX CREATE SERVER
ALTER LANGUAGE CREATE TABLE
引数にコマンドを付けるとコマンドの説明を表示します。
db=# \h COPY
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
SQL コマンドの使い方を忘れたときに 手元にリファレンスがなくても その場で確認できるので 便利です。
PostgreSQL の psql では \timing を使うと 実行時間を計測することができます。
db=# \timing
Timing is on.
これで計測が on になりました。
後は通常通り SQL を実行します。
db=# select 1;
?column?
----------
1
(1 row)
Time: 2.422 ms
もう 1 度実行すると off になります。
db=# \timing
Timing is off.
PostgreSQL は PL/pgSQL を使って ORACLE の PL/SQL のように 関数を作ることができますが 手続き言語を SQL でも簡単に関数を作ることができます。
次のように定義します。
CREATE FUNCTION test_func (
i1 integer,
i2 integer
)
RETURNS integer
AS
$$
SELECT $1 + $2
$$
LANGUAGE sql IMMUTABLE STRICT;
呼び出すときは同じです。
db=# SELECT test_func(4, 3);
7
PostgreSQL を使っていて認証が md5 などになっている場合 psql で接続するたびにパスワードを聞いてきます。
これが面倒な場合は 環境変数 PGPASSWORD にパスワードを設定しておくことができます。
$ echo "select * from pg_tables" | psql sample_db
Password:
通常は↑のようになります。
$ export PGPASSWORD=xxxxxx $ echo "select * from pg_tables" | psql sample_db
パスワードを聞いてこなくなります。
（ xxxxxx は、パスワードです）
バッチ処理などでもこの方法が使えます。
以下の SQL の 'xxxxx' の部分を列名に変更します。
SELECT schemas.nspname AS schema_name
, tables.relname AS table_name
FROM pg_attribute AS colmuns
INNER JOIN pg_class AS tables
ON colmuns.attrelid = tables.oid
INNER JOIN pg_namespace AS schemas
ON tables.relnamespace = schemas.oid
WHERE colmuns.attname = 'xxxxx'
ORDER BY schema_name, table_name
テーブルを再作成したときなど、テーブルに serial の列がある場合 既存の番号と重ならないように順序（ sequence ）の現在の値を設定する必要があります。
順序の現在の値を設定するには次の setval() を使用します。
# SELECT setval('順序名', 3, true);
設定した値を確認するには currval() を使用します。
# SELECT currval('順序名');
ただし、currval() は setval() か nextval() をしたセッション内でしか 使用できません。
PostgreSQL に格納されている PL/pgSQL 関数のソースを確認するメモです。
次の test という関数で確認してみます。
CREATE OR REPLACE FUNCTION test(f1 TEXT, f2 TEXT) RETURNS TEXT AS $$ DECLARE BEGIN RETURN 'This is a pen!'; END; $$ LANGUAGE PLPGSQL;
関数の情報が格納されている pg_proc カタログから取得します。 prosrc 列にソースが格納されています。
db=# SELECT prosrc FROM pg_proc WHERE proname = 'test'; prosrc ------------------------------ DECLARE BEGIN RETURN 'This is a pen!'; END; (1 row)
pg_proc カタログには 他にも色々な情報が格納されています。
db=# \x db=# SELECT * FROM pg_proc WHERE proname = 'test'; -[ RECORD 1 ]--+----------------------------- proname | test pronamespace | 27944 proowner | 16384 prolang | 34946 procost | 100 prorows | 0 proisagg | f prosecdef | f proisstrict | f proretset | f provolatile | v pronargs | 2 prorettype | 25 proargtypes | 25 25 proallargtypes | proargmodes | proargnames | {f1,f2} prosrc | : DECLARE : BEGIN : RETURN 'This is a pen!'; : END; : probin | - proconfig | proacl |
proargnames が引数の名前で proargtypes が引数のデータ型です。 prorettype は戻り値のデータ型です。
いきなり PREPARE と書いても 知らない人は「何それ？」となると思います。 PREPARE は PostgreSQL において SQL 文の予約をしてくれるメソッドです。 SQL 文の一時的な関数化と考えても良いかもしれません。
実際の使い方ですが、次のように宣言します。
db=# PREPARE sql_1 AS SELECT tablename from pg_tables; PREPARE
これで sql_1 という名前で登録できました。
予約した SQL 文を実行するには EXECUTE を使用します。
db=# EXECUTE sql_1; tablename ------------------------- sql_features sql_implementation_info sql_languages sql_packages sql_sizing sql_sizing_profiles
PREPARE には SELECT,INSERT,UPDATE,DELETE が使用できて 接続が切れるまで有効です。
SQL 文中の値を引数で変えることもできます。
db=# PREPARE sql_2 (varchar) AS SELECT tablename db-# from pg_tables db-# where tablename = $1; PREPARE
引数は 1つめから順番に 変数 $1,$2,$3... で受けます。
EXECUTE で実行するときに値を指定します。
db=# EXECUTE sql_2('pg_index');
tablename
-----------
pg_index
(1 row)
大きなメリットとして EXECUTE したときに実行計画などの準備をしないため （ PREPARE したときに準備されます） 同じ SQL 文を何度も使用する場合、速度が上がります。
前回 VACUUM の確認について書きましたが 今回は ANALYZE の確認について書きたいと思います。
まずはテーブル analyze_test を作成します。
db=# CREATE TABLE analyze_test (f1 VARCHAR(10), f2 INTEGER);
CREATE TABLE
ANALYZE も情報を pg_stat_all_tables から取得できます。
db=# SELECT * FROM pg_stat_all_tables db-# WHERE relname = 'analyze_test'; -[ RECORD 1 ]----+------------------------------ relid | 16469 schemaname | public relname | analyze_test seq_scan | 2 seq_tup_read | 9 idx_scan | idx_tup_fetch | n_tup_ins | 5 n_tup_upd | 1 n_tup_del | 1 n_tup_hot_upd | 1 n_live_tup | 4 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze |
ANALYZE する前はこのような状態です。
また、pg_stat_all_tables の他に 列ごとの統計情報が pg_stats ビューから取得できます。
db=# SELECT * FROM pg_stats WHERE tablename = 'analyze_test';
(No rows)
ANALYZE する前は レコードが取得できません。
それでは ANALYZE を実行します。
db=# ANALYZE analyze_test;
ANALYZE
状態を確認します。
db=# SELECT * FROM pg_stat_all_tables db-# WHERE relname = 'analyze_test'; -[ RECORD 1 ]----+------------------------------ relid | 16469 schemaname | public relname | analyze_test seq_scan | 2 seq_tup_read | 9 idx_scan | idx_tup_fetch | n_tup_ins | 5 n_tup_upd | 1 n_tup_del | 1 n_tup_hot_upd | 1 n_live_tup | 4 n_dead_tup | 0 last_vacuum | last_autovacuum | last_analyze | 2010-04-24 15:49:12.383112+09 last_autoanalyze |
last_analyze に ANALYZE した時刻が格納されました。
列ごとの統計情報を確認します。
db=# SELECT * from pg_stats WHERE tablename = 'analyze_test'; -[ RECORD 1 ]-----+-------------------------- schemaname | public tablename | analyze_test attname | f1 null_frac | 0 avg_width | 6 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {00001,00003,00004,00005} correlation | -0.2 -[ RECORD 2 ]-----+-------------------------- schemaname | public tablename | analyze_test attname | f2 null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {15,30,40,50} correlation | -0.2
ANALYZE したテーブルの列のレコードが追加されています。
histogram_bounds に列の値のサンプルが入っています。
（行が少ないので全ての値が入っています）
PostgreSQL は データを更新したときや 削除したときに レコードに「不要」マークを付けるという処理を行ないます。
「不要」マークの付いたレコード（以下、不要なレコード）は そのままでは再利用できないのでデータを挿入しなくても 更新を繰り返すだけで テーブルの使用するスペースは広がっていきます。
VACUUM は、不要になったレコードのスペースを 再利用可能にします。 今回は、その動きを確認してみたいと思います。
まずは新しくテーブル vacuum_test を作成します。
db=# CREATE TABLE vacuum_test (f1 VARCHAR(10), f2 INTEGER);
CREATE TABLE
情報は pg_stat_all_tables カタログから取得します。
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 0 n_dead_tup | 0 last_vacuum | last_autovacuum |
テーブルを作成した時点では上のようになっています。
VACUUM に関連する主な列のみ取得しています。
それぞれの列の意味は次のようになります。
| 列名 | 説明 |
|---|---|
| n_live_tup | 有効なレコード数 |
| n_dead_tup | 不要になったレコード数 |
| last_vacuum | 最後に VACUUM を実行した時刻 |
| last_autovacuum | 最後に 自動 VACUUM が実行された時刻 |
レコードを挿入します。
INSERT INTO vacuum_test VALUES ('00001', 10);
INSERT INTO vacuum_test VALUES ('00002', 20);
INSERT INTO vacuum_test VALUES ('00003', 30);
INSERT INTO vacuum_test VALUES ('00004', 40);
INSERT INTO vacuum_test VALUES ('00005', 50);
状態を確認します。
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 5 n_dead_tup | 0 last_vacuum | last_autovacuum |
5 レコード挿入したので、有効なレコード数が 5 になりました。
次に 1 レコード削除します。
db=# DELETE FROM vacuum_test WHERE f1 = '00002';
DELETE 1
状態を確認します。
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 4 n_dead_tup | 1 last_vacuum | last_autovacuum |
有効なレコード数が 4 になり 不要なレコード数が 1 になりました。
ついでに更新もします。
db=# UPDATE vacuum_test SET f2 = 15 WHERE f1 = '00001';
UPDATE 1
しつこく状態を確認します。
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------ n_live_tup | 4 n_dead_tup | 2 last_vacuum | last_autovacuum |
不要なレコード数が 1 増えました。
ここまでは準備です。 ようやく VACUUM を実行します。
db=# VACUUM vacuum_test;
VACUUM
状態を確認します。
db=# SELECT n_live_tup,n_dead_tup,last_vacuum,last_autovacuum db-# FROM pg_stat_all_tables db-# WHERE relname = 'vacuum_test'; -[ RECORD 1 ]----+------------------------------ n_live_tup | 4 n_dead_tup | 0 last_vacuum | 2010-04-20 22:20:20.560647+09 last_autovacuum |
不要なレコード数が 0 になりました。
last_vacuum に VACUUM を実行した時刻が入っています。
このように VACUUM で、不要になったレコードの スペースを再利用可能にすることができます。
PostgreSQL で 現在実行中のプロセスと 実行中のクエリや実行時間を取得します。
【参考サイト】
PostgreSQLのちょっとテク - よくきたWiki
SELECT
procpid AS "プロセスID",
start AS "開始時刻",
now() - start AS "実行時間",
current_query AS "実行クエリ"
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S
WHERE
current_query <> '<IDLE>'
AND
procpid <> pg_backend_pid()
ORDER BY
"実行時間" DESC;
自分自身のプロセスと idle 中のプロセスを除外しています。
件数が少ないときは、"\x" で展開表示 してあげると 長い SQL でも見やすくなります。
db=# \x db=# SELECT .... (省略) -[ RECORD 1 ]----------------------------- プロセスID | 19848 開始時刻 | 2010-03-25 22:17:48.459064+09 実行時間 | 00:00:18.431077 実行クエリ | <IDLE> in transaction
プロセスを止めたい場合は、次のようにします。
db=# SELECT pg_cancel_backend("プロセスID");
"プロセス ID" に対象のプロセス ID を入れてください。
PostgreSQL の SQL では色々な算術演算子が使えます。
今回はバイナリ演算子を除いたものを紹介したいと思います。
まずは四則演算。
db=# -- 和 db=# SELECT 1 + 1; db=# -- 差 db=# SELECT 3 - 2; db=# -- 積 db=# SELECT 4 * 6; db=# -- 商 db=# SELECT 7 / 2;
商は、整数の割り算の場合、切り捨てになります。
他にも色々あります。
同じ計算をする関数と併せてどうぞ。
db=# -- べき乗 db=# SELECT 2 ^ 5; db=# SELECT POWER(2, 5); db=# -- 剰余（割り算の余り） db=# SELECT 13 % 5; db=# SELECT MOD(13, 5); db=# -- 平方根 db=# SELECT |/ 9; db=# SELECT sqrt(9); db=# -- 立方根 db=# SELECT ||/ 8; db=# SELECT cbrt(8); db=# -- 絶対値 db=# SELECT @ -4; db=# SELECT abs(-4);
関数はありませんが、階乗の演算子もあります。
階乗の演算子は左右の 2 バージョンがあります。
db=# -- 階乗（左） db=# SELECT !! 4; db=# -- 階乗（右） db=# SELECT 4 !;
db=# SELECT DATE_TRUNC('month', now() + '1 months') + '-1 days';
?column?
------------------------
2010-02-28 00:00:00+09
(1 row)
翌月の月初日から 1 日引きます。
PostgreSQL には 通常の DISTINCT ではない DISTINCT ON というものがあります。
次のようなテーブルがあるとします。
db1=# SELECT * FROM distinct_test;
field1 | field2 | field3
--------+--------+--------
1 | 4 | 1
1 | 4 | 2
2 | 2 | 1
2 | 2 | 2
3 | -1 | 1
3 | -1 | 2
(6 rows)
まずは通常の DISTINCT です。
db1=# SELECT DISTINCT field1, field2
FROM distinct_test;
field1 | field2
--------+--------
1 | 4
2 | 2
3 | -1
(3 rows)
この場合、列に指定した field1, field2 の順番でソートされます。
列の指定の順番を変えます。
db1=# SELECT DISTINCT field2, field1
FROM distinct_test;
field2 | field1
--------+--------
-1 | 3
2 | 2
4 | 1
(3 rows)
今度は field2, field1 でソートされます。
これはまぁ、こういうものです。
DISTINCT ON を使ってみます。
DISTINCT ON (field1, field2) のカッコの中と
列の指定 "field1, field2, field3" が
違うことに注意してください。
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 1 2 | 2 | 1 3 | -1 | 1 (3 rows)
行数は DISTINCT で field1, field2 を指定したときと同じです。
つまり DISTINCT ON を使用すると カッコの中で指定した列で まとめられ、カッコの中で指定していない列（この例だと field3 ）は 最初に見つかったものが出力されます。
「 DISTINCT でまとめたいけど、他の列も欲しい！」というときに便利ですね。 ただ、このままだと DISTINCT ON に指定していない列は どの値が出るのか保障されないため、ソートをかけてやる必要があります。
ORDER BY によるソートは次のようになります。
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field1; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 1 2 | 2 | 1 3 | -1 | 1 (3 rows) db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field2; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field3; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
DISTINCT ON の先頭で指定されている field1 以外の 列では エラーになってしまいます。
ORDER BY でソートするには、 DISTINCT ON で指定した列（指定順）の後に、DISTINCT ON 以外の列を書く必要があります。
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field1, field2, field3 DESC; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 2 2 | 2 | 2 3 | -1 | 2 (3 rows)
こうすることによって GROUP BY で MAX() を使ったのと 同じ値を取得すことができました。
ブログなどのサイト上で SQL の実行結果を載せることがありますが 次のように出力されると ちょっと見辛いものがあります。
db1=# select * from pg_database where datname = 'postgres';
datname | datdba | encoding | datistemplate | datallowconn | datco
nnlimit | datlastsysoid | datvacuumxid | datfrozenxid | dattablespac
e | datconfig | datacl
----------+--------+----------+---------------+--------------+------
--------+---------------+--------------+--------------+-------------
--+-----------+--------
postgres | 10 | 6 | f | t |
-1 | 10792 | 499 | 499 | 16
63 | |
(1 row)
こういうときに展開表示を使います。
db1=# \x
Expanded display is on.
この設定によって 次のように結果が縦に展開されます。
db1=# select * from pg_database where datname = 'postgres';
-[ RECORD 1 ]-+---------
datname | postgres
datdba | 10
encoding | 6
datistemplate | f
datallowconn | t
datconnlimit | -1
datlastsysoid | 10792
datvacuumxid | 499
datfrozenxid | 499
dattablespace | 1663
datconfig |
datacl |
psql の起動時から設定するときは -x オプションを使用します。
$ psql -x db1
PostgreSQL では pg_hba.conf を使って接続設定を行いますが その前に ネットワーク接続の設定を postgresql.conf に行う必要があります。 （デフォルトでも動くと思いますが）
PostgreSQL は、TCP/IP ソケット や UNIX ドメインソケット を使って接続します。 わからなければ、TCP/IP ソケット は、ネットワーク、UNIX ドメインソケット は ローカル → ローカルの非ネットワーク通信だと考えてください。 外部から接続する場合、TCP/IP ソケット を使いますが、 ローカルから使う場合は TCP/IP ソケット でも UNIX ドメインソケット でも通信できます。
設定は postgresql.conf の listen_addresses パラメータを使用しますが CentOS や Redhat では デフォルトでは 次のようにコメントアウトされています。 コメントアウトされている場合のデフォルト値は localhost です。
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
この場合に PostgreSQL が LISTEN しているアドレスを netstat コマンドで 確認してみます。
localhost なので 次のように 127.0.0.1 で LISTEN していました。 また、ついでに UNIX ドメインソケットも見えます。
# netstat -an | grep LISTEN | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN unix 2 [ ACC ] STREAM LISTENING 343193 /tmp/.s.PGSQL.5432
この設定では IP アドレスが 127.0.0.1 として アクセスされたものしか接続できません。 外部から 192.168.1.92 といった IP アドレスでアクセスされた場合は 接続できないわけです。
外部から接続させるには 通常 listen_addresses パラメータに '*' (アスタリスク) を設定します。
listen_addresses = '*'
この場合は netstat の結果が 0.0.0.0 となります。
# netstat -an | grep LISTEN tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN unix 2 [ ACC ] STREAM LISTENING 343193 /tmp/.s.PGSQL.5432
0.0.0.0 の場合 127.0.0.1 でも 192.168.1.92 でも接続することができます。
他にも カンマで区切って指定することもできます。
listen_addresses = 'localhost,192.168.1.92'
この場合は netstat の結果が 次のように複数出てきます。
# netstat -an | grep LISTEN tcp 0 0 192.168.1.92:5432 0.0.0.0:* LISTEN tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN unix 2 [ ACC ] STREAM LISTENING 343193 /tmp/.s.PGSQL.5432
TCP/IP ソケット を使用せず UNIX ドメインソケットのみで使用する場合は listen_addresses パラメータに 空文字を設定します。
listen_addresses = ''
この場合は netstat の結果が 次のように複数出てきます。
# netstat -an | grep LISTEN
unix 2 [ ACC ] STREAM LISTENING 343193 /tmp/.s.PGSQL.5432
(Web サーバなどの) アプリケーションサーバと PostgreSQL サーバが同じマシンで 外部から接続させる必要がないのであれば、こうしておくと ネットワークを使用した接続を禁止できます。
PostgreSQL で 次の WARNING が発生することがあります。
WARNING: nonstandard use of escape in a string literal HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
これは SQL の中にバックスラッシュの文字リテラルが ある場合に起こるのですが HINT の通り 'E' を付けることで対応できます。
次のような SQL で発生します。
警告は出ますが、結果も取得できます。
db1=# SELECT REPLACE(field1, '\n', '\\n') FROM table1;
WARNING: nonstandard use of escape in a string literal
LINE 1: SELECT REPLACE(field1, '\n', '\\n') FROM table1
^
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING: nonstandard use of \\ in a string literal
LINE 1: SELECT REPLACE(field1, '\n', '\\n') FROM table1
^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
replace
------------------------------------------------------
test "test!"\ntest "test!"\ntest "test!"\nこんにちは
HINT に従って 文字リテラルに 'E' を付けます。
db1=# SELECT REPLACE(field1, E'\n', E'\\n') FROM table1; replace ------------------------------------------------------ test "test!"\ntest "test!"\ntest "test!"\nこんにちは
これで WARNING が出なくなりました。
PostgreSQL はログをローテーションする機能を持っています。
Redhat であればデフォルトで 次のようにMonやWedなどの曜日の文字列が 付与されて、ローテーションする形になっています。
$ ls /var/lib/pgsql/data/pg_log
postgresql-Mon.log
postgresql-Tue.log
postgresql-Wed.log
これは postgres.conf の log_filename パラメータで 設定することができます。 Redhat のデフォルト（曜日）は次のように設定されています。
log_filename = 'postgresql-%a.log'
年月日を付与するには、次のように設定します。
log_filename = 'postgresql-%Y%m%d.log'
これで、次のようなログファイルができます。
$ ls /var/lib/pgsql/data/pg_log
postgresql-20091005.log
postgresql-20091006.log
postgresql-20091007.log
PostgreSQL には SQL でセッションの情報を取得する関数が 色々と定義されています。 簡単に紹介していきたいと思います。
接続した PostgreSQL サーバのバージョンの文字列を返します。
db1=# SELECT version();
version
-------------------------------------------------------------------
PostgreSQL 8.3.5 on i386-redhat-linux-gnu, compiled by GCC gcc (GC
(1 row)
接続した データベースの名前を返します。
$ psql db1 postgres db1=# SELECT current_database(); current_database ------------------ db1 (1 row)
現在のスキーマ名を返します。
接続した直後は、postgresql.conf の search_path パラメータの 定義に依存した値を返します。 検索パスが空の場合は NULL を返します。
db1=# SELECT current_schema();
current_schema
----------------
public
(1 row)
CREATE TABLE などをする際に、スキーマ名を明示的に 定義しない場合、このスキーマ名が設定されます。
検索パスは set で変えることができます。
db1=# set search_path = 'hogehoge'; db1=# SELECT current_schema(); current_schema ---------------- hogehoge (1 row) db1=# set search_path = 'public','hogehoge'; db1=# SELECT current_schema(); current_schema ---------------- public (1 row)
検索パスにスキーマを複数指定した場合は、先頭のスキーマが 現在のスキーマとなります。
検索パス内のスキーマを返します。 boolean に true をセットすると暗黙のスキーマも返します。
db1=# SELECT current_schemas(false); current_schemas ----------------- {postgres,public} (1 row) db1=# SELECT current_schemas(true); current_schemas ----------------- {pg_catalog,postgres,public} (1 row)
データベースに接続したユーザ名を返します。
$ psql db1 postgres db1=# SELECT session_user; session_user -------------- postgres (1 row) db1=# \q $ psql db1 hogehoge db1=# SELECT session_user; session_user -------------- hogehoge (1 row)
current_user と user は同じです。
current_user は、現在実行しているユーザ名を返します。 通常は session_user と同じですが、 SECURITY DEFINER 属性が付いた関数を実行した場合に 返す値が違ってきます。 SECURITY DEFINER 属性は、関数を作成したユーザの権限で 実行するオプションです。
$ psql db1 postgres db1=# SELECT session_user, current_user; session_user | current_user --------------+-------------- postgres | postgres (1 row)
SECURITY DEFINER 属性を付けた関数 test1() と 付けていない test2() を hogehoge ユーザで作成してみます。
$ psql db1 hogehoge db1=# SCREATE FUNCTION test1() RETURNS name AS 'SELECT current_user;' SECURITY DEFINER LANGUAGE SQL ; CREATE FUNCTION db1=# SCREATE FUNCTION test2() RETURNS name AS 'SELECT current_user;' LANGUAGE SQL ; CREATE FUNCTION
これを postgres ユーザで実行します。
$ psql db1 postgres db1=# select test1(); test1 ----------- hogehoge (1 row) db1=# select test2(); test1 ----------- postgres (1 row)
異なる値を返しました。 このように実際に実行している権限を確認する場合に current_user を使用します。
また、session_user を含め、ユーザ系は特殊で、括弧 () を付けずに 呼び出す必要があるので注意しましょう。
CentOS や Redhat で PostgreSQL を入れた時に 日本語のソート順がおかしくなることがあります。
item_nm
----------------------
くり
なし
もも
みかん
りんご
いちじく
さくらんぼ
50 音順ではなく文字数の順番で 並んでしまうことがあります。
$ pg_controldata /var/lib/pgsql/data
Debian 系は /usr/lib/postgresql/x.y/bin/pg_controldata に あります。 (x.y は 8.1 などのバージョンです)
$ /usr/lib/postgresql/x.y/bin/pg_controldata \
/var/lib/postgresql/x.y/main/
この結果で、LC_COLLATE や LC_CTYPE が 下のように出力される場合、 日本語のソートがおかしくなります。
LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8
CentOS などでは、/etc/init.d/pgsql を実行したときに $PGDATA (/var/lib/pgsql/data) が存在しないと initdb を実行するのですが そのときのオプションに --no-local の指定がないので OS の環境がそのままセットされてしまうわけです。
ちなみに PostgreSQL CE の試験でも 日本語を使用する場合は --no-local を指定するのが良いとされていました。
こうなっている場合、initdb からやりなおす必要があります。 既存のデータはダンプを取っておけばリストアすることができます。
$PGDATA (/var/lib/pgsql/data) をリネームして 手動で "initdb --no-local" を実行することで pg_controldata の結果が次のようになります。
LC_COLLATE: C LC_CTYPE: C
ソート順も正しくなります。
item_nm
----------------------
いちじく
くり
さくらんぼ
なし
みかん
もも
りんご
OS 全体を変えても問題ない場合は、 /etc/sysconfig/i18n を修正するという手もあるようです。
LANG="ja_JP.UTF-8" # (修正後)
この場合 --no-local を設定する必要がなくなるので 既存の $PGDATA を消して /etc/init.d/pgsql を実行するだけです。
いずれにせよ、既存のデータがある場合 initdb を実行する前に 退避させておかないといけないので 注意してください。
タイトルの通りですが、PostgreSQL では、TRUNCATE TABLE も トランザクション内なので、ロールバックすると データは元に戻ります。
次のようになります。
postgres=# select * from test1; f1 | f2 ----+---- 1 | 1 2 | 2 (2 rows) postgres=# begin; BEGIN postgres=# truncate table test1; TRUNCATE TABLE postgres=# select * from test1; f1 | f2 ----+---- (0 rows) postgres=# rollback; ROLLBACK postgres=# select * from test1; f1 | f2 ----+---- 1 | 1 2 | 2 (2 rows)
面白いのは、CREATE TABLE などもトランザクション内です。
postgres=# begin; BEGIN postgres=# create table test2 (f1 varchar(1), f2 int); CREATE TABLE postgres=# select * from test2; f1 | f2 ----+---- (0 rows) postgres=# rollback; ROLLBACK postgres=# select * from test2; ERROR: relation "test2" does not exist
当然、このトランザクションがコミットするまでは、 別のトランザクションからは test2 テーブルは見えません。
別のトランザクションが絡んでくると少しややこしくなります。
トランザクション A がトランザクション中にテーブルを作成しているときに 他のトランザクション B が同じテーブル名でテーブルを作成しようとすると トランザクション B は A の結果待ちになります。
トランザクション A がロールバックして テーブルの作成がキャンセルされた場合 トランザクション B がテーブルを作成できるので、 トランザクション B はトランザクションを継続できます。
しかし、トランザクション A がコミットして テーブルを作成が確定してしまった場合は、 トランザクション B はテーブルの作成ができないのため B 側は、次のエラーが発生します。
ERROR: duplicate key value violates
unique constraint "pg_type_typname_nsp_index"
DROP TABLE も同様のことが起こります。
あまりこういったケースはないと思いますが、 この辺りの動きは Oracle と異なるので注意が必要です。
次のような PostgreSQL の SQL エラーがあります。
SELECT DISTINCT, ORDER BY expressions must appear in select list
これは SELECT DISTINCT を使用している場合に、 ORDER BY 句に含まれている項目が SELECT 句にない場合に発生します。
例えば、次のような SQL です。
SELECT DISTINCT
field_1
, field_2
FROM test_table
ORDER BY field_3
SELECT DISTINCT されているのは field_1 と field_2 ですが ソートは field_3 を指定しています。
これでは結果の並び順を保証することができないため エラーが発生するわけです。
ちなみに Oracle の場合は、次のようなエラーになります。
ORA-01791: SELECT式が無効です
ただ、Oracle は さらに賢いようで、次のような場合はエラーになりません。 （ PostgreSQL はエラーになります）
SELECT DISTINCT
field_1
, field_2
FROM test_table
WHERE field_3 = '0'
ORDER BY field_3
この場合は、field_3 が 1つに固定されるため、 "ソート指定なし" と同じ状態になるためエラーになりません。
メモ。
=# SELECT attr.attname AS column_name -# FROM pg_attribute AS attr -# INNER JOIN pg_stat_user_tables AS stat -# ON attr.attrelid = stat.relid -# AND stat.schemaname = 'スキーマ名' -# AND stat.relname = 'テーブル名' -# INNER JOIN pg_constraint cons -# ON attr.attnum = ANY (cons.conkey) -# AND cons.contype = 'p' -# AND cons.conrelid = stat.relid;
主キー項目だけが出力されます。
次のようなテーブルが データベースにあるとします。
| group_name | food_name |
|---|---|
| fruits | orange |
| fruits | apple |
| fruits | pears |
| fish | sardines |
| fish | tuna |
| fish | bream |
画面に表示する場合は、 次のようにグループ化して表示したくなることが あるんじゃないかと思います。 私はそうです。
| group_name | food_name |
|---|---|
| fruits | orange |
| apple | |
| pears | |
| fish | sardines |
| tuna | |
| bream |
そのためには、グループごとに件数を取得するなど 追加で処理を作成する必要があるのですが、 今回は集約関数を作って擬似的に対応してみます。
集約関数とは max(*) や count(*) のように 複数のレコードに対して処理を行なう関数です。
まず定義です。
CREATE AGGREGATE 文を使用します。
詳しくは、以下を参照してください。
PostgreSQL 8.0.2 文書 - CREATE AGGREGATE
こんな感じです。
CREATE AGGREGATE array_accum
(
BASETYPE = anyelement
, SFUNC = array_append
, STYPE = anyarray
, INITCOND = '{}'
);
テーブルは次のようになっています。
db=# select group_name, food_name from foods;
group_name | food_name
------------+-----------
fruits | orange
fruits | apple
fruits | pears
fish | sardines
fish | tuna
fish | bream
(6 rows)
作成した集約関数 array_accum を使ってみます。
db=# select group_name db-# , array_accum(food_name) as food db-# from foods db-# group by group_name; group_name | food ------------+----------------------- fruits | {orange,apple,pears} fish | {sardines,tuna,bream} (2 rows)
これでは値の並びが悪いので 副問合せでテーブルをソートします。
db=# select group_name db-# , array_accum(food_name) as food db-# from (select * from foods order by food_name) as foods db-# group by group_name; group_name | food ------------+----------------------- fruits | {apple,orange,pears} fish | {bream,sardines,tuna} (2 rows)
文字列として返すために array_to_string を使って 配列を結合します。 array_to_string(配列, '区切り文字') です。
db=# select group_name db-# , array_to_string(array_accum(food_name), '/') as food db-# from (select * from foods order by food_name) as foods db-# group by group_name; group_name | food ------------+--------------------- fruits | apple/orange/pears fish | bream/sardines/tuna (2 rows)
値を受けたプログラムで処理をすることもできますが 元が改行を含まない値の場合、 次のように改行で区切って返すようにすると扱い易いです。
select group_name
, array_to_string(array_accum(food_name), chr(10)) as food
from (select * from foods order by food_name) as foods
group by group_name;
改行を <br> に置き換えると 次のようになります。
| group_name | food |
|---|---|
| fruits | apple orange pears |
| fish | bream sardines tuna |
複数行のデータを１行で返すこの方法は 色々な応用ができるので 覚えておくと役に立つと思います。
PostgreSQL 8.1 から使えるようになった autovacuum ですが 8.2 までは postgresql.conf に次のように設定していました。
# vi postgresql.conf
autovacuum = on stats_row_level = on
PostgreSQL 8.3 からは autovacuum が デフォルトで on になっているので 設定しなくても良いのですが、 設定する場合は、 stats_row_level ではなく 次のように track_counts を設定するようになりました。 （ stats_row_level を設定するとエラーになります）
autovacuum = on track_counts = on
PostgreSQL の システムカタログは Oracle の ディクショナリ に近いものです。
例えばテーブルの情報を取得するには次の pg_tables ビューを使います。
select schemaname, tablename from pg_tables;
他にも色々なシステムカタログがあります。 詳しくは PostgreSQL のサイトの情報を参照してください。