PostgreSQL
 クエリプロトコルの実証 @kabaome 第12回 PostgreSQLアンカンファレンス@オンライン

概要 https://github.com/kbth/PostgresMessageSerializer • プロトコルわかりたい • シリアライザをつくった • プロトコルを話してみた

デモ

背景

きっかけ: 事象 PostgreSQL PostgreSQL Pgpool-Ⅱ Application Client Library クライアントライブラリを
 バージョンアップ アプリケーションがハング。。

きっかけ: 原因 PostgreSQL PostgreSQL Pgpool-Ⅱ Application Client Library https://www.pgpool.net/docs/latest/en/html/release-3-6-13.html 利用するクエリプロトコルが
 変更されていた 拡張クエリ特有のバグがあった 簡易クエリ → 拡張クエリ

プロトコル わかりたい

公式ドキュメントを読む ? https://www.postgresql.jp/document/11/html/protocol-overview.html 整理されている◎ 文字でイメージしづらい△

サーバ or クライアントのコードを読む ? 実践で使われている◎ 高度に最適化されている△ Name Language Comments Website DBD::Pg Perl Perl DBI driver https://metacpan.org/release/DBD-Pg JDBC Java Type 4 JDBC driver https://jdbc.postgresql.org/ libpqxx C++ C++ interface http://pqxx.org/ node-postgres JavaScript Node.js driver https://node-postgres.com/ Npgsql .NET .NET data provider http://www.npgsql.org/ pgtcl Tcl https://github.com/flightaware/Pgtcl pgtclng Tcl https://sourceforge.net/projects/pgtclng/ pq Go Pure Go driver for Go's database/sql https://github.com/lib/pq psqlODBC ODBC ODBC driver https://odbc.postgresql.org/ psycopg Python DB API 2.0-compliant http://initd.org/psycopg/ https://www.postgresql.org/docs/12/external-interfaces.html

通信パケットを読む ? 具体的な通信がみえる◎ 読みづらい△ https://www.manniwood.com/2016_12_29/tcpdump_pg.html

シリアライザの実装

実証する対象 • プロトコルバージョン3.0（PostgreSQL 7.4〜） • クエリ用のサブプロトコル PostgreSQL Protocol Start-up Query Copy Termination Function Simple Query Extended Query

フォーマット Server Client Message A Message B Message C メッセージ種別 (byte) メッセージ長 (int32) ペイロード

シリアライズ メッセージ型のインスタンスから
 バイト列を生成したい ペイロードのフォーマットは
 メッセージごとに定義されている メッセージの種類を識別する

シリアライズ メッセージ種別 (byte) メッセージ長 (int32) ペイロード

デシリアライズ バイト列からメッセージ型の
 インスタンスを生成したい プロトコルに則って読み書きをする 例: 文字列は ’0’ で区切られる

デシリアライズ メッセージ種別 (byte) メッセージ長 (int32) ペイロード

シリアライザの実装：まとめ • クエリプロトコルの実証を目的とした • コードの品質・実用性は無視した

プロトコルの実証

準備 Server Client 2. テーブルを作る 1. ローカルPCで
 PostgreSQLを起動する

簡易クエリ (データなし) Server Client Query CommandComplete ReadyForQuery コマンドが完了したことを通知する サーバサイドがメッセージを受け入れる
 状態になったことを通知する SQLを送信する

簡易クエリ (データなし)

簡易クエリ (データなし): 出力の形式 サーバ（PostgreSQL）への送信 サーバからの返信 メッセージ名 メッセージのペイロードを
 解釈したプロパティ

簡易クエリ (データなし) 　I (73) => トランザクションブロック外（Idle） 　T (84) => トランザクションブロック内（Tran） 　E (69) => トランザクションブロック内（Error） 　挿入先テーブルのOID（ない場合は「0」）と、
 　挿入した行数

簡易クエリ (データあり) Server Client Query RowDescription DataRow DataRow CommandComplete ReadyForQuery 行の応答が返ることを示し、
 行のデータを解釈する情報を含む 行ごとのメッセージとして
 データを取得する

簡易クエリ (データあり)

簡易クエリ (データあり) フィールド (≒列・属性)の数 フィールド名 テーブルのOID 型のサイズ（int = 4バイト) フィールドの型のOID

簡易クエリ (データあり) 列の数 値のバイト列の長さ 　FormatCode = 0 : 文字列 (ASCII)
 　FieldTypeOid = 23 : int4 　から、「1」と解釈できる

簡易クエリの内部 Server Client Query RowDescription DataRow DataRow CommandComplete ReadyForQuery parse bind execute SQLを構文解析し、
 プリペアド文を生成する プリペアド文からポータルを生成し、 実行計画を生成する ポータルを実行し、
 リレーションを取得する

拡張クエリ Server Client Parse ParseComplete Bind BindComplete Describe Execute Sync ReadyForQuery DataRow DataRow CommandComplete RowDescription SQLを構文解析し、
 プリペアド文を生成する プリペアド文からポータルを生成し、 実行計画を生成する ポータルを実行し、
 リレーションを取得する サーバ・クライアントの状態を
 同期する

拡張クエリ

拡張クエリ 生成するステートメント名（使い回せる） パラメータの型指定（今回はなし）

拡張クエリ ParameterXxxはパラメータの設定（今回はなし） 生成するポータル名

拡張クエリ S (ステートメント) もしくは P (ポータル) を指定する

拡張クエリ 取得するデータに対してLimitをかける (≒カーソル) 実行するポータル名

簡易クエリと拡張クエリ 簡易クエリ 拡張クエリ 実行の流れ 簡易 詳細 パラメータバインド 利用できない 利用できる メリット 実装しやすい 性能・セキュリティ 利用シーン psqlやPgAdmin等のツール クライアントライブラリ

プロトコルの実証：まとめ • 簡易クエリと拡張クエリを試した • その他はブログを参照ください • トランザクション • パイプライン • パラメータ • エラー • etc… https://kyabatalian.hatenablog.com/entry/2020/03/15/194923

まとめ

まとめ • クエリプロトコルに対してシリアライザを実装した • 実用だけでなく実証もモチベーションになりうる • ｢抽象化のすきま｣ が問題解決につながる