Я думаю, что многие, кто занимается разработкой в стеке Go + PgBouncer + PostgreSQL, уже примерно поняли, о чём будет статья — о проблеме выполнения prepared statements и сравнении работы двух популярных драйверов для PostgreSQL: lib/pq и jackc/pgx.
Если интересно — добро пожаловать под кат.
🧨 Описание проблемы
1. Одной стороны — PgBouncer
PgBouncer создаёт ограниченный пул соединений к PostgreSQL и переиспользует их между клиентами, которых может быть в разы больше. Существует 3 режима работы PgBouncer с соединениями:
-
session— соединение закрепляется за клиентом на всю сессию (наименее эффективен); -
transaction— соединение выделяется только на время выполнения транзакции (оптимальный и популярный режим); -
statement— соединение выделяется на каждый SQL-запрос (максимальная эффективность, но много ограничений).
Далее мы будем использовать режим transaction, как наиболее популярный.
2. С другой стороны — драйверы к Pg
Драйверы PostgreSQL (в том числе для Go) обычно выполняют запросы в два этапа: подготовка prepared statement и его последующее выполнение с параметрами. Это безопасный подход, так как SQL-запрос отделён от данных, что исключает SQL-инъекции.
Но поскольку PgBouncer может направить второй этап (выполнение запроса) на другое соединение, которое не знает о ранее подготовленном statement, мы получаем ошибку.
Я создал папочку в репозитории, где можно воспроизвести проблему. Для этого достаточно скачать файлы и выполнить следующие команды:
make start-env # поднятие окружения в docker compose go run main.go --mode lib/pq --problem # воспроизведение проблемы с драйвером lib/pq #out: ✅ query executed successfully ✅ query executed successfully ❌ query failed: pq: bind message supplies 17 parameters, but prepared statement "" requires 23 ✅ query executed successfully ✅ query executed successfully ❌ query failed: pq: bind message supplies 25 parameters, but prepared statement "" requires 27 ❌ query failed: pq: bind message supplies 22 parameters, but prepared statement "" requires 27 ❌ query failed: pq: bind message supplies 5 parameters, but prepared statement "" requires 27 ❌ query failed: pq: bind message supplies 6 parameters, but prepared statement "" requires 31 ❌ query failed: pq: bind message supplies 31 parameters, but prepared statement "" requires 6 go run main.go --mode jackc/pgx --problem # воспроизведение проблемы с драйвером lib/pq #out ❌ query failed: ERROR: prepared statement "stmtcache_9380f4d2e1a2b142f7fe0abcac4e988bc2e5743c200e941d" already exists (SQLSTATE 42P05) ✅ query executed successfully ❌ query failed: ERROR: prepared statement "stmtcache_962730cffc37e0a1a08d9043dbe66bb5fa1ad53cfbffb8c5" does not exist (SQLSTATE 26000) ✅ query executed successfully ❌ query failed: ERROR: prepared statement "stmtcache_377d3096921449e3e4893ab85c5416441e35cbc0c0bd2959" already exists (SQLSTATE 42P05) ✅ query executed successfully ✅ query executed successfully ❌ query failed: ERROR: prepared statement "stmtcache_9380f4d2e1a2b142f7fe0abcac4e988bc2e5743c200e941d" already exists (SQLSTATE 42P05) ❌ query failed: ERROR: prepared statement "stmtcache_377d3096921449e3e4893ab85c5416441e35cbc0c0bd2959" already exists (SQLSTATE 42P05) ✅ query executed successfully ⚡ SimpleProtocol
Если поискать, как решается подобная проблема для драйвера jackc/pgx, можно найти статьи вроде этой, где рекомендуется включать PreferSimpleProtocol.
Что это такое? Проще всего понять, начав с официальной документации PostgreSQL (на русском) и кода в pgx (комментарии в коде ниже уже мои):
func (c *Conn) execSimpleProtocol(ctx context.Context, sql string, arguments []any) (commandTag pgconn.CommandTag, err error) { if len(arguments) > 0 { // в sanitizeForSimpleQuery происходит экранирование и // формирование финального запроса sql, err = c.sanitizeForSimpleQuery(sql, arguments...) if err != nil { return pgconn.CommandTag{}, err } } // Здесь непосредственно выполнение, как видите, аргументы тут не передаются, т.к. // в sanitizeForSimpleQuery все аргументы уже были подставлены в запрос. mrr := c.pgConn.Exec(ctx, sql) for mrr.NextResult() { commandTag, _ = mrr.ResultReader().Close() } err = mrr.Close() return commandTag, err } PreferSimpleProtocol просто формирует готовую строку запроса, как если бы вы её писали в psql. SQL-инъекции здесь возможны, если неаккуратно обрабатывать входные данные.
Проверим, что всё работает на нашем стенде:
go run main.go --mode jackc/pgx ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ⚡ BinaryParameters
Драйвер lib/pq позволяет использовать binary_parameters=yes в строке подключения (dsn). Это интереснее, чем SimpleProtocol: здесь используется расширенный протокол (на русском) с поддержкой pipelining (конвейеризация)— возможностью отправлять несколько команд в одном пакете, что мы и видим в коде lib/pq (далее в коде мои + оригинальные комментарии).
func (cn *conn) sendBinaryModeQuery(query string, args []driver.Value) { if len(args) >= 65536 { errorf("got %d parameters but PostgreSQL only supports 65535 parameters", len(args)) } // здесь начинается команда Parse, // описание формата сообщения <https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-PARSE> b := cn.writeBuf('P') b.byte(0) // unnamed statement <- это оригинальный комментарий, обратите на него внимание b.string(query) b.int16(0) // команда Bind <https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-BIND> b.next('B') b.int16(0) // unnamed portal and statement cn.sendBinaryParameters(b, args) b.bytes(colFmtDataAllText) // команда Describe <https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-DESCRIBE> b.next('D') b.byte('P') b.byte(0) // unnamed portal // команда Execute <https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-EXECUTE> b.next('E') b.byte(0) b.int32(0) // команда Sync <https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-SYNC> b.next('S') cn.send(b) } Таким образом, binary_parameters в lib/pq отправляет несколько команд, одним сообщением, что исключает описанную ранее проблему. Просто и эффективно. Мы можем вернуться к нашему стенду и выполнить следующую команду, чтобы убедиться, что всё теперь работает:
go run main.go --mode lib/pq ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ⚡ И снова pgx
Хотя наиболее известным решение проблемы (по моему субъективному мнению) является simple protocol,у jackc/pgx есть свой аналог binary_parameters , а именно ExecMode (появился в 5й версии pgx, 2022 год). По-сути, он делает тоже самое (ссылка на код):
func (pgConn *PgConn) ExecParams(ctx context.Context, sql string, paramValues [][]byte, paramOIDs []uint32, paramFormats []int16, resultFormats []int16) *ResultReader { result := pgConn.execExtendedPrefix(ctx, paramValues) if result.closed { return result } // знакомая нам команда Parse pgConn.frontend.SendParse(&pgproto3.Parse{Query: sql, ParameterOIDs: paramOIDs}) // потом Bind pgConn.frontend.SendBind(&pgproto3.Bind{ParameterFormatCodes: paramFormats, Parameters: paramValues, ResultFormatCodes: resultFormats}) // Describe, Execute и Sync прячутся уже внутри этого метода pgConn.execExtendedSuffix(result) return result } func (pgConn *PgConn) execExtendedSuffix(result *ResultReader) { pgConn.frontend.SendDescribe(&pgproto3.Describe{ObjectType: 'P'}) pgConn.frontend.SendExecute(&pgproto3.Execute{}) pgConn.frontend.SendSync(&pgproto3.Sync{}) err := pgConn.flushWithPotentialWriteReadDeadlock() if err != nil { pgConn.asyncClose() result.concludeCommand(CommandTag{}, err) pgConn.contextWatcher.Unwatch() result.closed = true pgConn.unlock() return } result.readUntilRowDescription() } Таким образом pgx отвоёвывает очки репутации, потерянные с SimpleProtocol. А далее даже вырывается вперед, но обо всем по порядку.
Снова проверим, что все корректно работает на стенде:
go run main.go --mode exec ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully 🚫 «Бесполезная» статья?
Почему в итоге она «бесполезная»? Возможно, вы уже используете jackc/pgx с настройками по умолчанию, и всё у вас работает без каких-либо проблем. Всё потому, что в 2023 году в PgBouncer добавили поддержку prepared statements.
Мы также это можем проверить на стенде. Изначально для того чтобы проблему можно было воспроизвести, я указывал для PgBouncer значение параметра MAX_PREPARED_STATEMENTS, равное 0 (по умолчанию — 200). Мы можем перезапустить тестовый стенд со значением по умолчанию и проверить поведение:
make stop-env # если ранее запускали make start-env-with-ps # запуск окружения с MAX_PREPARED_STATEMENTS=200 go run main.go --mode jackc/pgx --problem # ранее такой запуск приводил к ошибке ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully Логично ожидать, что с включённой поддержкой prepared statements в PgBouncer всё заработает и с lib/pq без дополнительных параметров… но нет.
В коде видно, что lib/pq использует неименованные prepared statements, что мешает PgBouncer их кэшировать:
func (cn *conn) sendBinaryModeQuery(query string, args []driver.Value) { ... b := cn.writeBuf('P') b.byte(0) // unnamed statement b.string(query) b.int16(0) ... } Из-за чего в результате ошибки сохраняются:
# ранее должно было быть запущено окружение командой `make start-env-with-ps` go run main.go --mode lib/pq --problem ❌ query failed: pq: bind message supplies 32 parameters, but prepared statement "" requires 8 ✅ query executed successfully ✅ query executed successfully ✅ query executed successfully ❌ query failed: pq: bind message supplies 17 parameters, but prepared statement "" requires 34 ❌ query failed: pq: bind message supplies 31 parameters, but prepared statement "" requires 27 ✅ query executed successfully ❌ query failed: pq: bind message supplies 5 parameters, but prepared statement "" requires 17 ✅ query executed successfully ❌ query failed: pq: bind message supplies 33 parameters, but prepared statement "" requires 32 Так что, если вы все-таки используете драйвер lib/pq в связке с PgBouncer, то binary_parameters=yes вам все ещё необходимо указывать.
📌 PS
Надеюсь, что всё-таки статья вышла не такой бесполезной, как заявлено в заголовке. Готовя материал для статьи, я не знал про обновления, сделанные в PgBouncer 3 года назад, так что, как минимум для меня, эта статья оказалась полезной. Так что, если все же было интересно — ставьте лайк, подписывайтесь на канал. Спасибо, что дочитали до конца!
ссылка на оригинал статьи https://habr.com/ru/articles/913754/
Добавить комментарий