PgBouncer のトランザクションプーリングで壊れるものと、それぞれの直し方

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 の機能表、設定リファレンス、そして私たちの検証環境から、影響を受けるものを挙げます。

この記事の残りでは、各行が実際に起きる様子を示します。

PgBouncer の設定リファレンスにある 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 本だけなら、すべてのクライアントが同じ接続に割り当てられるので、各障害がたまにではなく毎回起きます。本番環境でプールに数十本のサーバー接続があると、同じ障害が断続的に起きます。デバッグが難しいのはまさにそのためです。

; 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

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 アプリからの別々のリクエストや、別々のサーバーレス呼び出しと同じです。まず次のヘルパーをシェルに貼り付けてください。

pooled() { psql -X -h localhost -p 6432 -U app app "$@"; }

クライアント A は、アプリがリクエストの開始時に行うように、1 秒のステートメントタイムアウトを設定します。クライアント B は別の誰かです。

pooled -c "SET statement_timeout = '1s';"
pooled -c "SHOW statement_timeout;" -c "SELECT pg_sleep(2);"

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 と同じように漏れます。

pooled -c "SET search_path = app_private, public;"
pooled -Atc "SHOW search_path;"

SET
app_private, public

修正方法：設定のスコープをトランザクションに限定します。 SET LOCAL は現在のトランザクションの終わりまで有効なので、そのトランザクション内のすべてのステートメントに適用され、サーバー接続がプールに戻る前に消えます。

pooled -c "BEGIN; SET LOCAL statement_timeout = '1s'; SHOW statement_timeout; COMMIT;"
pooled -Atc "SHOW statement_timeout;"

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 を渡す一般的な方法になっています。マネージド認証のガイドはまさにこのパターンを使っており、同じ理由でプーラーの背後でも正しく動きます。

すべての接続に持たせたい設定は、代わりにロールに紐づけます。

ALTER ROLE app SET statement_timeout = '5s';

Postgres はロールの設定をサーバー接続の開始時に適用します。PgBouncer がすでに開いているサーバー接続は古い値のままです。私たちの検証環境では、ALTER ROLE の直後にプーラー経由で SHOW statement_timeout を実行するとまだ 0 が表示され、PgBouncer が新しいサーバー接続を開いた後に 5s になりました。

SQL レベルのプリペアドステートメントはサーバー接続に属します。同じ名前でプリペアする 2 つのクライアントは、遅かれ早かれ同じサーバー接続に割り当てられます。

pooled -c "PREPARE get_one AS SELECT 1;"
pooled -c "PREPARE get_one AS SELECT 1;"

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 つのクライアントから名前付きクエリを実行する例です。

// 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();

npm install pg@8.23.1
DATABASE_URL=postgres://app:secret@localhost:6432/app node named.js

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）。そのため、プーラー経由で接続するときは、ドライバーの名前付きプリペアドステートメントを無効にしてください。

ステートメントを一度だけプリペアすることで節約できるプランニング時間は失われます。Web リクエストが実行する短いクエリでは、そのコストはネットワークの往復に比べて通常は小さなものです。プリペアドステートメントに依存している場合は、ダイレクト接続を使ってください。

セッションレベルのアドバイザリロックは、1 つのワーカーだけがジョブを実行するようにするための一般的な方法です。このロックはサーバー接続に属し、再入可能です。つまり、ロックを保持しているセッションはそのロックを再び取得できます。トランザクションプーラーの背後では、これが次のような結果になります。

pooled -c "SELECT pg_advisory_lock(42);"
pooled -c "SELECT pg_try_advisory_lock(42) AS got_it;"

 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 を再起動します。

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

 got_it
--------
 f
(1 row)

ジョブは 1 つのトランザクションに収まる必要があります。収まらない場合は、ダイレクト接続でセッションロックを取得してください。

接続ごとのデフォルト値を設定する一般的な方法は、接続文字列の options パラメーターです。PgBouncer はこれを拒否します。

psql -X "host=localhost port=6432 user=app dbname=app options=-cstatement_timeout=5s" -c "SELECT 1"

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 を使ってください。

この 3 つはトランザクションより長く続くセッションを必要とし、PgBouncer の機能表ではトランザクションプーリングで「Never」とされています。

• LISTEN はクライアントではなくサーバー接続を登録するので、通知はそのときたまたまそのサーバー接続を持っているクライアントに届くか、誰にも届きません。NOTIFY による送信は動作します。
• WITH HOLD カーソル は COMMIT の後もサーバー接続上で開いたままです。次の FETCH は別の接続に届くかもしれません。
• トランザクションより長く残る 一時テーブル は、他のクライアントに漏れます。次の例では、クライアント A が一時テーブルを作成し、それを作成していないクライアント B がクエリできてしまいます。

pooled -c "CREATE TEMP TABLE scratch (id int);"
pooled -c "SELECT count(*) FROM scratch;"

CREATE TABLE
 count
-------
     0
(1 row)

ON COMMIT DROP 付きで作成した一時テーブルはトランザクションの終わりに消えるので安全です。それ以外は、ダイレクト接続を使ってください。

Relyt One では、1 つのコンピュートに対して 2 つの接続文字列が提供されます。Connect（接続）ダイアログの Connection pooling（コネクションプーリング）スイッチで、どちらかを選びます。プール接続はトランザクションレベルのプーリングで PgBouncer を経由し、最大 10,000 の同時クライアント接続をサポートします。ダイレクト接続は通常の Postgres セッションで、上記のすべての機能を使えます。

どちらの接続文字列も同じデータベースを指しているので、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 のプールにすると、本番環境で断続的に起きるバグが毎回起きるエラーになります。

無料で始める