# PgBouncer のトランザクションプーリングで壊れるものと、それぞれの直し方 トランザクションプーラーの背後で「prepared statement already exists」が出る理由、設定がリクエスト間で漏れる理由、アドバイザリロックが何もロックしない理由を PgBouncer 1.24 と PostgreSQL 17 で再現し、それぞれの修正方法と、代わりにダイレクト接続を使うべき場面の判断基準を示します。 **PgBouncer をトランザクションモードで Postgres の前に置けば、数千の短命なクライアントが数十本のサーバー接続を共有できます。その代償として、トランザクションとトランザクションの間では「自分の接続」という概念が意味を持たなくなります。`prepared statement "s0" already exists` のようなエラーはここから生まれます。別の誰かのリクエストに現れる `SET` や、2 つのワーカーが両方とも保持しているアドバイザリロックも同じです。この記事では、Relyt One のプール接続文字列と同じ構成（トランザクションモード、Postgres 17）にしたローカルの PgBouncer 1.24.1 と PostgreSQL 17.11 でこれらをそれぞれ再現し、それぞれの修正方法を示し、最後にプーラーを使わないほうがよい場面の判断基準を示します。以下のコマンドと出力はすべて、このローカル環境で実行したものです。** ## 要点 トランザクションモードの PgBouncer は、クライアントにサーバー接続を 1 トランザクションの間だけ貸し出し、`COMMIT` または `ROLLBACK` の時点で回収します。明示的なトランザクションの外にあるステートメントは、それ自体が 1 つのトランザクションです。サーバー接続上に 1 トランザクションより長く残るものは、次のクライアントに漏れるか、次のトランザクションでは存在しないかのどちらかになります。[PgBouncer の機能表](https://www.pgbouncer.org/features.html)、設定リファレンス、そして私たちの検証環境から、影響を受けるものを挙げます。 | 使っているもの | 何が起きるか | 修正方法 | | ---------------------------------------------- | --------------------------------------- | ------------------------------------------------------------------------------------ | | `SET statement_timeout`、`SET search_path`、… | 設定がサーバー接続に残り、次にその接続を受け取ったクライアントに適用される | トランザクション内で `SET LOCAL` または `set_config(…, true)` を使う。デフォルト値には `ALTER ROLE … SET` を使う | | SQL の `PREPARE` / `EXECUTE` | `already exists` / `does not exist` エラー | プーラー経由では SQL レベルのプリペアドステートメントを使わない | | ドライバーの名前付きプリペアドステートメント | ドライバーから同じエラーが出る | ドライバー側で無効にするか、`max_prepared_statements` を設定したプーラーを使う | | `pg_advisory_lock()` | 2 つのクライアントが同じロックを両方とも「保持」できてしまう | トランザクション内で `pg_advisory_xact_lock()` を使う | | 接続文字列の `options=-c …` | 接続が拒否される | `ALTER ROLE … SET`、またはトランザクションごとの `SET LOCAL` を使う | | `LISTEN`、`WITH HOLD` カーソル、トランザクションより長く残る一時テーブル | 信頼できない、または誤った結果 | ダイレクト接続を使う | この記事の残りでは、各行が実際に起きる様子を示します。 ## トランザクションプーリングが接続を割り当てる仕組み ![3 つのクライアントが PgBouncer の 1 本のサーバー接続を共有し、それぞれ 1 トランザクションの間だけその接続を受け取る。接続に残されたセッション状態は次のクライアントから見える](/blog/assets/b2c2c7f734f5e598.svg) [PgBouncer の設定リファレンス](https://www.pgbouncer.org/config.html)にある 2 つの点が、以下の内容のほとんどを説明します。 - **クライアント間で何もリセットされません。** `server_reset_query`（デフォルトは `DISCARD ALL`）は、トランザクションプーリングでは「使用されません」。トランザクションがサーバー接続に何かを残すと、それは次のクライアントに引き継がれます。 - **追跡される設定とされない設定があります。** PgBouncer は、Postgres がクライアントに報告する少数のパラメーター（1.24 では `client_encoding`、`DateStyle`、`IntervalStyle`、`TimeZone`、`standard_conforming_strings`、`application_name`）についてクライアントごとの値を記憶し、そのクライアントにサーバー接続を渡すときに復元します。機能表では、それ以外の `SET` はすべてトランザクションプーリングと非互換とされています。変更はサーバー接続に残り、次のクライアントに渡ります。 ## 壊して試せる検証環境 以下はすべて、Docker 上のローカルの Postgres 17.11 と PgBouncer 1.24.1 に対して実行します。PgBouncer のイメージはコミュニティがメンテナンスしている `edoburu/pgbouncer` です。プーラーの設定は Relyt One のプーラーと同じくトランザクションモードで、プリペアドステートメントの追跡はオフのままですが、プールのサーバー接続は **1 本** にしています。サーバー接続が 1 本だけなら、すべてのクライアントが同じ接続に割り当てられるので、各障害がたまにではなく毎回起きます。本番環境でプールに数十本のサーバー接続があると、同じ障害が断続的に起きます。デバッグが難しいのはまさにそのためです。 ```ini ; pgbouncer.ini [databases] * = host=pg port=5432 [pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction default_pool_size = 1 max_prepared_statements = 0 ignore_startup_parameters = extra_float_digits ``` ```bash echo '"app" "secret"' > userlist.txt docker network create pgblab docker run -d --name pg --network pgblab \ -e POSTGRES_USER=app -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=app \ postgres:17.11-alpine docker run -d --name pgbouncer --network pgblab -p 6432:6432 \ -v "$PWD/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro" \ -v "$PWD/userlist.txt:/etc/pgbouncer/userlist.txt:ro" \ edoburu/pgbouncer:v1.24.1-p1 export PGPASSWORD=secret ``` 以下の各 `psql -c` は、PgBouncer への別々のクライアント接続です。Web アプリからの別々のリクエストや、別々のサーバーレス呼び出しと同じです。まず次のヘルパーをシェルに貼り付けてください。 ```bash pooled() { psql -X -h localhost -p 6432 -U app app "$@"; } ``` ## 1. あるリクエストの SET が次のリクエストに適用される クライアント A は、アプリがリクエストの開始時に行うように、1 秒のステートメントタイムアウトを設定します。クライアント B は別の誰かです。 ```bash pooled -c "SET statement_timeout = '1s';" pooled -c "SHOW statement_timeout;" -c "SELECT pg_sleep(2);" ``` ```text SET statement_timeout ------------------- 1s (1 row) ERROR: canceling statement due to statement timeout ``` クライアント B はタイムアウトを一度も設定していないのに、2 秒のクエリがキャンセルされました。`SET` はそれ自体のトランザクションで実行され、サーバー接続は `statement_timeout = 1s` を抱えたままプールに戻り、B がその接続を受け取ったのです。プールが大きければ逆のことも起きます。クライアント A の次のステートメントが、タイムアウトが設定されていない別のサーバー接続に割り当てられるのです。本番環境では両方が見られます。ときどき適用されない設定と、頼んでもいないリクエストで起きるタイムアウトや誤った `search_path` です。 追跡されるパラメーターは挙動が異なります。同じ実験を `SET TimeZone = 'Asia/Tokyo'` で行い、2 つ目のクライアントから `SHOW TimeZone` を実行すると `UTC` が表示されます。PgBouncer が各クライアント自身の値を復元するからです。`search_path` はこのリストに含まれておらず、`statement_timeout` と同じように漏れます。 ```bash pooled -c "SET search_path = app_private, public;" pooled -Atc "SHOW search_path;" ``` ```text SET app_private, public ``` **修正方法：設定のスコープをトランザクションに限定します。** `SET LOCAL` は現在のトランザクションの終わりまで有効なので、そのトランザクション内のすべてのステートメントに適用され、サーバー接続がプールに戻る前に消えます。 ```bash pooled -c "BEGIN; SET LOCAL statement_timeout = '1s'; SHOW statement_timeout; COMMIT;" pooled -Atc "SHOW statement_timeout;" ``` ```text BEGIN SET statement_timeout ------------------- 1s (1 row) COMMIT 0 ``` （手順をなぞっている場合は、上の漏れを片付けるために、先に `pooled -c "RESET statement_timeout;" -c "RESET search_path;"` を実行してください。）アプリケーションコードからは、`set_config(name, value, true)` が `SET LOCAL` と同じ働きをし、しかもパラメーターを受け取れるので、行レベルセキュリティのポリシーにユーザー ID を渡す一般的な方法になっています。[マネージド認証のガイド](/blog/ja/managed-auth-getting-started)はまさにこのパターンを使っており、同じ理由でプーラーの背後でも正しく動きます。 すべての接続に持たせたい設定は、代わりにロールに紐づけます。 ```sql ALTER ROLE app SET statement_timeout = '5s'; ``` Postgres はロールの設定をサーバー接続の開始時に適用します。PgBouncer がすでに開いているサーバー接続は古い値のままです。私たちの検証環境では、`ALTER ROLE` の直後にプーラー経由で `SHOW statement_timeout` を実行するとまだ `0` が表示され、PgBouncer が新しいサーバー接続を開いた後に `5s` になりました。 ## 2. 「prepared statement already exists」 SQL レベルのプリペアドステートメントはサーバー接続に属します。同じ名前でプリペアする 2 つのクライアントは、遅かれ早かれ同じサーバー接続に割り当てられます。 ```bash pooled -c "PREPARE get_one AS SELECT 1;" pooled -c "PREPARE get_one AS SELECT 1;" ``` ```text PREPARE ERROR: prepared statement "get_one" already exists ``` PgBouncer は SQL レベルの `PREPARE` を一切追跡しません。設定リファレンスによると、設定にかかわらず `PREPARE`、`EXECUTE`、`DEALLOCATE` をそのまま Postgres に転送します。トランザクションプーラー経由では使わないでください。（検証環境の後片付けは `pooled -c "DEALLOCATE ALL;"` で行えます。） 多くの人は、このエラーにむしろドライバー経由で遭遇します。多くのドライバーは、デフォルトで、あるいはクエリが繰り返されたときに、プロトコルレベルのプリペアドステートメントを使います。クエリを名前付きで一度送り、その後は名前で実行するのです。次は node-postgres（`pg` 8.23）で、2 つのクライアントから名前付きクエリを実行する例です。 ```js // named.js const { Client } = require('pg'); async function main() { const url = process.env.DATABASE_URL; const a = new Client({ connectionString: url }); const b = new Client({ connectionString: url }); await a.connect(); await b.connect(); const query = { name: 'get-account', text: 'SELECT $1::int AS id' }; try { console.log('a:', (await a.query({ ...query, values: [1] })).rows); console.log('b:', (await b.query({ ...query, values: [2] })).rows); } catch (err) { console.log('error:', err.message); } finally { await a.end(); await b.end(); } } main(); ``` ```bash npm install pg@8.23.1 DATABASE_URL=postgres://app:secret@localhost:6432/app node named.js ``` ```text a: [ { id: 1 } ] error: prepared statement "get-account" already exists ``` その逆のケースには、サーバー接続が 2 本のプールが必要です。クライアント A が名前付きクエリを実行し、別のクライアントがトランザクションを開いて A が使ったばかりのサーバー接続を取り、A がもう一度クエリを実行します。ドライバーは `get-account` をすでにプリペアしたと記憶しているので「実行」メッセージだけを送り、それがもう一方のサーバー接続に届きます。結果は `prepared statement "get-account" does not exist` です。私たちは検証環境で両方のエラーを再現しました。 PgBouncer 1.21 では、プロトコルレベルのケースに対する修正が追加されました。`max_prepared_statements` を 0 より大きく設定すると、各クライアントがプリペアしたステートメントを追跡し、そのクライアントが受け取ったサーバー接続上でプリペアします。`max_prepared_statements = 200` で両方の実験を再実行したところ、どちらのエラーも消えましたが、上の SQL レベルの `PREPARE` は依然として失敗しました。 **Relyt One のプール接続文字列では、この追跡はオフです**（`max_prepared_statements = 0`）。そのため、プーラー経由で接続するときは、ドライバーの名前付きプリペアドステートメントを無効にしてください。 | ドライバー | プーラー経由での対応 | | ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | node-postgres（`pg`） | クエリ設定に `name` を渡さないでください。通常のパラメーター付きクエリ（`client.query(text, values)`）は名前なしステートメントを使うので問題なく動きます。検証環境で確認済みです | | psycopg 3 | `prepare_threshold=None` で接続します。これにより[プリペアドステートメントが無効になります](https://www.psycopg.org/psycopg3/docs/advanced/prepare.html) | | asyncpg | [FAQ](https://magicstack.github.io/asyncpg/current/faq.html) が PgBouncer 向けに推奨しているとおり、`connect()` または `create_pool()` に `statement_cache_size=0` を渡します | | Prisma | プール接続の URL に `pgbouncer=true` を追加し、マイグレーションはダイレクト URL で実行します。Prisma のガイドでは PgBouncer 1.21 以降ではこのフラグを不要としていますが、それは `max_prepared_statements` が 0 より大きいことが前提で、ここではそうではありません（[Prisma の PgBouncer ガイド](https://www.prisma.io/docs/orm/prisma-client/setup-and-configuration/databases-connections/pgbouncer)を参照） | ステートメントを一度だけプリペアすることで節約できるプランニング時間は失われます。Web リクエストが実行する短いクエリでは、そのコストはネットワークの往復に比べて通常は小さなものです。プリペアドステートメントに依存している場合は、ダイレクト接続を使ってください。 ## 3. 何もロックしないアドバイザリロック セッションレベルのアドバイザリロックは、1 つのワーカーだけがジョブを実行するようにするための一般的な方法です。このロックはサーバー接続に属し、再入可能です。つまり、ロックを保持しているセッションはそのロックを再び取得できます。トランザクションプーラーの背後では、これが次のような結果になります。 ```bash pooled -c "SELECT pg_advisory_lock(42);" pooled -c "SELECT pg_try_advisory_lock(42) AS got_it;" ``` ```text pg_advisory_lock ------------------ (1 row) got_it -------- t (1 row) ``` ワーカー A がロック 42 を取得しました。ワーカー B が同じロックを要求すると、Postgres は許可しました。両方のリクエストが同じサーバー接続を通ったからです。これで両方のワーカーが、ジョブを独占していると思い込んでいます。プールが大きい場合は、壊れ方は違っても同じくらいひどいことになります。ワーカー A のロック解除が別のサーバー接続に届き、Postgres はそのロックを所有していないという警告を返し、A が終わった後もロックは元のサーバー接続に保持されたままになります。（検証環境のロックは `pooled -c "SELECT pg_advisory_unlock_all();"` で解放できます。） **修正方法：ロックをトランザクション単位で取得します。** `pg_advisory_xact_lock()` と `pg_try_advisory_xact_lock()` は `COMMIT` または `ROLLBACK` の時点でロックを解放するので、ロックと処理が同じトランザクションと同じサーバー接続を共有します。2 つ目のワーカーを排除する様子を見るには、2 つのワーカーが同時に実行できるよう、検証環境で `default_pool_size = 2` に設定して PgBouncer を再起動します。 ```bash docker restart pgbouncer pooled -c "BEGIN; SELECT pg_advisory_xact_lock(42); SELECT pg_sleep(3); COMMIT;" > /dev/null & sleep 1 pooled -c "SELECT pg_try_advisory_xact_lock(42) AS got_it;" wait ``` ```text got_it -------- f (1 row) ``` ジョブは 1 つのトランザクションに収まる必要があります。収まらない場合は、ダイレクト接続でセッションロックを取得してください。 ## 4. 「unsupported startup parameter in options」 接続ごとのデフォルト値を設定する一般的な方法は、接続文字列の `options` パラメーターです。PgBouncer はこれを拒否します。 ```bash psql -X "host=localhost port=6432 user=app dbname=app options=-cstatement_timeout=5s" -c "SELECT 1" ``` ```text psql: error: connection to server at "localhost" (::1), port 6432 failed: FATAL: unsupported startup parameter in options: statement_timeout ``` PgBouncer は、追跡できるスタートアップパラメーターしか受け付けません。未知のパラメーターを無視させることもできます（`ignore_startup_parameters`）が、その場合は設定が黙って捨てられるので、さらに悪い結果になります。Relyt One のプール接続エンドポイントも `options` を転送しません。デフォルト値にはセクション 1 の `ALTER ROLE … SET` を、1 トランザクションだけなら `SET LOCAL` を使ってください。 ## 5. LISTEN、カーソル、一時テーブル この 3 つはトランザクションより長く続くセッションを必要とし、PgBouncer の機能表ではトランザクションプーリングで「Never」とされています。 - **`LISTEN`** はクライアントではなくサーバー接続を登録するので、通知はそのときたまたまそのサーバー接続を持っているクライアントに届くか、誰にも届きません。`NOTIFY` による送信は動作します。 - **`WITH HOLD` カーソル** は `COMMIT` の後もサーバー接続上で開いたままです。次の `FETCH` は別の接続に届くかもしれません。 - トランザクションより長く残る **一時テーブル** は、他のクライアントに漏れます。次の例では、クライアント A が一時テーブルを作成し、それを作成していないクライアント B がクエリできてしまいます。 ```bash pooled -c "CREATE TEMP TABLE scratch (id int);" pooled -c "SELECT count(*) FROM scratch;" ``` ```text CREATE TABLE count ------- 0 (1 row) ``` `ON COMMIT DROP` 付きで作成した一時テーブルはトランザクションの終わりに消えるので安全です。それ以外は、ダイレクト接続を使ってください。 ## Relyt One でのプール接続とダイレクト接続 Relyt One では、1 つのコンピュートに対して 2 つの接続文字列が提供されます。**Connect**（接続）ダイアログの **Connection pooling**（コネクションプーリング）スイッチで、どちらかを選びます。プール接続はトランザクションレベルのプーリングで PgBouncer を経由し、最大 10,000 の同時クライアント接続をサポートします。ダイレクト接続は通常の Postgres セッションで、上記のすべての機能を使えます。 | プール接続を使う場面 | ダイレクト接続を使う場面 | | ------------------------------------------------------- | --------------------------------------------------------------- | | 呼び出しごとに接続を開くサーバーレス関数やエッジ関数 | スキーママイグレーション（[そもそも注意が必要な理由](/blog/ja/postgres-migration-locks)） | | 短いトランザクションをいくつか実行する Web リクエスト | `LISTEN`、`WITH HOLD` カーソル、セッションの一時テーブル | | それぞれが独自の接続を保持してしまう多数のアプリインスタンス | セッションのアドバイザリロックと長時間実行されるジョブ | | 状態をトランザクション内に保持するコード（`SET LOCAL`、`set_config(…, true)`） | プリペアドステートメントに依存するドライバーやツール | | | `psql`、GUI クライアント、管理作業 | どちらの接続文字列も同じデータベースを指しているので、1 つのアプリから両方を使えます。リクエストのトラフィックにはプール接続を使い、マイグレーションのステップと通知を LISTEN するバックグラウンドワーカーにはダイレクト接続を使う、という具合です。 ## プール接続に切り替える前のチェックリスト 1. コード内の通常の `SET` をすべて検索し（`BEGIN … COMMIT` の中の `SET` もコミット後まで残ります）、トランザクション内の `SET LOCAL` または `set_config(…, true)` に置き換えます。 2. 接続ごとのデフォルト値を `options=` から `ALTER ROLE … SET` に移します。 3. ドライバーの名前付きプリペアドステートメントを無効にします（セクション 2 の表）。 4. `pg_advisory_lock()` を `pg_advisory_xact_lock()` に置き換えるか、そのジョブをダイレクト接続に移します。 5. `LISTEN`、`WITH HOLD` カーソル、セッションの一時テーブルをダイレクト接続に移します。 6. マイグレーションはダイレクト接続で実行し続けます。 7. 小さなプールでテストします。検証環境で示したとおり、サイズ 1 のプールにすると、本番環境で断続的に起きるバグが毎回起きるエラーになります。 [無料で始める](/auth/signin?ref=blog-pgbouncer-transaction-pooling)