はじめに
にゃーん。趣味でポスグレをやっている者だ。
この記事はPostgreSQL アドベントカレンダー 2024の7日目の記事です。
昨日はnoborusさんに書いていただきました。
前日のPostgreSQL Conference Japan 2024（そして今日のOpen Source Conference 2024 Fukuokaでも）ECPGの話をしたのですが、今回のアドベントカレンダーもECPGの小ネタにしようかと思います。
ECPGとは
ECPG(ecpg)とは、埋め込みSQL用Cプリプロセッサのことです。この機能は、PostgreSQLのかなり古い版（PostgreSQL 6.3～）から実装されているものです。
埋め込みSQLというのは、非常にざっくりいうと、C言語等のソース内に特殊な記法（EXEC SQL <SQL文>;）でSQL文を埋め込み、C言語等のプログラムからPostgreSQLにアクセス可能にするものです。
以下にシンプルなC言語での埋め込みSQLの例を示します。
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
EXEC SQL BEGIN DECLARE SECTION;
char* connection_string = "dbname=mydb user=myuser";
EXEC SQL END DECLARE SECTION;
int main() {
EXEC SQL CONNECT TO mydb USER myuser;
EXEC SQL BEGIN DECLARE SECTION;
int employee_id = 1;
char employee_name[50];
EXEC SQL END DECLARE SECTION;
EXEC SQL SELECT name INTO :employee_name FROM employees WHERE id = :employee_id;
if (sqlca.sqlcode < 0) {
printf("Failed to fetch employee name\n");
exit(1);
}
printf("Employee name: %s\n", employee_name);
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
return 0;
}
当然、そのままだとCコンパイラは通せないので、Cコンパイラにかける前に「プリプロセス」を行って、EXEC SQL <SQL文>;のような文をC言語の形式に展開したCソースファイルを生成します。
ecpgはそのプリプロセスを行うためのツールです。
ネタ1：EXEC SQL PREPAREの重複
PostgreSQLのPREPARE
PostgreSQLにはPREPAREというSQL文があります。これは「準備文」と呼ばれています。PREPAREで準備文を作ったあとでEXECUTEで準備文を指定して実行します。（普通はパラメータを設定しますが、今回は簡易化のためにパラメータなしで実行しています）
myuser@mydb=> PREPARE stmt AS SELECT 1;
PREPARE
myuser@mydb=> EXECUTE stmt;
?column?
----------
1
(1 row)
PREPAREは明示的にDEALLOCATEしないとセッション内で残り続けます。
この状態で、同じ名前の準備文を作ろうとすると重複エラーになります。
myuser@mydb=> PREPARE stmt AS SELECT 1;
ERROR: prepared statement "stmt" already exists
myuser@mydb=>
ECPGのPREPARE
さて、埋め込みSQLにもPREPAREのコマンドがあります。これはSQL EXEC PREPARE 準備文名 FROM SQL文あるいは、SQL EXEC PREPARE 準備文名 FROM :SQL文を設定したホスト変数という形式で記述します。
たとえば、以下のようなシンプルなECPGプログラムがあります。
#include <stdio.h>
#include <ecpglib.h>
#include <sqlca.h>
int main() {
EXEC SQL BEGIN DECLARE SECTION;
const char *conninfo= "mydb@localhost:17001";
char sql[1024];
int i_data;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR GOTO err;
/* 接続 */
EXEC SQL CONNECT TO :conninfo USER myuser;
/* 準備文の作成 */
strcpy(sql, "SELECT 1");
EXEC SQL PREPARE stmt FROM :sql;
/* 準備文の実行 */
EXEC SQL EXECUTE stmt INTO :i_data;
printf("i_data=%d\n", i_data);
/* クリーンアップ */
EXEC SQL DEALLOCATE stmt;
EXEC SQL DISCONNECT ALL;
return 0;
err:
/* SQLERROR発生時 */
printf("SQLエラー: (%s) %s\n", sqlca.sqlstate, sqlca.sqlerrm.sqlerrmc);
return -1;
}
これを実行すると、（わざわざPREPAREする必要はないけど）SELECT 1を実行し、その結果をホスト変数i_dataに格納します。そして、i_dataをprintf()で出力します。
$ ./sample
i_data=1
$
さて上記のプログラムをちょっとだけ修正します。
準備文を作成する箇所
/* 準備文の作成 */
strcpy(sql, "SELECT 1");
EXEC SQL PREPARE stmt FROM :sql;
ここを
/* 準備文の作成 */
strcpy(sql, "SELECT 1");
EXEC SQL PREPARE stmt FROM :sql;
strcpy(sql, "SELECT 2");
EXEC SQL PREPARE stmt FROM :sql;
に修正します。
修正後のコードでは、同じstmtという準備文を２回作成しようとします。
修正後のECPGプログラムをビルドして実行すると、最初のPostgreSQLのPREPAREのところで説明したように、同じ準備文を（DEALLOCATEせず）PREPAREしようとするとエラーになると思いきや・・・
$ ./sample
i_data=2
$
しれっと２回目のEXEC SQL PREPAREはエラーにならず、2回目のPREPAREで設定した準備文がEXECUTEで実行されます。
どういうことなの・・・
サーバログを確認
サーバログの出力設定として、
log_connections = on
log_disconnection = on
log_statements = 'all'
を設定して、上記のECPGプログラムを動かしてサーバログを確認してみます。（タイムスタンプ等のprefixは削除しています）
LOG: connection received: host=127.0.0.1 port=59934
LOG: connection authenticated: user="myuser" method=trust (/data/pgdata/17-ecpg/pg_hba.conf:119)
LOG: connection authorized: user=myuser database=mydb
LOG: statement: deallocate "stmt"
LOG: statement: begin transaction
LOG: execute stmt: SELECT 2
LOG: statement: deallocate "stmt"
LOG: disconnection: session time: 0:00:00.003 user=myuser database=mydb host=127.0.0.1 port=59934
どうやら、2回目のEXEC SQL PREPAREの実行時に既に存在している準備文があったら、それをdeallocateして、再度同じ名前の準備文を作成しているようですね。
埋め込みSQLのPREPAREのページには上記の挙動の記載はありませんが、まあそういうものみたいですね。
ecpglibの実装を見てみる
ここからはおまけ程度の話。なんとなく気になったのでソースをちら見してみました。
/* 準備文の作成 */
strcpy(sql, "SELECT 1");
EXEC SQL PREPARE stmt FROM :sql;
という埋め込みSQL文は、ecpgを通してCする言語に展開されます。
展開された後のCソースはこんな感じ。
/* 準備文の作成 */
strcpy(sql, "SELECT 1");
{ ECPGprepare(__LINE__, NULL, 0, "stmt", sql);
#line 19 "sample.pgc"
C言語上は、ECPGprepare()という関数に展開されます。
この関数は、ecpglibというECPGの機能を実行するライブラリ内で実装されています。
で、ecpglibのソース（src/interfaces/ecpg/ecpglib/prepare.cから、ECPGprepare()をつらつらと読んでみると、その中でecpg_find_prepared_statementという関数を呼び出しています。その関数内では、ecpglib内（サーバ側ではなくクライアント側）に準備文のリストを保持していて、それを探索しているようです。
/* check if we already have prepared this statement */
this = ecpg_find_prepared_statement(name, con, &prev);
if (this && !deallocate_one(lineno, ECPG_COMPAT_PGSQL, con, prev, this))
return false;
そしてecpg_find_prepared_statementの結果がNULLでなければ、既存の準備文(this)をdeallocate_one()という関数に渡して、その関数内でSQL文のdeallocateを実行しているっぽいかな・・・。
（なので、同じ名前の準備文をEXEC SQL PREPAREしても一度DEALLOCATEしちゃっているからエラーにならないようです）
ネタ2：準備文は接続切断時に解放してくれる
最初のサンプルコードのうち、クリーンナップの部分に着目したネタです。
/* クリーンアップ */
EXEC SQL DEALLOCATE stmt;
EXEC SQL DISCONNECT ALL;
作成した準備文はDEALLOCATEで解放するのが作法なのですが、どうやらECPGではEXEC SQL DISCONNECT背景で、自動的にセッション内で作成した準備文を削除するっぽいです。
接続→準備文作成→実行→DEALLOCATE→DISCONNECTしたときのサーバログを見てみるとこんな感じになりますが、
LOG: connection received: host=127.0.0.1 port=49144
LOG: connection authenticated: user="myuser" method=trust (/data/pgdata/17-ecpg/pg_hba.conf:119)
LOG: connection authorized: user=myuser database=mydb
LOG: statement: begin transaction
LOG: execute stmt: SELECT 1
LOG: statement: deallocate "stmt"
LOG: disconnection: session time: 0:00:00.003 user=myuser database=mydb host=127.0.0.1 port=49144
以下のようにDEALLOCATEをサボっても
/* クリーンアップ */
// EXEC SQL DEALLOCATE stmt;
EXEC SQL DISCONNECT ALL;
サーバログを見ると、ECPGライブラリ内で準備文の解放をサーバに指示しているっぽいですね。
LOG: connection received: host=127.0.0.1 port=49150
LOG: connection authenticated: user="myuser" method=trust (/data/pgdata/17-ecpg/pg_hba.conf:119)
LOG: connection authorized: user=myuser database=mydb
LOG: statement: begin transaction
LOG: execute stmt: SELECT 1
LOG: statement: deallocate "stmt"
LOG: disconnection: session time: 0:00:00.003 user=myuser database=mydb host=127.0.0.1 port=49150
まあ、作法として準備文が不要になった時点で、きちんとEXEC SQL DEALLOCATEをは実行すべきだとは思います。
ネタ3：EXEC SQL PREPAREで重複エラーが起きる！？
ネタ1で説明したように、普通は埋め込みSQLのEXEC SQL PREPAREの背景で重複エラーが起きることはないように見えます。また、ネタ2でも書いているように、ECPGの準備文は勝手に削除されるっぽいので、ますます重複エラーが起きることはなさそうに思えます。
しかし・・・ある条件が重なると、ECPG SQL PREPAREでステートメントの重複エラーが起きます。
どういうことなの・・・
pgbouncerとの合わせ技（？）
これが発生するケースは、pgbouncer（PostgreSQL用の軽量コネクションプール。詳細は割愛）を使っているときです。
以下のような手順でこれは再現できます。
① pgbouncerをpool_mode = transactionの設定で起動します。
② pgbouncer経由でpsqlを起動します。そのセッション内でトランザクションを開始（BEGIN）して準備文（例：stmt）を生成します。
③ psqlを終了します。
④ すぐにECPGアプリケーションを起動します。
⑤ ECPGアプリケーション内でstmtという名前の準備文をPREPAREしようとすると、準備文の重複エラー（SQLSTATE=42P05）が発生します。
どうしてこんなことになるのかというと、
- ②の時点で、PostgreSQLサーバオブジェクトとして、stmtという準備文が作成されます。
- ③の時点ではまだ、セッションはpgbouncerのプールの中に残っています。また、セッション内で明示的に
DEALLOCATEしていないので、stmtという準備文も残っています。 - ④のECPGアプリケーションは新たに接続を生成せずに、pgbouncerプール内の接続を使います。
- ④で取得した接続内には、stmt準備文が残っています。
- ⑤の
EXEC SQL PREPAREの実行時には、ECPGアプリケーション（ecpglib内）の準備文リストは空なので、stmtの解放動作は行わず、そのままサーバにstmtという名前の準備文の作成を要求します。 - しかし、サーバ内には既にstmtという準備文が存在するために、準備文の重複エラー（SQLSTATE=42P05）が発生します。
なお、この問題は、③のpsql終了前に、きちんとDEALLOCATEを実行して準備文を解放していれば発生しません。
pgbouncerのようなプーリングソフトウェアは実案件でも良く使うと思いますが、解放忘れしていると、こういう問題が発生する可能性もあります。
使い終わった準備文は明示的にDEALLOCATEしましょうね。
おわりに
枯れた技術と思われているECPGも改めて使ってみると、新たな発見があるものですね。
明日はfujii_masaoさんの記事です。お楽しみに！