4.1.2 Can't query with with PGBouncer?

[relistan]

Connecting to PGBouncer, without SSL, and attempting to run any query fails with:

ERROR: unsupported pkt type: 80 (SQLSTATE 08P01)

I also tried 4.0.0 and it seems that this error is not new. Looks quite similar to #512. I attempted both the sql.DB interface and the native pgx interface to no avail.

Versions:

• PGBouncer 1.11.0
• pgx 4.0.0, 4.1.2

[jackc]

There are a few restrictions when using PgBouncer. One is that you cannot use prepared statements. So if you have any explicit prepared statement usage that would be a problem. But also pgx automatically uses prepared statements internally. So that needs to be disabled for pgx to work with pgbouncer.

There are two ways of doing that:

1. Set the statement cache to describe mode (include statement_cache_mode=describe in your connection string is the simplest way to set this)
2. Set PreferSimpleProtocol to true in your pgx.ConnConfig

[glebwell]

I tried to use PreferSimpleProtocol = true, but it didn't work in postgresql 10. It returns Database error: unused argument: 0.
I also tried to set statement_cache_mode=describe via connection string. I've done it using pgx.ParseConnectionString("postgresql://user:user@ip:port/db?statement_cache_mode=describe&sslmode=disable"). But function returned: unrecognized configuration parameter "statement_cache_mode" (SQLSTATE 42704).
I used pgx version 3.6.0.
@jackc, could you please help me sharing the code showing how to set statement_cache_mode parameter?

[jackc]

@glebwell statement_cache_mode is only needed / supported in v4. PreferSimpleProtocol should be sufficient in v3.

I don't recognize the error Database error: unused argument: 0. Are you using something else besides pgx like an ORM?

[glebwell]

@jackc, thanks for quick reply. I figured out that the error Database error: unused argument: 0 occured because of sqlx library. When I made sql requests using only pgx (with PreferSimpleProtocol true), the error's gone. Maybe simple protocol affects badly on structure mapping feature of sqlx.
I also tried to set statement_cache_mode=describe via connection string like postgres://user:password@ip:port/db?statement_cache_mode=describe. And it's been parsed without errors by pgxpool.ParseConfig (pgx v4). Just one question, is it safe to use both settings in the same time: statement_cache_mode=describe and PreferSimpleProtocol=true?

[jackc]

is it safe to use both settings in the same time: statement_cache_mode=describe and PreferSimpleProtocol=true

It is safe -- as in nothing should break -- but the functionality is mutually exclusive. The statement cache is only used with the extended protocol. So when PreferSimpleProtocol=true the statement cache will not be used. In most cases either one should work fine, but in v4 statement_cache_mode=describe may give better performance in some circumstances.

[glebwell]

@jackc, thanks for good explanation.

[voodoo-dn]

@jackc We are use PgBouncer with session mode and use binary_parameters=yes connection option, because we need to send parameters with prepared statement. But on connect, we received unrecognized configuration parameter "binary_parameters" (SQLSTATE 42704). Could we use pgbouncer or we need direct connection to Postgres?

[jackc]

Is binary_parameters a leftover in the connection string from lib/pq? That's not a valid PostgreSQL setting and I don't think it is a PgBouncer setting either.

With pgx and PgBouncer you will want to use either statement_cache_mode=describe or PreferSimpleProtocol=true. Prefer statement_cache_mode=describe as long as you aren't doing schema changes while your application is running.

[mescam]

Is binary_parameters a leftover in the connection string from lib/pq? That's not a valid PostgreSQL setting and I don't think it is a PgBouncer setting either.

With pgx and PgBouncer you will want to use either statement_cache_mode=describe or PreferSimpleProtocol=true. Prefer statement_cache_mode=describe as long as you aren't doing schema changes while your application is running.

@jackc Could you please explain what kind of issues we might encounter during schema changes on live application with describe mode?

[jackc]

Describe statement cache mode allows pgx to remember the argument types and result types from a query. If a column type changes then query errors or even data corruption could occur. For example, if the server changed a column from an int4 to float4 data corruption could occur as the binary representation of an integer would be stored into a float.

[pavelpatrin]

Thank you guys for this thread, it helps a lot. I have one more question about v4.

@jackc, why ParseConfig() knows nothing about prefer_simple_protocol DSN option?

[jackc]

@jackc, why ParseConfig() knows nothing about prefer_simple_protocol DSN option?

No reason. Just never implemented. It's on master now.

[pavelpatrin]

@jackc thank you very much!

[pavelpatrin]

For everyone, who used this but migrated to V5.

Right now, simple protocol can be enabled in a different way.

// ParseConfig creates a ConnConfig from a connection string. ParseConfig handles all options that pgconn.ParseConfig
// does. In addition, it accepts the following options:
//
//   - default_query_exec_mode.
//     Possible values: "cache_statement", "cache_describe", "describe_exec", "exec", and "simple_protocol". See
//     QueryExecMode constant documentation for the meaning of these values. Default: "cache_statement".

[pavelpatrin]

Error for search engines.

FATAL: unrecognized configuration parameter "prefer_simple_protocol" (SQLSTATE 42704)

[relistan]

@pavelpatrin thanks for that

[CodaBool]

wasn't super clear to me but this effectively means changing the connection string from

postgres://postgres:PASSWORD@HOST:6543/postgres?statement_cache_mode=describe

into

postgres://postgres:PASSWORD@HOST:6543/postgres?default_query_exec_mode=simple_protocol

default_query_exec_mode can be any of these values: "cache_statement", "cache_describe", "describe_exec", "exec", and "simple_protocol"

Or adding to the query itself

db.Query(context.Background(), "select stars, description from repo", pgx.QueryExecModeSimpleProtocol)