タグ: PostgreSQL

PostgreSQL で to_dec

to_hex() の逆関数は標準では提供されていません。
PL/pgSQL が使える環境であれば「Conversion between hex and dec numbers(16進数と10進数の変換)」(SQL Tricks)のページで定義している to_dec() 関数を使うのが便利です。

=# select to_hex(11);
    to_hex
   ——–
    b
   (1 row)
postgres=# create or replace function to_dec(text)
           returns integer as $$
           declare r int;
           begin
             execute E’select ”||$1|| E’\’::integer’ into r;
             return r;
           end
           $$ language plpgsql;
CREATE FUNCTION
postgres=# select to_dec(‘ff’);
 to_dec

—-
    255
(1 row)

 
別の方法として、16進数を1度ビット型に変換することでも10進数に変換できます(桁数が分かっている場合のみ)。
たとえば、2桁の16進数文字列 の hextext というカラムを10進数に変換した居場合は次のようになります。

SELECT (‘x’ || hextext)::bit(8)::integer — bit(桁数の4倍)

PostgreSQL テーブル LOCK MODE 関係図

テーブルロックモードの関係を把握しやすいように図で表してみました。
PostgreSQL LOCK MODE 関係図
() 内はそのロックを自動的に獲得するクエリです。
 
■ロックとトランザクション
SQL における排他制御の方法として、トランザクションとロックが挙げられます。
複数のクエリをトランザクションでひとつにまとめることで、(デフォルトのトランザクションレベル READ COMMITTED の場合)コミット済みの結果のみ参照されるようになり、トランザクション中の内容を外部からは見えなくなるため一見アトミック性が守られるように見えます。
 
しかし、複数の接続で同時に既存の同じレコードに対して参照と更新を分けて実行する場合などはトランザクションだけでは限界があるのも事実です。
 
たとえば以下のテーブルを考えてみましょう。

CREATE TABLE foo (
  id int NOT NULL, — 商品の識別番号
  price int NOT NULL — 値段
);
INSERT INTO foo (id, price) VALUES(1, 100); — id:1 は 100 円
INSERT INTO foo (id, price) VALUES(2, 500); — id:2 は 500 円

このテーブルに対して、以下の処理を行うものとします。

BEGIN;
SELECT id FROM foo WHERE price < 500; -- 500円未満の id:1 を得る
UPDATE foo SET price = price + 500 WHERE id = 1; — 500円未満だった id:1 を 500円増し
COMMIT;

READ COMMITED レベルで同時に同じのクエリが実行されると、id:1 が 1100 円になる可能性があります。
 
トランザクションのみでこれを防ぐには衝突する可能性のある全てのクエリのトランザクションレベルを最も厳密な SERIALIZABLE にする必要があります。

— 衝突回避版
BEGIN;
SET TRANSACTION SERIALIZABLE; — この接続のトランザクションレベルを SERIALIZABLE に。
SELECT id FROM foo WHERE price < 500; -- 500円未満の id:1 を得る
UPDATE foo SET price = price + 500 WHERE id = 1; — 500円未満だった id:1 を 500円増し
COMMIT;

ただし、 SERIALIZABLE 隔離レベルのトランザクションの実行中に他の接続で COMMIT された場合、直列性を守るためにエラーになり処理が続行できなくなります。このため、衝突した場合は ROLLBACK の後再処理を行う必要があるなど, 欠点もあります。トランザクションのみでは、参照を完全にブロックすることができないのが原因のひとつです。
 
そこで、読み込みも制御できるロックの出番というわけです。
 
■ロックの獲得方法
多くのクエリは暗黙にクエリ単位で行レベルロックを獲得しています。
ロックの種類と影響する範囲については図を参照してください。
 
複数のクエリでロックを獲得するには LOCK 構文を用います。
LOCK 構文で取得できるロックはテーブルに対するロックのみです。

BEGIN;
LOCK foo_table IN EXCLUSIVE MODE; — EXCLUSIVE ロック獲得。
SELECT * FROM foo_table;
UPDATE foo_table SET …
— その他何か色々なクエリ…
COMMIT;

たとえば EXCLUSIVE モードでロックを獲得すると、他の接続では SELECT, ANALYZE 文以外の全てのクエリ、および ACCESS_SHARE 以外の全てのロックを明示的に獲得しようとするとロック待ちになります。
※具体的には UPDATE, DELETE, INSERT 文および SELECT … FROM table FOR …, CREATE INDEX, ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, VACUUM, VACUUM FULL 文の実行を防ぎます。
また、ACCESS EXCLUSIVE, EXCLUSIVE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, SHARE の明示ロック獲得を防ぎます。
 
■ロックの解放方法
ロックはトランザクションの終了時(COMMIT, ROLLBACK実行時)に自動で解放されます。
トランザクションの途中でロックだけを解放する方法はありません。
 
参考:
–トランザクションの隔離(PostgreSQL 8.3.1 マニュアル)
–LOCK 構文(同上)
–トランザクションと隔離レベルとロック(ファイヤープロジェクト)

NULL は「値がない」のではなく「値が不明」

SQL では NULL を含む演算は IS NULL, IS NOT NULL と一部の論理演算を除き常に戻り値が NULL になります。
この結果として、NULL を値に持つレコードを IS NULL 以外で抽出できなくなります。
たとえば極端な例ですが

SELECT NULL != NULL

が true にならず NULL になったりします(つまり (NULL != NULL) IS NULL が真になる)。
 
この仕様は Java や PHP などのプログラミング言語から考えると不可解なものにうつりますが、 NULL は値が存在しないのではなくまだわかっていない(unknown)だけなのだ、と考えるとスッキリします。
 
値段のカラムに null を持つ商品について考えるとき、その商品は「無料」ではなく値段がまだ「決まっていない」だけ。携帯のメールアドレスが null のユーザがいたなら、その人は携帯電話を「持っていない」という意味ではなく、その人が携帯電話を持っているか「わからない」だけです。
 
指定した部署(department_id)以外にいる社員(employee)の名前を探す下記のようなクエリを実行しても、部署が null の社員の名前は含まれません。その社員が 1 の部署に配属されるかもしれないからです。

SELECT name FROM employee WHERE department_id != 1

null 値も結果に含めたい場合は下記のようにしなければなりません。

SELECT name FROM employee WHERE department_id != 1 AND department_id IS NULL

 
0 や に包括的なからっぽ(empty)という意味を持たせたるのも伝統的でいいですが、SQL では 0, と NULL を区別して有効に使うほうがよさそうです。
 
参考:
3値論理とNULL(CodeZine)

SQL の基礎 – JOIN (表結合)の意味の違い

MySQL / PostgreSQL / Oracle すべてのSQLサーバで基準になっているSQL標準のJOIN(LEFT JOINなど)について、基礎からしっかりまとめてみました。
続きを読む SQL の基礎 – JOIN (表結合)の意味の違い

テーブルのディスク使用量を取得する

SELECT relname, relfilenode, relpages FROM pg_class;

relname がテーブル名、relfilenode が実体ファイル名、 relpages が利用しているページ数概算。1ページは通常 8KB です。
 

SELECT c2.relname, c2.relpages
    FROM pg_class c, pg_class c2, pg_index i
    WHERE c.oid = i.indrelid
        AND c2.oid = i.indexrelid
    ORDER BY relpages DESC;

同様にインデックスの利用しているページ数を取得します。
 
参考:
– ディスク使用量の監視(PostgreSQL 8.2.5 公式マニュアル)
– pg_class(同上)

PostgreSQL で使える全文検索エンジン

– Ludia
wiki: http://ludia.sourceforge.jp/moin.cgi/#id9
 
内部で Senna と MeCab が使われています。
 
PostgreSQL に Ludia をインストールすると、Senna の構文での文字列検索を行える @@ 演算子等、全文検索用の機能が追加されます。

SELECT content FROM fooTable WHERE content @@ ‘今日は+です’

結果:

| content |
| 今日はくもりです |
| 今日はいい天気ですね。|

タイムスタンプでマイクロ秒ではなく整数秒を利用する

PostgreSQL では

SELECT CURRENT_TIMESTAMP
SELECT now()

などとすると、

2006-08-02 22:14:42.642554

のように、小数点以下の秒数まで表示してくれます。
 
これはこれでいいのですが、小数点秒を扱う必要のないケースもあります。
 
その場合は、timestamp(0) 型として扱うことで小数点以下を四捨五入します。

SELECT CAST(CURRENT_TIMESTAMP AS timestamp(0))

のようにすると、先程の結果は次のようになります。

2006-08-02 22:14:43

 
テーブルの場合も、

CREATE TABLE foo (
  created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

から

CREATE TABLE foo (
   created_on timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP
);

のようにすることで、整数秒として保存するようになります。

トランザクションでロールバックされない処理

BEGIN
— 任意の処理 ..(A)
ROLLBACK

とすれば、(A)で何をしてもロールバックしてくれるものだと思っていたらそれは勘違いのようです。
 
たとえばシーケンスなど

create sequence seq;
 
BEGIN;
SELECT nextval(‘seq’); — (B)
SELECT nextval(‘seq’); — (C)
ROLLBACK;
SELECT nextval(‘seq’); — (D)

としても (B) == (D) にはならず、BEGIN, ROLLBACK で囲まなかった場合と同じになります。
 
PGSQL-JP ML によると
– http://www.drive.ne.jp/iMA/showmail.pl?pgsql-jp=3098

 

素人的な考えだと、transaction 内での処理なんだから、値を戻して
くれてもよさそうなものなのに、と思うのですが。。

#Oracle でも sequence は戻らないと聞いたので、出来ないのが普通
#なのかなあ?

 
他にも戻らないものとしては、
create table
create index
create sequence
があります(他にもあるかも)。商用DBでも多分同じです。

公式 PGSQL ML の引用

It’s normal behaviour.
 
Sequences are locked only for duration of nextval call, not for
transaction! So, one sequence may be used simultaneously
by many transactions. No way to rollback sequence changes.
 
意訳: シーケンスのロックは nextval 呼び出しについてのロックであり、トランザクションのためのロックではありません。一つのシーケンスが同時に複数のトランザクションで利用されうるため、シーケンスの変更をロールバックする方法は存在しません。

ということですが、単純に、一つのテーブルで serial (bigserial)として利用していて、他で nextval が呼ばれないことをシステム的に保証していれば、テーブルロックを使い、ROLLBACK 時にシーケンスを元に戻してやれば、一応実現は可能かなと思います。

最後に挿入したクエリの ID を取得する

PostgreSQL 8.1 になって、
MySQL における LAST_INSERT_ID() に似た機能をもつ、LastVal() がサポートされるようになりました。

MySQL:
CREATE TABLE employee (
  id int unsigned auto_increment not null primary key,
  name varchar(128) not null
);
 
INSERT INTO employee (name) VALUES (‘John’);
SELECT LAST_INSERT_ID();

と、次のものは全く同等の意味になります。

PostgreSQL:
CREATE TABLE employee (
  id serial not null primary key,
  name varchar(128) not null
);
 
INSERT INTO employee (name) VALUES (‘John’);
SELECT LASTVAL();

 
LASTVAL() はシーケンスのインクリメント関数である NEXTVAL() が一番最後に返した値を返すものです。
 
PostgreSQL の LASTVAL() は MySQL の LAST_INSERT_ID() と同様に、
他のセッションでの NEXTVAL()の影響は受けません。
 
一度も自分のセッションで NEXTVAL() を呼び出していない場合は、”ERROR: lastval is not yet defined in this session”というエラーが発生します。
 
参考:
PostgreSQL を MySQL 互換にする(Open Tech Press)
シーケンス操作関数(PostgreSQL 8.1.0 日本語ドキュメント)