Я думаю, что многие, кто занимается разработкой в стеке 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 года назад, так что, как минимум для меня, эта статья оказалась полезной. Так что, если все же было интересно — ставьте лайк, подписывайтесь на канал. Спасибо, что дочитали до конца!