adiary開発日誌

adiaryではすべてのテーブルに pkey というシリアル値（PRIMARY KEY）を設定しています。その扱いについて。

特にMySQLで特別な加工をせず、安全にシリアル値を取得する方法。

PostgreSQLの場合

PostgreSQLではそのままserial型というものがあり、

CREATE TABLE test(pkey SERIAL PRIMARY KEY, x INT);
INSERT INTO test(x) VALUES(10);

とすることで、pkeyをプライマリキーとして自動的に生成することができます。

PostgreSQLではシーケンス操作関数というものがあり、SERIAL型を定義すると自動的に作成されます。

例えば、現在の値を取得したければ

SELECT currval(pg_catalog.pg_get_serial_sequence('test', 'pkey'))

とします。pkeyに値を設定して INSERT したとき、シーケンス関数は自動的に再定義されないため次の関数で再設定する必要があります。

SELECT setval(pg_catalog.pg_get_serial_sequence('test', 'pkey'), (SELECT max(pkey) FROM test))

現在の値（最後に生成された値）を取得するには次のようにします。

SELECT currval(pg_catalog.pg_get_serial_sequence('test', 'pkey'), (SELECT max(pkey) FROM test))

同じセッション内でテーブル問わず最後に生成された値を取得する場合は次で済みます。

SELECT lastval()

安全に（他のセッションともかぶらない唯一無二な）次のシリアル値を取得するには、次のようにします。

SELECT nextval(pg_catalog.pg_get_serial_sequence('test', 'pkey'))

MySQLの場合

MySQLにも5.1以降（それより前から？）SERIAL型があります。

CREATE TABLE test(pkey SERIAL PRIMARY KEY, x INT);
INSERT INTO test(x) VALUES(10);

これによりpkeyに自動的にシリアル値を設定することができます。

• SERIALは「BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE」のエイリアスです。
• AUTO_INCREMENTは１つのテーブルに１カラムしか指定できない。
• AUTO_INCREMENTを指定したカラムに、例えばpkeyを指定してINSERTしても、AUTO_INCREMENTがよきにはからってくれる（PostgreSQLのように値を再設定する必要なし）。

現在の値（最後に生成された値）を取得するには次のようにします。

SELECT LAST_INSERT_ID();

ただしC API等では mysql_insert_id()というものがあるため、こちらを使用するほうが効率が良いようです。例えばPerl DBIならば $sth->{mysql_insertid}。

追記。

SELECT * FROM test WHERE pkey IS NULL;

以前はこの方法でも取得が可能でしたが、いつの間にか使えなくなっているようなので注意しましょう。

値を取得、値の変更

SHOW TABLE STATUS WHERE NAME = 'test';

で得られたテーブル情報の中からAuto_incrementカラムの値を参照します。得られるのは次に挿入される値です。*1

何かの都合で値を設定するときは次のようにします。

ALTER TABLE test AUTO_INCREMENT=1;

安全に次の値を取得

MySQLでもっとも厄介なのはこれです。auto_incrementには安全に次の値を得る方法が提供されていません。別にシーケンステーブルを作る方法などがありますが、面倒です。

INSERTでしか得られないならINSERTしてしまえばいいやという単純な方法です。

INSERT INTO test() VALUES();
DELETE FROM test WHERE pkey=LAST_INSERT_ID();
SELECT LAST_INSERT_ID();

NOT NULL制約等が付いているとINSERTが失敗するので、CREATE TABLE等であらかじめ制約を満たすDEFAULT値を設定しておく必要があります。MyISAMではトランザクションが使えないため、NOT NULLかつUNIQUE制約が付いていると、複数のプロセスが同時に"INSERT INTO test() VALUES();"を実行したとき一方が失敗します。

MySQLの場合はINSERTしてUPDATEした方がスマートなのはたしかなのですが、PostgreSQLではシーケンス型の次の値を安全に取得できるし、INSERTに成功してUPDATEに失敗した場合を考えるとややこしいので。

• PostgreSQLのトランザクションの仕様
• DBD::Pgの謎
• 謎の解析
  • 追記
• メモ

PostgreSQLのトランザクションの仕様

CREATE TABLE test(x INT UNIQUE);
INSERT INTO test (x) VALUES (1);
INSERT INTO test (x) VALUES (2);

としておきます。

ここで、

=> BEGIN;
=> INSERT INTO test (x) VALUES (3);
INSERT 0 1
=> INSERT INTO test (x) VALUES (1);
ERROR:  duplicate key value violates unique constraint "test_x_key"

とするとエラーになります。PostgreSQLはトランザクション中にエラーが起こると、以後何をしてもエラーになり受け付けなくなります。ためしに続けて色々発行してみても、

=> INSERT INTO test (x) VALUES (10);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
=> INSERT INTO test (x) VALUES (20);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

こんな感じです。COMMITすると

=> COMMIT;
ROLLBACK

このようにROLLBACKされます。これがPostgreSQLの仕様です。

DBD::Pgの謎

• 確認環境
  • PostgreSQL 8.3.7
  • DBD::Pg　Ver1.49

DBD::Pgの場合

01: $dbh->begin_work;
02: $dbh->do('INSERT INTO test (x) VALUES (10)');
03: $dbh->do('INSERT INTO test (x) VALUES (1);');
04: $dbh->do('INSERT INTO test (x) VALUES (20);');
05: $dbh->commit;

とすると、3行目でエラーが起こり、4行目の実行でも"ERROR: current transaction is aborted, commands ignored until end of transaction block"といわれます。

これを、DBIのprepareを使用して

01: $dbh->begin_work;
02: $dbh->prepare('INSERT INTO test (x) VALUES (?)')->execute(10);
03: $dbh->prepare('INSERT INTO test (x) VALUES (?)')->execute(1);
04: $dbh->prepare('INSERT INTO test (x) VALUES (?)')->execute(20);
05: $dbh->commit;

とすると、3行目でエラーが起っても、4行目の実行が反映されてしまいます。謎の挙動です。

謎の解析

このようにソースを改変してログを取ってみました。

open(my $fh, ">pg_log.txt");
$dbh->pg_server_trace($fh);
$dbh->begin_work;
$dbh->prepare('INSERT INTO test (x) VALUES (?)')->execute(10);
$dbh->prepare('INSERT INTO test (x) VALUES (?)')->execute(1);
$dbh->prepare('INSERT INTO test (x) VALUES (?)')->execute(20);
$dbh->pg_server_untrace();
close($fh);

このときサーバに対して次のようなコマンドが発行されていました。

=> BEGIN;
=> INSERT INTO test (x) VALUES (10);
=> INSERT INTO test (x) VALUES (1);
エラー : duplicate key value violates unique constraint "test_x_key"
=> ROLLBACK;
=> BEGIN;
=> INSERT INTO test (x) VALUES (20);
=> COMMIT;

どうりで最後のCOMMITが成功するはずです。取得した生ログも置いておきます。

これを、

01: $dbh->begin_work;
02: $dbh->prepare('INSERT INTO test (x) VALUES (10)')->execute();
03: $dbh->prepare('INSERT INTO test (x) VALUES ( 1)')->execute();
04: $dbh->prepare('INSERT INTO test (x) VALUES (20)')->execute();
05: $dbh->commit;

とすると再現しません。prepare中にUNIQUE制約をチェックして、勝手にrollbackしているようですが、原因がPostgreSQL側なのかDBD::Pg側なのかは絞り込めませんでした。

追記

DBD::Pgの実装仕様みたいです。トラックバックを参考にしてください（iakioさんに感謝）。

DBD::Pgを直すとすれば、プレースホルダが破棄されてもトランザクションが終了するまでDEALLOCATEするのを待つ、くらいでしょうが。

明示的なトランザクション内でのエラーとDEALLOCATE

ROLLBACKしていいから、DEALLOCATE後に空のトランザクションを begin して、失敗させてくれればそれで十分な予感。

メモ

• どちらの場合も $dbh->commit(); の戻り値は "1" で成功している。