ECPG PREPAREの小ネタ

# はじめに
![kuma.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/32626/7357e7b1-dd39-1c2c-5f99-e7514e94efcd.jpeg)
にゃーん。趣味でポスグレをやっている者だ。

この記事は[PostgreSQL アドベントカレンダー 2024](https://qiita.com/advent-calendar/2024/postgresql)の7日目の記事です。
昨日は[noborus](https://qiita.com/noborus)さんに書いていただきました。

前日の[PostgreSQL Conference Japan 2024](https://www.postgresql.jp/jpug-pgcon2024)（そして今日の[Open Source Conference 2024 Fukuoka](https://event.ospn.jp/osc2024-fukuoka/)でも）ECPGの話をしたのですが、今回のアドベントカレンダーもECPGの小ネタにしようかと思います。

# ECPGとは
ECPG(ecpg)とは、[埋め込みSQL用Cプリプロセッサ](https://www.postgresql.jp/document/16/html/app-ecpg.html)のことです。この機能は、PostgreSQLのかなり古い版（PostgreSQL 6.3～）から実装されているものです。

[埋め込みSQL](https://www.postgresql.jp/document/16/html/ecpg.html)というのは、非常にざっくりいうと、C言語等のソース内に特殊な記法（``EXEC SQL <SQL文>;``）でSQL文を埋め込み、C言語等のプログラムからPostgreSQLにアクセス可能にするものです。
以下にシンプルなC言語での埋め込みSQLの例を示します。

```c
#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はそのプリプロセスを行うためのツールです。

![ECPG-FLOW-1024.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/32626/d220051d-b0d7-90ea-9191-71933f134be7.jpeg)

# ネタ1：EXEC SQL PREPAREの重複
## PostgreSQLのPREPARE
PostgreSQLには``PREPARE``というSQL文があります。これは「準備文」と呼ばれています。``PREPARE``で準備文を作ったあとで``EXECUTE``で準備文を指定して実行します。（普通はパラメータを設定しますが、今回は簡易化のためにパラメータなしで実行しています）

```sql
myuser@mydb=> PREPARE stmt AS SELECT 1;
PREPARE
myuser@mydb=> EXECUTE stmt;
 ?column?
----------
        1
(1 row)
```

PREPAREは明示的に``DEALLOCATE``しないとセッション内で残り続けます。
この状態で、同じ名前の準備文を作ろうとすると重複エラーになります。

```sql
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プログラムがあります。

```sql
#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()で出力します。
```sh
$ ./sample
i_data=1
$
```

さて上記のプログラムをちょっとだけ修正します。
準備文を作成する箇所
```sql
    /* 準備文の作成 */
    strcpy(sql, "SELECT 1");
    EXEC SQL PREPARE stmt FROM :sql;
```
ここを
```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しようとするとエラーになると思いきや・・・

```sh
$ ./sample
i_data=2
$
```
しれっと２回目のEXEC SQL PREPAREはエラーにならず、2回目のPREPAREで設定した準備文がEXECUTEで実行されます。
どういうことなの・・・

## サーバログを確認
サーバログの出力設定として、
```sql
log_connections = on
log_disconnection = on
log_statements = 'all'
```
を設定して、上記のECPGプログラムを動かしてサーバログを確認してみます。（タイムスタンプ等のprefixは削除しています）

```sql
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](https://www.postgresql.jp/document/16/html/ecpg-sql-prepare.html)のページには上記の挙動の記載はありませんが、まあそういうものみたいですね。

## ecpglibの実装を見てみる
ここからはおまけ程度の話。なんとなく気になったのでソースをちら見してみました。

```sql
    /* 準備文の作成 */
    strcpy(sql, "SELECT 1");
    EXEC SQL PREPARE stmt FROM :sql;
```
という埋め込みSQL文は、ecpgを通してCする言語に展開されます。
展開された後の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内（サーバ側ではなくクライアント側）に準備文のリストを保持していて、それを探索しているようです。

```C
        /* 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：準備文は接続切断時に解放してくれる

最初のサンプルコードのうち、クリーンナップの部分に着目したネタです。

```sql
    /* クリーンアップ */
    EXEC SQL DEALLOCATE stmt;
    EXEC SQL DISCONNECT ALL;
```
作成した準備文は``DEALLOCATE``で解放するのが作法なのですが、どうやらECPGでは``EXEC SQL DISCONNECT``背景で、自動的にセッション内で作成した準備文を削除するっぽいです。

接続→準備文作成→実行→DEALLOCATE→DISCONNECTしたときのサーバログを見てみるとこんな感じになりますが、
```sql
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をサボっても
```sql
    /* クリーンアップ */
    // EXEC SQL DEALLOCATE stmt;
    EXEC SQL DISCONNECT ALL;
```
サーバログを見ると、ECPGライブラリ内で準備文の解放をサーバに指示しているっぽいですね。

```sql
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](https://www.pgbouncer.org/)（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](https://qiita.com/fujii_masao)さんの記事です。お楽しみに！