PostgreSQL トランザクションの隔離性

PostgreSQL のトランザクションで使用できる隔離性 isolation level は, read committed と, serializable の 2 種類です. これらは, それぞれ, 文単位での読み取り一貫性と, トランザクション単位での読み取り一貫性を保証しようとするものです. ¹

さて, 以下のようなテーブル m があります.

mydb=# select * from m;

 m_id | m_name | m_upd_time
------+--------+------------
    1 | foo    |
    2 | bar    |
    3 | baz    |
(3 rows)

ここに, 以下のようなタイミングで, 2 つのトランザクション, Trans1, Trans2 を実行します.

Time	Trans1	Trans2
1	select from m	-
2	-	update m
3	select from m	-

すると, それぞれの隔離性に対して, 以下の実行結果が得られます.

------- Read Committed Isolation Level
begin trans2
end trans2
begin trans1
begin select
trans1:bar
end select
begin trans2
end trans2
begin select
trans1:fizzbuzz
end select
end trans1
------- Serializable Isolation Level
begin trans2
end trans2
begin trans1
begin select
trans1:bar
end select
begin trans2
end trans2
begin select
trans1:bar
end select
end trans1

なお, 各隔離性の実行結果について, 先頭の Trans2 の実行は無視してください. テーブル値の初期化に使用しています.

以下は, 実験に使用したソースコード.

###
### pg-trans1.rb
###
require 'rubygems'
require 'pg'

class PgTrans
  def initialize(*conn)
    @conn = PGconn.open(*conn)
  end

  def x(sql, *rest)
    @conn.exec(sql, *rest)
  end

  private :x
end

class Trans1 < PgTrans
  def execute(is_serial = false)
    begin
      puts 'begin trans1'
      x 'begin'
      x 'set transaction isolation level serializable' if is_serial
      puts 'begin select'
      r = x 'select * from m where m_id = 2'
      r.each {|t| puts "trans1:#{t['m_name']}" }
      puts 'end select'
      sleep 0.2
      puts 'begin select'
      r = x 'select * from m where m_id = 2'
      r.each {|t| puts "trans1:#{t['m_name']}" }
      puts 'end select'
      x 'commit'
      puts 'end trans1'
    rescue
      x 'rollback'
      STDERR.puts "pg:#$!"
    end
  end
end

class Trans2 < PgTrans
  def execute(mname)
    begin
      puts 'begin trans2'
      x 'begin'
      x "update m set m_name = $1 where m_id = 2", [mname]
      x 'commit'
      puts 'end trans2'
    rescue
      x 'rollback'
      STDERR.puts "pg:#$!"
    end
  end
end

def go_trans(is_serial)
  Trans2.new(:dbname => 'mydb').execute('bar')
  threads = []
  threads.
    push(Thread.new() do
           Trans1.new(:dbname => 'mydb').execute(is_serial)
         end
         )
  threads.
    push(Thread.new() do
           Trans2.new(:dbname => 'mydb').execute('fizzbuzz')
         end
         )
  threads.each {|t| t.join }
end

puts '------- Read Committed Isolation Level'
go_trans(false)
puts '------- Serializable Isolation Level'
go_trans(true)

---

1. 完全に保証するわけではない. 13.2.2.1. Serializable Isolation versus True Serializability, PostgreSQL 8.4.2 Documentation

レガシーアプリケーション，だと...

O/R マッパーについて，改めて調べようと，"Hibernate in Action" (Bauer, 2005)¹ を拾い読みしていて，行き当たった記述です．

Java アプリケーションが他のレガシーアプリケーションとデータベースのアクセスを共有するケースは多い．この場合，トランザクションスコープキャッシュを超えるどんな種類のキャッシュも利用すべきでない．なぜなら，キャッシュシステムにはレガシーアプリケーションがいつ共有されたデータを更新したかについて検知する方法がないからだ．

(Bauer, 2005, p.222)

ここでいう，「レガシーアプリケーション」とは， Hibernate を使用しない，あらゆるアプリケーションであり， Hibernate を利用できない，あらゆる開発言語のことですよね．

FORTRAN， COBOL はもちろん， C/C++ も含むと．さらに， Perl, Python, Ruby, PHP であるとか， Haskell, Eralng といった，開発言語も含まれているわけです．

Java と ．NET に限定される，と言った方が早いですね．

NHibernate for .NET

この NHibernate とやらは， Java の Hibernate とキャッシュの同期をやるのですかね． .NET CLR 上で動作するとすると， Java VM とは別プロセスで動くはずで，そうすると，キャッシュの同期をやるには，プロセス間通信（クラスタリング）が必要になるはず．

2005 年に発行された本の内容ですので，今は違うのかもしれませんけど．でも，データベース側の変更を検知して，キャッシュのコントロールをしている，となると，それはそれで，アプリに無駄な複雑さを持ち込んでいる感じはあります．

キャッシュはデータベースへのアクセスを避けるために，次のような場合に試みられる．

● アプリケーションが識別子（主キー）によるルックアップを実行する．

● 永続化レイヤが関連を遅延して解決する．

クエリの結果をキャッシュすることも可能である．しかし，７章で論じるようにクエリ結果のキャッシュがパフォーマンスに与える影響はほとんどの場合わずかである．したがって，この機能の利用頻度は低いものである．

(Bauer, 2005, p.218)

O/R マッパーの発行する SQL のパフォーマンスが低いから，それを緩和するために，メモリへのキャッシュが必要，といっているようにしか読めませんね．

駄目な技術を何とか使えるように，あれこれと手立てを講じているように思えるのは気のせいか？

何か， RAID (Redundant Array of Inexpensive Disks) のパリティ方式，RAID2 -> RAID3 -> RAID4 -> RAID5 -> RAID6 を彷彿させますね．

---

1. Christian Bauer, Gavin King. "Hibernate in Action". Manning Publications, 2005. 倉橋 央, 勝嶌 和彦. "Hibernate イン アクション". ソフトバンク クリエイティブ， 2006.

PHP の超便利オブジェクト指向，および PHP 所感

サンプルコード：

<?php
class Foo {
}

$foo = new Foo();
$foo->bar = 'Hello World!!';
var_dump($foo);

実行結果：

object(Foo)#1 (1) {
  ["bar"]=>
  string(13) "Hello World!!"
}

な，なんだってー！！！

．．．

人気が急上昇した言語はグーグルの「Go」と「PHP」、メジャーなのは静的型付け言語。オランダのTIOBEが発表, Publickey

確かに，何か， PHP 使った開発案件の引き合いが増えてるんですよね．思うに，ユーザサイドでアプリをメンテナンスしたい，という意向が背景にあるのでしょう．「ホームページ」的なノリで．内製化ってやつですかね．

ただ，本格的な入力系アプリを，ホームページ的ノリで作ってしまうと，プロでもメンテナンスできなくなってしまう巨大スパゲッティが出現するのは必至であるわけでして．

入力系アプリは， MVC フレームワーク等を使って， データベースアプリの 3 層 アーキテクチャを採用する一方，単純な ｒｅａｄ ｏｎｌｙ のホームページ的画面は HTML ベタで作る，というのが良いのかな．エンドユーザも，複雑なアプリの部分は，どうやっても手が出せないでしょうし．

でも， PHP でロジック組むのは，やっぱりいやだ．プレゼン層だけに， PHP の利用を限定する単純でうまい方法ってないかな．

the N+1 Schema Problem

Lambda the Ultimate の以下の記事より．

Why Normalization Failed to Become the Ultimate Guide for Database Designers? , Lambda the Ultimate

以下，引用．日本語は拙訳．

In my experience, nothing seems to effect lines of code in an enterprise system more than schema design, both in the data layer and logic layer, and often an inverse relationship exists between the two; hence the use of object-relational mapping layers to consolidate inevitable problems where there will be The Many Forms of a Single Fact (Kent, 1988).

私の経験上，データベース・スキーマ設計以上に，企業システムのコード量を左右するものはないと思える．これは，データ層，ロジック層の両方についていえる．また，しばしば両者の関係は逆転する．そして，object-relational マッピング層（ O/R マッピング）の使用は， 単一事実の多くの形態の存在 (Kent, 1988) という，避けようのない問題を，確実にもたらすことになるだろう．

Mapping stabilizes the problem domain by labeling correspondances between all the possible unique structures.

O/R マッピングは，可能でありユニークな，あらゆるデータ構造群の関係，とされる問題領域を保ち続ける．¹

I refer to this among friends and coworkers as the N+1 Schema Problem, as there is generally 1 schema thought to be canonical, either extensionally or intensionally, and N other versions of that schema.

私と友人，同僚との間では，この問題を， the N+1 Schema Problem として言及している．すなわち，一般的に，数学的公理に則った（ canonical ）と考えられる， 1 つのスキーマがあるとき， - それが外延的であるか内包的であるかを問わず - 別にそのスキーマの N 個のバージョンが存在する．

・・・

O/R マッピングというのは，クラスのフィールドに対して，テーブル，およびクエリ結果のカラム（ relation の attribute ）を対応させるのが一般的だと思います．

O/R マッピングがこのように行なわれるとしますと，アプリケーション側からデータベースをみたとき，データベースは，クエリによって，新しいデータタイプを実行時に生み出していることになるわけでして．極端な話， SELECT 文のカラムリストが一つ違うだけで，異なるデータタイプが生み出されることになるんですよね．

マッピングがコンパイル時に行なわれるのだとすると，データベース・スキーマが持っている， relation データタイプの情報を，コンパイル前に，アプリケーション側のプログラミング言語にコピーすることになります．そして，そのコピーは大抵，改変を伴って行なわれます．つまり，アプリケーションごとに，データベース・データタイプの別バージョンコピーを持つことになります．

これが， object-relational 間のギャップがもたらす，より大きな，そして深刻な問題ではないかと考えます．

---

1. データベースの用語では，データ統合 data integration の問題．プログラミングの用語では， DRY (Don't repeat yourself )原則の問題かと思う．

詳細設計書の入力先は人間

Educators, generals, dieticians, psychologists, and parents program. Armies, students, and some societies are programmed.

Harold Abelson, Gerald Jay Sussman, Julie Sussman, "Structure and Interpretation of Computer Programs" Second Edition, (MIT Press, 1996)

詳細設計書の読み手は，あくまで人間であって，機械ではないわけですから，当然，曖昧さはあるでしょう．また，曖昧さがなければ意味がないとも言えます．

ひとまず詳細設計書には意味を与えるべきです。機械的実行が可能な表記法を定義し、それに則って記述するべきです。なんなら「詳細設計書を読み込んで実行するプログラム」があれば良いでしょう。

古き悪しき詳細設計書 - SiroKuro Page

事前条件，事後条件（，不変条件）を列挙して，機械に実行させる，というのは，宣言型プログラミング言語が目指すところであるわけでして．形式仕様記述言語¹というのは，そうしたものでしょう． そうした仕様記述から，機械で実行可能なプログラムを作り出すのであれば，詳細設計書を読んでコードを書く人間は不要になりますね．²

詳細設計書に擬似コードを載せる，ということにも，意味はあると思います．そのとおりに実装しろという指示ではなく，あくまで，仕様を理解するための一例とするなら．例えば，コード例がひとつも載っていない，言語仕様， API 仕様， アルゴリズム仕様を読んで理解できるのか，という話です．この場合，コード例が COBOL をベースにした擬似コードでも構わないと思います．

もちろん，仕様が正しいことを，設計者・プログラマの双方が確認できるように，事前条件，事後条件，不変条件を列挙したり，テストケース，テストデータといったものを用意する必要はあるでしょう．

詳細設計書の問題として語られているのは，実は詳細設計書の書き方の問題ではなく，工程が分断されていて，コミュニケーションが一方通行になってしまっている，という問題ではないでしょうか．

詳細設計書というものは，プログラマを「プログラムする」ためのドキュメントと考えます．そうしますと，詳細設計書を入力されたプログラマが，「コンパイル・エラー」をだす可能性は当然あるわけです．この場合，設計書を書いた人間にエラーを知らせて，設計書を修正させなければなりません．

詳細設計書をレビューするのに，最も適した人間は，それを読んでコードを書くプログラマです．プログラマがレビューし，設計書を書いた人間にフィードバックできないのであれば，その設計書の品質は，かなり低いものとならざるを得ないでしょうし，場合によっては無用の長物となるでしょう．

---

1. 例えば，VDM．　形式仕様記述から，C++ ，Java のコードを自動的に生成する機能がある．

2. SQL ，Prolog がそうであるように，宣言型プログラミング言語は，パフォーマンスの問題を無視できるには至っていない．従って，プログラマを「人間コンパイラ/オプティマイザ」として位置付けるのは，それはそれで一定の意味はある． RISC プロセッサと， C コンパイラが成し遂げたように，機械が自動生成するコードが，人間の書くコードにパフォーマンスで勝る，ということが，この抽象度のレベルで有り得るのかが問題．

SQL でヒストグラムを作成 - 訂正 -

よくみてみると, 各階級の結果が微妙におかしかったようです. 丸めが適当にすぎたのが原因でした. 以下のとおり, 謹んで訂正をさせていただきます.

コード

select
  x_from, x_to,
  count(*) as y_freq
from (
  select
    (cx - 1) * u_val as x_from,
    cx * u_val as x_to
  from (
    select
      u_val,
      ceil(o_total / u_val) as cx
    from (
      select
        ceil(max_val / breaks) as u_val
      from (
        select
          max(o_total) as max_val,
          1 + log(2, count(*)) as breaks
        from orders
      ) s0
    ) s1,
    orders
  ) s2
) s3
group by x_from, x_to
order by x_from

実行結果

 x_from | x_to | y_freq
--------+------+--------
      0 |   23 |   1435
     23 |   46 |   1698
     46 |   69 |   1649
     69 |   92 |   1667
     92 |  115 |   1686
    115 |  138 |   1663
    138 |  161 |   1650
    161 |  184 |   1676
    184 |  207 |   1754
    207 |  230 |   1707
    230 |  253 |   1700
    253 |  276 |   1672
    276 |  299 |   1631
    299 |  322 |   1719
    322 |  345 |   1641
    345 |  368 |    972
(16 rows)

前バージョンとの差分

--- hist_v_0.0.1.sql  2010-01-04 03:07:20.372303040 +0900
+++ hist_v_0.0.2.sql  2010-01-04 03:10:55.108940398 +0900
@@ -3,15 +3,15 @@
   count(*) as y_freq
 from (
   select
-    ceil((cx - 1) * u_val) as x_from,
-    ceil(cx * u_val) as x_to
+    (cx - 1) * u_val as x_from,
+    cx * u_val as x_to
   from (
     select
       u_val,
       ceil(o_total / u_val) as cx
     from (
       select
-        max_val / breaks as u_val
+        ceil(max_val / breaks) as u_val
       from (
         select
           max(o_total) as max_val,

解説の続き

PostgreSQL の width_bucket 関数ですが, これは, 範囲の上限・下限, 階級の数, 対象値を渡すと, その対象値の階級を返す関数ですね. ちょっと使い途のよくわからない関数です. これを調べていて間違いに気づいたわけですけど.

SQL でヒストグラムを作成

前提条件

select
version();

                                    version
--------------------------------------------------------------------------------
 PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.3.4, 32-bit
(1 row)

select
min(o_total),
max(o_total),
count(*)
from orders;

 min  |  max   | count
------+--------+-------
 1.11 | 359.80 | 25920
(1 row)

コード

select
  x_from, x_to,
  count(*) as y_freq
from (
  select
    ceil((cx - 1) * u_val) as x_from,
    ceil(cx * u_val) as x_to
  from (
    select
      u_val,
      ceil(o_total / u_val) as cx
    from (
      select
        max_val / breaks as u_val
      from (
        select
          max(o_total) as max_val,
          1 + log(2, count(*)) as breaks
        from orders
      ) s0
    ) s1,
    orders
  ) s2
) s3
group by x_from, x_to
order by x_from

実行結果

 x_from | x_to | y_freq
--------+------+--------
      0 |   23 |   1432
     23 |   46 |   1696
     46 |   69 |   1651
     69 |   92 |   1662
     92 |  115 |   1690
    115 |  138 |   1651
    138 |  161 |   1652
    161 |  184 |   1673
    184 |  207 |   1754
    207 |  230 |   1699
    230 |  253 |   1699
    253 |  276 |   1678
    276 |  299 |   1631
    299 |  322 |   1716
    322 |  345 |   1634
    345 |  368 |   1002
(16 rows)

解説

といっても, breaks を求めている, この式しかありません.

1 + log(2, count(*)) as breaks

PostgreSQL には, width_bucket という関数がありますね.

return the bucket to which operand would be assigned in an equidepth histogram with count buckets, in the range b1 to b2

Table 9-3. Mathematical Functions, PostgreSQL 8.4.2 Documentation

この関数は, 似たようなことをやっていそうですが, 動作は確認していません.

PostgreSQL で フィボナッチ数

使用したのは, PostgreSQL 8.4.2 です.

コード

with recursive fib(y1, y2) as (
    values (0, 1)
  union all
    select
      y2,
      y1 + y2 as y3
    from fib
)
select y1 from fib limit 20;

実行結果

  y1
------
    0
    1
    1
    2
    3
    5
    8
   13
   21
   34
   55
   89
  144
  233
  377
  610
  987
 1597
 2584
 4181
(20 rows)

わかったこと

正確には, 再帰ではないらしいです.

Note: Strictly speaking, this process is iteration not recursion, but RECURSIVE is the terminology chosen by the SQL standards committee.

7.8. WITH Queries, Chapter 7. Queries, PostgreSQL 8.4.2 Documentation

色々と試していて, "iteration not recursion" の意味がわかりました. つまり, with recursive query では, 前 1 回分の計算結果しか受け取ることができません. また, 再帰箇所も 1 箇所に限定されます.

この iteration を末尾再帰と同一視してよいかはわかりませんが, iteration が末尾再帰になるのは確かでしょう.

もともと, SQL という言語は, 「再帰が使えない」という点にこだわりがあるみたいでしたので(停止性を保証することに何か意味があるらしい), 再帰が導入されたらしいと知って少々意外に思っていましたが. かなり限定的な「再帰」のようです.

データベース上で使うものだと考えれば, これはこれで良いのでしょう.