PostgreSQLのクエリプロトコルと起こりうる問題

Postgres Advent Calendar 2019の11日目。クエリプロトコルについておしごとで調べる機会があったため、にわか知識でさわりを紹介してみる。.NETのコードやライブラリを例に出すが、PostgreSQLの仕様に関するおはなしのはず。

アプリケーションがRDBMSと通信するとき、多くの場合、クライアントライブラリを使って通信を抽象化している。そのため、おそらくほとんどのソフトウェアエンジニアは日常的に通信のプロトコルを意識することはない。ただし、クライアントライブラリを実装する場合や、後に述べる特異なケースにおいて、プロトコルの仕様が重要になる。

簡易クエリと拡張クエリ

PostgresSQLはフロントエンド(クライアント)とバックエンド(サーバ)の通信に、メッセージベースのプロトコルを使う。このプロトコルには、通信の開始・終了や、データのやりとり(クエリ)、コピー、レプリケーションといった用途に応じて、複数のサブプロトコルが存在する。本記事ではそのうち、クエリ用のプロトコルに触れる。PostgreSQLのクエリプロトコルは、大きく簡易クエリ拡張クエリの2種類がある。

詳しい挙動の違いは公式ドキュメントにあるが、ものすごくざっくり書いてみる。簡易クエリは、パラメータをテキストとしてSQLの文字列に埋め込み、送信する。PostgreSQLは受け取ったSQLを実行する。それに対し、拡張クエリでは、クエリの解析・パラメータバインド・実行という一連の処理が、それぞれ別のメッセージとして送信される。クエリの解析結果は使い回すことができるため、例えば同一セッションにおいて*1同じSQLでパラメータだけ異なるクエリを大量に実行する場合、性能が向上する。機能的にはPREPAREと等価であり、実際内部的にもPREPARE/EXECUTEに相当する処理が実行されている。

各プロトコルの検証

クライアントライブラリによって、使っているプロトコルが異なる。どちらのプロトコルを使っているかを知るには、仕様・実装をみる、postgresのログをみる、パケットを覗くなどある。今回はPostgreSQLのログをみてみる。.NETのPostgreSQLクライアントライブラリであるNpgsqlは、メジャーバージョンが2のときに簡易クエリ、3以上では拡張クエリを使っている。比較にちょうどいいので、このライブラリを使って、それぞれのバージョンで同一のコードを実行し、PostgreSQLのログの差異を確認してみる。

using (var conn = new NpgsqlConnection(connnectionString))
{
    conn.Open();

    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT @test;";
        cmd.Parameters.AddWithValue("test", NpgsqlDbType.Integer, 100); // 型を指定してパラメータをセット
        cmd.ExecuteScalar();
    }
}

このコードをNpgsqlの旧バージョン新バージョンで実行する*2。postgresql.confにおいてログレベルを最大の log_min_messages = debug5 としておく。

旧バージョンにおいて、クエリの実行に関するログは以下。 @test のところに 100 が文字列として埋め込まれ、かつintegerにキャストされている。簡易クエリでは、パラメータをSQLに文字列として埋め込み、かつ型情報を使ってキャストされることがわかる。

2019-12-08 19:33:39 JST LOG: 実行 SELECT (('100'::integer))

続いて、新バージョンのNpgsqlにおいて、クエリの実行に関するログは以下。「解析」「バインド」「実行」と、分割されていることがわかる。

2019-12-08 18:52:26 JST DEBUG:  解析 <unnamed>: SELECT $1
(略)
2019-12-08 18:52:26 JST DEBUG:  バインド<unnamed>: <unnamed>
2019-12-08 18:52:26 JST LOG:  実行 <unnamed>: SELECT $1
2019-12-08 18:52:26 JST 詳細:  パラメータ: $1 = '100'

「解析」がPREPAREに相当する処理で、内部的には以下のSQLと等価になる。unnamedとなっているのは無名のPREPAREであることを意味するが、これは拡張クエリを介してのみ許可されている。

PREPARE unnamed (integer) AS (SELECT $1);
EXECUTE unnamed ('100');

簡易クエリではパラメータの型情報を使ってキャストしていたのに対し、拡張クエリではPREPARE文の宣言時に使われる。

影響をうける実例

クライアントライブラリがどちらのプロトコルを使っているかは、アプリケーションの視点からは隠蔽されている。そのため、日常的には意識する必要がないが、特異なケースのおいて問題になる。先述のNpgsqlを例に挙げて紹介する。上記のプロトコル変更は、リリースノートにおける以下の破壊的変更の要因になっている。

Parameter types have become more strict. Previous versions allowed to you pass arbitrary value types, such as writing CLR string to int columns, or anything that implemented IConvertible. Although some implicit conversions are still supported (e.g. long -> int, short -> int), some have been removed.
Npgsql 4.0 Release Notes | Npgsql Documentation

型が厳密になった、と書かれている。これがプロトコルの変更起因ということを理解していないと、何をどう対応していいかわからない。例えば以下のコードは旧バージョンでは動くが、新バージョンでは実行時エラーになる。

using (var conn = new NpgsqlConnection(connnectionString))
{
    conn.Open();

    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT @test;";
        cmd.Parameters.AddWithValue("test", DBNull.Value);
        cmd.ExecuteScalar(); // -> Npgsql.PostgresException: '42P18: could not determine data type of parameter $1'
    }
}

先述の例と異なり、NpgsqlDbTypeを指定していない。その場合、Npgsqlは値のオブジェクトから型を推定するのだが、値がDBNull.Valueなので、型を推定する情報がない。結果、Npgsqlはパラメータを「型情報なし」でPostgreSQLに送信する。PostgreSQLは型情報のないパラメータを受けとると、不明型( unknown )としてPREPAREを実行する。

PREPARE <unnamed> (unknown) AS (SELECT $1);

このときさらにPostgreSQLは、リテラル文字列に対するやり方と同じ方法で $1 の型を推定しようとする。しかし、$1がnullの場合、型が解決できずエラーになる。

おこりうる問題

上記のコードは SELECT NULL するだけであり無意味すぎるため、実際に困ることはない。もう少し入り組んだ例で、実際に起こる可能性のある問題について考えてみる。改めて、拡張クエリを使う場合、パラメータの名前・値・型情報をセットで渡す必要がある。型情報はPostgreSQLが管理するoidを渡すため、クライアントライブラリは事前にシステムカタログを参照し、型とoidのマップを取得する。このようなハンドシェイクのために、型情報なしのクエリを許容している。型情報があれば、 SELECT @test も問題なく実行できる。

逆を返せば、型情報が明示できない場合に問題がおこりうる。例えば、さらに別の外部ライブラリにより、パラメータの型指定が隠蔽されている場合がある。.NETにおいて、DapperというORMを例に挙げる。Dapperは、コネクションクラスを拡張し、in/outのパラメータをマッピングしてくれる。例えば、最初に紹介したNpgsqlCommandを使ったクエリ実行は、以下のようにシンプルに書け、値を取得できる。パラメータの型はDapperが内部で動的にセットしてくれる。すごく便利。

using (var conn = new NpgsqlConnection(connnectionString))
{
    conn.QueryFirstOrDefault<int>("SELECT @test;", new { test = 100 });
}

以下のコードを考える。 inputUserIds はコレクションが渡される想定だが、これがnullのとき、Npgsqlの新バージョン(拡張クエリを実装したバージョン)ではエラーになる。

using (var conn = new NpgsqlConnection(connnectionString))
{
    // ユーザIDが指定されているときは、該当するユーザの名前を取得する
    // 未指定(NULL)のときは、全ユーザの名前を取得する
    var sql = @"
SELECT user_name
FROM user_table
WHERE @targetUserids IS NULL OR user_id = any(@targetUserIds);
";

    var userNames = conn.Query<string>(sql, new {  targetUserIds = inputUserIds }); 
    // ... 
}

これはコレクションのパラメータ特有の問題である。パラメータの型がintやstringやDateTimeなどであれば、値がnullだとしても、Dapperが適切な型情報をNpgsqlに渡してくれる。コレクションの場合には、型情報を渡せない。この問題は、以下2点の競合により発生している。

  • Dapperは任意のRDBMSに接続するインターフェイス拡張を提供するライブラリである
  • PostgreSQLにおける配列型は、SQL標準ではない拡張仕様である

配列型はSQL標準にはなく、PostgreSQLの特徴的な機能でもある。そのためDapperは配列型に対応するCLRの型、すなわちコレクションを「知っている必要がない」のだが、例外的にコレクションのパラメータをサポートしている。上のコードでinputUserIdsがコレクションのオブジェクトである場合、Dapperは型情報として汎用的な DbType.Object をセットし、Npgsqlに渡す。Npgsqlは、受け取ったパラメータの型は不明(汎用型)だが、値がコレクションであることから、配列型の型情報を付与してPostgreSQLに送信する。PostgreSQLは配列型として処理できる。ただしinputUserIdsがnullの場合が問題になる。Npgsqlは値から型を推定する手段がなく、型情報なしで送らざるを得ない。そのため、 @targetUserIds IS NULL がPREPARE時に型が解決できずエラーとなる。

簡易クエリを実装した旧バージョンのNpgsqlであれば、PREPARE文は実行されず文字列として埋め込むだけなので 'null' IS NULL となり、問題なく実行される。それを知らずにNpgsqlのバージョンを移行すると、思わぬところで不具合が発生する。かもしれない。

ちなみに、.NET的には(というか一般的に)そもそもコレクションの実体がnullになる実装自体がDO NOT。「プロトコルの変更によって、動いていたものが動かなくなる」例として挙げた、あくまでも思考実験です。

X DO NOT return null values from collection properties or from methods returning collections. Return an empty collection or an empty array instead.
Guidelines for Collections - Framework Design Guidelines | Microsoft Docs

おこりうる小問題

SQLクライアントアプリケーションは人間がSQLを実行する用途のため、大量のユーザが利用するアプリケーションやバッチ処理と比べて、大量のクエリを撃ちまくる用途ではない。そのため、実装のシンプルさを優先して、簡易クエリを使って実装されがち。PostgreSQLに限らず、ユーザ向けアプリケーションと手元のSQLクライアントで挙動が違う場合には、プロトコルの違いを疑ってみてもいいかも。

まとめ

プロトコルはほとんど関係ない内容になってしまった。プロトコルの差異とソフトウェア間の仕様の競合を組み合わせて、コーナーケースを紹介してみた。たまにはこういう抽象化の隙間を考えるのもおもしろい。ソフトウェアは思ったとおりには動かないけど、書いたとおりに動く。

クライアントライブラリを実装してみて、性能検証などしてみようかとも思ったが、思いついたときには時間が足りなかった。また、Npgsql以外の実装、また他のRDBMSのプロトコルもいくつかみてみておもしろかった(設計思想の違いがみえる)が、そのあたりも別の機会に。来年からは、時間がないという言い訳をしない人生を歩んでいきたい。よいお年を。

*1:PREPARE文のライフタイム

*2:PostgreSQLのバージョンは9.6を利用