JDBCドライバとバックエンドのやりとり。フロントエンド/バックエンドプロトコル

追記：キャプチャの結果が間違っていた（Row descriptionとData rowを混同していた）ので直した。
追記：JDBCドライバのログ出力も載せた。

PostgreSQLのフロントエンド/バックエンドプロトコルを、JDBCを使うプログラムを使って確認してみた。通信のキャプチャにはWiresharkを使った。

使ったJavaプログラム

使ったプログラムは以下のようなもの。1つのテーブル（branches）に対しSELECT文を実行する。よくあるケースで試したかったので、autoCommitはオフにした。branchesテーブルには11件のデータが入っている。ResultSet#nextを繰り返している途中でデータアクセスが発生するようにfetchSizeは6とし、11件より小さくした。

public class PostgreSqlTest {

    Connection conn;

    @Before
    public void before() throws Exception {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://10.0.1.87/test";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "postgres");
        conn = DriverManager.getConnection(url, props);
        conn.setAutoCommit(false);
    }

    @After
    public void after() throws Exception {
        if (conn != null) {
            conn.rollback();
            conn.setAutoCommit(true);
            conn.close();
        }
    }

    @Test
    public void test() throws Exception {
        PreparedStatement ps =
            conn.prepareStatement("select bid from branches");
        try {
            ps.setFetchSize(6);
            ResultSet rs = ps.executeQuery();
            try {
                while (rs.next()) {
                    System.out.println(rs.getString(1));
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.close();
        }
    }
}

やりとりされたメッセージ

あるメソッドを呼び出したときにどんなメッセージが送られたかを示す。ここでは、メッセージの種類だけを示し、実際に送られたデータは示さない。メッセージの詳細はドキュメントの45.4. メッセージの書式にある。

1.DriverManager.getConnection()したとき

フロントエンド（JDBCドライバ）はバックエンド（PostgreSQL）に Startup message を送る。

Type: Startup message

バックエンドはフロントエンドに次のメッセージを返す。

Type: Authentication request
Type: Parameter status
Type: Parameter status
Type: Parameter status
Type: Parameter status
Type: Parameter status
Type: Parameter status
Type: Parameter status
Type: Parameter status
Type: Parameter status
Type: Backend key data
Type: Ready for query

メッセージはまとめられて返される。Backend key dataは、この接続専用のキャンセルキーを送っている。

2.PreparedStatement#executeQuery()したとき

フロントエンドはバックエンドに次のメッセージを送る。

Type: Parse
Type: Bind
Type: Execute
Type: Parse
Type: Bind
Type: Describe
Type: Execute
Type: Sync

いくつかのメッセージがまとめられて一度の通信で送られる。
最初のParse、Bind、ExecuteはBEGINステートメントに対応する。BEGINステートメントはautoCommitが無効のときに自動で送られる。2番目のParse、Bind、ExecuteはSELECTステートメントに対応するもの。Describeの必要性についてはいまいち理解しきれていないと思ったけど、フィールドのメタデータの要求っぽい（返されるデータはResultSetMetaDataや型の判別で使われる）。そのあとにSyncでメッセージを送信。

バックエンドはフロントエンドに次のメッセージを返す。

Type: Parse completion
Type: Bind completion
Type: Command completion
Type: Parse completion
Type: Bind completion
Type: Row description
Type: Data row
Type: Data row
Type: Data row
Type: Data row
Type: Data row
Type: Data row
Type: Portal suspended
Type: Ready for query

戻りのメッセージもまとめられて返される。最初のParse completion、Bind completion、Command completionはBEGINステートメントに対応するもの。2番目のParse completion、Bind completionはSELECTステートメントに対応。Row descriptionはDescribeで要求したメタデータ（フィールドの名前や型情報）。次にData rowが6件。この6件は行の数。fetchSizeに6を指定しているので6件が返る。Portal suspendedでポータルが一時中断されたことがわかる（でも、このメッセージを送るだけでサーバ側の処理はsuspendedしようがしまいが変わりないように見えた）。Portal suspendedならば、JDBCドライバはまだポータルを閉じない。

3.7回目のResultSet#next()をしたとき

フロントエンドはバックエンドに次のメッセージを送る。

Type: Execute
Type: Sync

Executeを再度実行し、次のデータを要求している。

バックエンドはフロントエンドに次のメッセージを返す。

Type: Data row
Type: Data row
Type: Data row
Type: Data row
Type: Data row
Type: Command completion
Type: Ready for query

残りの5件が返り、Command completionでSELECTステートメントが完了したことを示す。Portal suspendedではなくCommand completionなので、JDBCドライバはポータルを閉じる。

4.Connection#rollback()をしたとき

フロントエンドはバックエンドに次のメッセージを送る。

Type: Close
Type: Close
Type: Parse
Type: Bind
Type: Execute
Type: Sync

1番目のCloseはステートメントのクローズ。2番目のCloseはポータルのクローズ。Parse、Bind、ExecuteはROLLBACKステートメントに対するもの。

Type: Close completion
Type: Close completion
Type: Parse completion
Type: Bind completion
Type: Command completion
Type: Ready for query

1番目のClose completionはステートメントのクローズ。2番目のClose completionはポータルのクローズ。Parse completion、Bind completion、Command completionはROLLBACKステートメントに対応するもの

5.Connection#close()をしたとき

フロントエンドはバックエンドに次のメッセージを送る。

Type: Termination

接続を閉じる。

宿題 & 疑問

昨日の宿題

Executeで途中でタプルを戻す場合、どこまで読んでおくのか？
- fetchSizeがあればその指定された行数に達するまでに読んだページの全タプルまで。次のResultSet#next()で新しいページを読むことになったとしても、一旦フロントエンドにデータを返す時点では先のページまでは読まない。

スクロール可能なResultSetを使った場合は、全行をクライアント側に返しているかも？
- 返している。スクロール可能なResultSetを使った場合は、fetchSizeは無視され全件フロントエンド（クライアント側）に返される。これまで、サーバ側でデータを保持して実現していると思っていたよ。

疑問

データが複数ページにまたがっている場合、次のResultSet#next()で次のページを読む前に別のトランザクションによってデータが変えられたどうなる？文レベルの読み取り一貫性があるのなら、書き換わる前のデータが読めるはず？MVCCのスナップショットで実現？スナップショットってどんなデータ？

キャプチャするより実はJDBCドライバのログ機能を使ったほうがわかりやすかったり。。。

ログレベルをDEBUGにしておく。

org.postgresql.Driver.setLogLevel(org.postgresql.Driver.DEBUG);

すると次のようにコンソールに出力される。FEは「Front End」、BEは「Back End」のこと。

10:36:40.218 (1) PostgreSQL 8.3 JDBC3 with SSL (build 603)
10:36:40.234 (1) Trying to establish a protocol version 3 connection to 192.168.100.2:5432
10:36:40.234 (1)  FE=> StartupPacket(user=postgres, database=test, client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2)
10:36:40.250 (1)  <=BE AuthenticationOk
10:36:40.265 (1)  <=BE ParameterStatus(client_encoding = UNICODE)
10:36:40.265 (1)  <=BE ParameterStatus(DateStyle = ISO, YMD)
10:36:40.265 (1)  <=BE ParameterStatus(integer_datetimes = off)
10:36:40.265 (1)  <=BE ParameterStatus(is_superuser = on)
10:36:40.265 (1)  <=BE ParameterStatus(server_encoding = UTF8)
10:36:40.265 (1)  <=BE ParameterStatus(server_version = 8.3.5)
10:36:40.265 (1)  <=BE ParameterStatus(session_authorization = postgres)
10:36:40.265 (1)  <=BE ParameterStatus(standard_conforming_strings = off)
10:36:40.265 (1)  <=BE ParameterStatus(TimeZone = US/Eastern)
10:36:40.265 (1)  <=BE BackendKeyData(pid=3075,ckey=1822665832)
10:36:40.265 (1)  <=BE ReadyForQuery(I)
10:36:40.265 (1)     compatible = 8.3
10:36:40.265 (1)     loglevel = 2
10:36:40.265 (1)     prepare threshold = 5
getConnection returning driver[className=org.postgresql.Driver,org.postgresql.Driver@55571e]
10:36:40.296 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1855af5, maxRows=0, fetchSize=6, flags=9
10:36:40.312 (1)  FE=> Parse(stmt=S_1,query="BEGIN",oids={})
10:36:40.312 (1)  FE=> Bind(stmt=S_1,portal=null)
10:36:40.312 (1)  FE=> Execute(portal=null,limit=0)
10:36:40.312 (1)  FE=> Parse(stmt=S_2,query="select bid from branches",oids={})
10:36:40.312 (1)  FE=> Bind(stmt=S_2,portal=C_3)
10:36:40.312 (1)  FE=> Describe(portal=C_3)
10:36:40.312 (1)  FE=> Execute(portal=C_3,limit=6)
10:36:40.312 (1)  FE=> Sync
10:36:40.312 (1)  <=BE ParseComplete [S_1]
10:36:40.312 (1)  <=BE BindComplete [null]
10:36:40.312 (1)  <=BE CommandStatus(BEGIN)
10:36:40.312 (1)  <=BE ParseComplete [S_2]
10:36:40.312 (1)  <=BE BindComplete [C_3]
10:36:40.312 (1)  <=BE RowDescription(1)
10:36:40.312 (1)  <=BE DataRow
10:36:40.312 (1)  <=BE DataRow
10:36:40.312 (1)  <=BE DataRow
10:36:40.312 (1)  <=BE DataRow
10:36:40.312 (1)  <=BE DataRow
10:36:40.312 (1)  <=BE DataRow
10:36:40.312 (1)  <=BE PortalSuspended
10:36:40.328 (1)  <=BE ReadyForQuery(T)
10:36:40.328 (1)  FE=> Execute(portal=C_3,limit=6)
10:36:40.328 (1)  FE=> Sync
10:36:40.328 (1)  <=BE DataRow
10:36:40.328 (1)  <=BE DataRow
10:36:40.328 (1)  <=BE DataRow
10:36:40.328 (1)  <=BE DataRow
10:36:40.328 (1)  <=BE DataRow
10:36:40.328 (1)  <=BE CommandStatus(SELECT)
10:36:40.328 (1)  <=BE ReadyForQuery(T)
10:36:40.343 (1) simple execute, handler=org.postgresql.jdbc2.AbstractJdbc2Connection$TransactionCommandHandler@eee36c, maxRows=0, fetchSize=0, flags=22
10:36:40.343 (1)  FE=> CloseStatement(S_2)
10:36:40.343 (1)  FE=> ClosePortal(C_3)
10:36:40.343 (1)  FE=> Parse(stmt=S_4,query="ROLLBACK",oids={})
10:36:40.343 (1)  FE=> Bind(stmt=S_4,portal=null)
10:36:40.343 (1)  FE=> Execute(portal=null,limit=1)
10:36:40.343 (1)  FE=> Sync
10:36:40.343 (1)  <=BE CloseComplete
10:36:40.343 (1)  <=BE CloseComplete
10:36:40.343 (1)  <=BE ParseComplete [S_4]
10:36:40.343 (1)  <=BE BindComplete [null]
10:36:40.343 (1)  <=BE CommandStatus(ROLLBACK)
10:36:40.343 (1)  <=BE ReadyForQuery(I)
10:36:40.343 (1)  FE=> Terminate