PostgerSQL ARRAY_AGG の と Oracle の LISTAGG

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 は 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 で 数値の小数点以下の 0 を削る

メモです。
(使用している 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" は 不正確な可変精度の数値データ型ですので 精度の問題には注意する必要があります。

[参考]
8.1. 数値データ型 - PostgreSQL 9.3.2文書

PostgreSQL で、一時的に全てのトリガーを無効にする

メモです。
(使用している 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 的には非推奨と書きましたが 推奨されている方法について書いておきます。

[参考]
環境変数 - 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 にしていないとエラーになります。

Linux でプロセスの環境変数を確認する

以前、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 のソート順とロケールの設定

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文書

PostgreSQL でも watch

以前、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

データの監視や 処理の繰り返しなどに 利用できそうですね。

SQL の LATERAL キーワード

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-#     部署.部署名 = 'システム1部';
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: (部署名 = 'システム1部'::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-#     部署.部署名 = 'システム1部';
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: (部署名 = 'システム1部'::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 で インデックス・オンリー・スキャン

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 と出力されます。

実行時パラメータの現在の設定を表示する SHOW

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.

PostgreSQL で設定されている外部キーの一覧を取得する

メモです。

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';

PostgreSQL Study (5)

今回はディレクトリ編の最後、ログなどのデータが格納されるディレクトリについて。
とはいえパッケージでインストールしているので 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" のようにデータベースクラスタと同じキーワードが入っています。 (ログファイル名は設定で変更できます)

PostgreSQL Study (4)

今回はテーブルなどのデータが格納されるディレクトリについて。
とはいえパッケージでインストールしているので 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/

PostgreSQL Study (3)

今回はコマンド(実行ファイル)が格納されるディレクトリについて。
とはいえパッケージでインストールしているので 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 で、コマンドを受けて、環境変数などから適切なバージョンの コマンドを起動するようになっているわけです。

PostgreSQL Study (2)

今回は設定ファイルが格納されるディレクトリについて。
とはいえパッケージでインストールしているので 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 Study (1)

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 の write failed エラー

PostgreSQL で SELECT 文を実行した場合でも write failed というエラーが出ることがあります。 (というか出ました)

この場合、 PostgreSQL が稼動しているマシンの ディスクの空き容量がなくなってる可能性があります。

データを参照するだけの SELECT 文でも 再帰処理などを使って無限に回ってしまうと このエラーが出ることがあります。

PostgreSQL でデフォルトの表領域の変更

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;

cross-database references are not implemented

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;

意外と気づきにくいです。

PostgreSQL で ROWNUM みたいなことをしたい

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 の実行計画の Tid Scan

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 の変数について書きましたが 変数への値の代入は 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 を実行したい場合などに使えます。

psql で変数を扱う

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 の出力コマンド

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 が含まれるビューとインデックス

会社で聞かれた 「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 には 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 の index-only

PostgreSQL 関連の記事をサーフィンしていたときに 下の記事に流れ着きました。

[参考]
POSTORO: postgresは Covering Indexによる高速検索ができないらしい

なんと。

index-only とか covering index というのは 『データベースからデータを取得するときに インデックスに必要な列が全て含まれていれば テーブルのデータを読まなくても済む』という パフォーマンスを高める方法です。

狙って使用したことはありませんが なんとなく PostgreSQL でも普通に使えると思っていました。

HOTもあるので、インデックスの設計するときには 気をつけなくてはならないですね。

PostgreSQL の PRIMARY KEY を削除する SQL

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 の動き

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 に値を設定する

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 の実体

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 もテーブルとルールを使って実現していますが 少ない実装で多くの機能を実現するというのはカッコいいですね。

psql の便利なコマンド \h

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 コマンドの使い方を忘れたときに 手元にリファレンスがなくても その場で確認できるので 便利です。

psql で 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.

SQL で関数作成

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 の環境変数 PGPASSWORD

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 は、パスワードです)

バッチ処理などでもこの方法が使えます。

PostgreSQL で 特定の列を含むテーブル一覧を取得する SQL

以下の 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

PostgreSQL で 順序( sequence )の値を設定する

テーブルを再作成したときなど、テーブルに serial の列がある場合 既存の番号と重ならないように順序( sequence )の現在の値を設定する必要があります。

順序の現在の値を設定するには次の setval() を使用します。

# SELECT setval('順序名', 3, true);

設定した値を確認するには currval() を使用します。

# SELECT currval('順序名');

ただし、currval() は setval() か nextval() をしたセッション内でしか 使用できません。

PostgreSQL の PL/pgSQL 関数のソースを確認する

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 は戻り値のデータ型です。

【参考サイト】
PostgreSQL 8.1.0 文書 41.24. pg_proc

PostgreSQL の PREPARE

いきなり 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 文を何度も使用する場合、速度が上がります。

PostgreSQL の ANALYZE の動きを確認する

前回 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 の動きを確認する

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 で 現在実行中のプロセスと 実行中のクエリや実行時間を取得します。

【参考サイト】
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 で使える算術演算子

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 !;

PostgreSQL で月末日の取得

db=# SELECT DATE_TRUNC('month', now() + '1 months') + '-1 days';

        ?column?
------------------------
 2010-02-28 00:00:00+09
(1 row)

翌月の月初日から 1 日引きます。

PostgreSQL の DISTINCT ON

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() を使ったのと 同じ値を取得すことができました。

psql の 展開表示

ブログなどのサイト上で 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 の ネットワーク接続設定

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 サーバが同じマシンで 外部から接続させる必要がないのであれば、こうしておくと ネットワークを使用した接続を禁止できます。

nonstandard use of escape in a string literal

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 のログファイル名に日付を付与する

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 のセッションの情報を取り出す関数

PostgreSQL には SQL でセッションの情報を取得する関数が 色々と定義されています。 簡単に紹介していきたいと思います。

version()

接続した PostgreSQL サーバのバージョンの文字列を返します。

db1=# SELECT version();
                                                version            
-------------------------------------------------------------------
 PostgreSQL 8.3.5 on i386-redhat-linux-gnu, compiled by GCC gcc (GC
(1 row)

current_database()

接続した データベースの名前を返します。

$ psql db1 postgres

db1=# SELECT current_database();
 current_database
------------------
 db1
(1 row)

current_schema()

現在のスキーマ名を返します。

接続した直後は、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)

検索パスにスキーマを複数指定した場合は、先頭のスキーマが 現在のスキーマとなります。

current_schemas(boolean)

検索パス内のスキーマを返します。 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)

session_user

データベースに接続したユーザ名を返します。

$ 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 と 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 を含め、ユーザ系は特殊で、括弧 () を付けずに 呼び出す必要があるので注意しましょう。

PostgreSQL の ソート順

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 もトランザクション内です。

タイトルの通りですが、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 と異なるので注意が必要です。

ORDER BY expressions must appear in select list

次のような 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つに固定されるため、 "ソート指定なし" と同じ状態になるためエラーになりません。

PostgreSQL で 主キー項目を取得する。

メモ。

=# 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;

主キー項目だけが出力されます。

PostgreSQL で 集約関数を作成する

次のようなテーブルが データベースにあるとします。

group_namefood_name
fruitsorange
fruitsapple
fruitspears
fishsardines
fishtuna
fishbream

画面に表示する場合は、 次のようにグループ化して表示したくなることが あるんじゃないかと思います。 私はそうです。

group_namefood_name
fruitsorange
apple
pears
fishsardines
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_namefood
fruitsapple
orange
pears
fishbream
sardines
tuna

複数行のデータを1行で返すこの方法は 色々な応用ができるので 覚えておくと役に立つと思います。

PostgreSQL 8.3 からの autovacuum 設定

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 の システムカタログ

PostgreSQL の システムカタログは Oracle の ディクショナリ に近いものです。

例えばテーブルの情報を取得するには次の pg_tables ビューを使います。

select schemaname, tablename from pg_tables;

他にも色々なシステムカタログがあります。 詳しくは PostgreSQL のサイトの情報を参照してください。