PostgreSQLのトランザクション制御でさっそくハマった２点

久しぶりにPostgreSQLの門に入ったので

前に触ったのは、8.1.2～8.1.6くらいだったと思います。その間、非エンジニア業もやってた（企画系および管理系）ので、完全に忘れてた。

なので、謙虚に入門したいと思います。

OracleやMySQLから移ってくるにあたって、嵌りどころ２つ

DDLにもトランザクション（Commit/Rollback）がある

なので、DDL叩いても、Commit打たないと他のセッションから認識されない。
ていうか、DDL叩くと「テーブルレベルの排他ロック（ACCESS EXCLUSIVEロック）」がかかって、Commit/Rollbackするまで、他セッションから参照すらできない。。。

試しにDDL投げてCommit打たないまま、他セッションからSELECT投げると、処理が返ってきません。
DDL投げたセッションでCommitを打った瞬間に、Selectの結果が返ってきます。

こんな感じで、ロックの状況も確認できます。
DDLを発行して、Commitの前にロック状況を見ると「AccessExclusiveLock」が発生しているのが見えますね。

testdb=> \echo :AUTOCOMMIT
off
testdb=> SELECT oid, datname,datdba FROM pg_database WHERE datname = 'testdb';
  oid  | datname | datdba
-------+---------+--------
 24581 | testdb  |  16389
(1 row)

testdb=> COMMIT;
COMMIT
testdb=> CREATE TABLE test_tbl1 (
testdb(>     col_1 integer Primary key,
testdb(>     col_2 text
testdb(> );
CREATE TABLE
testdb=> COMMIT;
COMMIT
testdb=> SELECT l.locktype,l.database,l.relation,c.relname,mode FROM pg_locks l  JOIN pg_class c ON l.relation = c.oid;
 locktype | database | relation |              relname              |      mode
----------+----------+----------+-----------------------------------+-----------------
 relation |    24581 |     3455 | pg_class_tblspc_relfilenode_index | AccessShareLock
 relation |    24581 |     2663 | pg_class_relname_nsp_index        | AccessShareLock
 relation |    24581 |     2662 | pg_class_oid_index                | AccessShareLock
 relation |    24581 |     1259 | pg_class                          | AccessShareLock
 relation |    24581 |    11577 | pg_locks                          | AccessShareLock
(5 rows)

testdb=> ALTER TABLE test_tbl1 ADD COLUMN col_3 integer;
ALTER TABLE
testdb=> SELECT l.locktype,l.database,l.relation,c.relname,mode FROM pg_locks l  JOIN pg_class c ON l.relation = c.oid;
 locktype | database | relation |              relname              |        mode
----------+----------+----------+-----------------------------------+---------------------
 relation |    24581 |     3455 | pg_class_tblspc_relfilenode_index | AccessShareLock
 relation |    24581 |     2663 | pg_class_relname_nsp_index        | AccessShareLock
 relation |    24581 |     2662 | pg_class_oid_index                | AccessShareLock
 relation |    24581 |     1259 | pg_class                          | AccessShareLock
 relation |    24581 |    11577 | pg_locks                          | AccessShareLock
 relation |    24581 |    36919 | test_tbl1                         | AccessExclusiveLock
(6 rows)

testdb=> COMMIT;
COMMIT
testdb=> SELECT l.locktype,l.database,l.relation,c.relname,mode FROM pg_locks l  JOIN pg_class c ON l.relation = c.oid;
 locktype | database | relation |              relname              |      mode
----------+----------+----------+-----------------------------------+-----------------
 relation |    24581 |     3455 | pg_class_tblspc_relfilenode_index | AccessShareLock
 relation |    24581 |     2663 | pg_class_relname_nsp_index        | AccessShareLock
 relation |    24581 |     2662 | pg_class_oid_index                | AccessShareLock
 relation |    24581 |     1259 | pg_class                          | AccessShareLock
 relation |    24581 |    11577 | pg_locks                          | AccessShareLock
(5 rows)

ドキュメントのこちらに書いてありますね。

ACCESS EXCLUSIVE
全てのモードのロック（ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、および ACCESS EXCLUSIVE）と競合します。このモードにより、その保持者以外にテーブルにアクセスするトランザクションがないことが保証されます。
DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL、（CONCURRENTLYなしの）REFRESH MATERIALIZED VIEWコマンドによって獲得されます。 ALTER TABLEの多くの形式もこのレベルでロックを獲得します。これはまた、明示的にモードを指定しないLOCK TABLE文のデフォルトのロックモードです。
13.3. 明示的ロック

DDLにトランザクション管理がない他のDBだと、なかなか考えられない落とし穴。

MS SQL ServerもDDLにトランザクションがある、って効いたことがあるけど、手元に試せる環境がないのでスルーします。

トランザクションの途中でエラーが起きると、以降、Commit/Rollbackするまで、トランザクション全体が無効

同じデータが間違って2回飛んできてしまうことがあり、重複はPKエラーを握りつぶしつつ、新規分だけまるごとINSERTかけたいケース、あると思います。

が、どうやらPostgreSQLはそれを許してくれないようで、トランザクション開始以降に投げたSQL、途中でエラーを起こすと、「トランザクション全体がエラー」というステータスになり、以降すべてのSQLがエラーとなって、Commitを打っても、先に実行して成功しているSQLも含めてロールバックされる、とのこと。

以下のように、トランザクション（暗黙での開始）の中で、一意制約違反となるINSERTを実行した後、エラーにならないINSERTを実行してCOMMITした場合、エラーになる前のSQLも含めてまるごとRollbackされているのがわかります。

testdb=> \echo :AUTOCOMMIT
off
testdb=> SELECT * FROM test_tbl1;
 col_1 | col_2 | col_3
-------+-------+-------
(0 rows)

testdb=> INSERT INTO test_tbl1 VALUES (1,'test 01');
INSERT 0 1
testdb=> COMMIT;
COMMIT
testdb=> SELECT * FROM test_tbl1;
 col_1 |  col_2  | col_3
-------+---------+-------
     1 | test 01 |
(1 row)

testdb=> INSERT INTO test_tbl1 VALUES (2,'test 02');
INSERT 0 1
testdb=> INSERT INTO test_tbl1 VALUES (2,'test 03');
ERROR:  duplicate key value violates unique constraint "test_tbl1_pkey"
DETAIL:  Key (col_1)=(2) already exists.
testdb=> INSERT INTO test_tbl1 VALUES (3,'test 03');
ERROR:  current transaction is aborted, commands ignored until end of transaction block
testdb=> COMMIT;
ROLLBACK
testdb=> SELECT * FROM test_tbl1;
 col_1 |  col_2  | col_3
-------+---------+-------
     1 | test 01 |
(1 row)

公式ドキュメントでは、ここでサラっと触れていますが、もっと大きく扱ってもいいんじゃないかな、と思います。