pgbouncer: 'unsupported startup parameter: extra_float_digits' (and 'no such database')

TL;DR

1
2
3

; /etc/pgbouncer/pgbouncer.ini
[pgbouncer]
ignore_startup_parameters = extra_float_digits, options

1

systemctl reload pgbouncer          # or RELOAD; on the admin console: psql -p 6432 -U pgbouncer pgbouncer

extra_float_digits can be ignored with no effect. options (what JDBC and DataGrip use for -c search_path=…) is ignored too, but then the client’s search_path never arrives: pin it on the role with alter role app set search_path = app, public.

The error

1

FATAL: unsupported startup parameter: extra_float_digits

or, depending on the client, pooler error: unsupported startup parameter: extra_float_digits. Straight to port 5432 it connects; through pgbouncer’s 6432, it does not. JDBC sends it (everything using the PostgreSQL Java driver: DBeaver, DataGrip, Spring, Jenkins), as do Npgsql and some ORMs; other clients send options.

Why it happens

When a client opens a connection, it puts the session parameters it wants into the startup packet (extra_float_digits=3, options=-c search_path=app). pgbouncer, in transaction or statement mode, shares one server connection among many clients, so it only lets through the parameters it knows how to store per client and restore on the server when that client’s turn comes: client_encoding, datestyle, timezone, standard_conforming_strings, application_name. Anything else would be a setting leaking from one client to another; rather than that, pgbouncer rejects the connection.

Two ways to resolve it:

extra_float_digits is harmless since PostgreSQL 12: float output is already the shortest exact representation and the parameter changes nothing. options with search_path does matter: ignored, the client sees the database’s default search_path and unqualified queries fail with relation does not exist. The clean fix is the role:

1

alter role app set search_path = app, public;

’no such database'

1

pooler error: no such database: app_db

pgbouncer only routes databases listed under [databases]. Either add the entry, or a wildcard:

1
2
3

[databases]
app_db = host=db.example port=5432 dbname=app_db
* = host=db.example port=5432

With *, a client asking for other_db gets host=db.example dbname=other_db; those automatic entries clean themselves up after autodb_idle_timeout. And check the entry’s dbname=: pointing app_db at another database through a copy-paste is common.

Traps

• RELOAD is enough for ignore_startup_parameters and [databases]; listen_* and auth_type need a restart.
• transaction mode and SET: a manual SET search_path in the session is lost as soon as the server connection moves to another client; use SET LOCAL inside the transaction or the alter role.
• Named prepared statements (JDBC prepareThreshold, PREPARE) do not work in transaction mode on old pgbouncer; since 1.21, max_prepared_statements = 200 supports them.
• SCRAM: with auth_type = scram-sha-256 the userlist.txt needs the SCRAM hash exactly as it comes out of pg_authid, or auth_query so pgbouncer looks it up.
• SHOW CONFIG; and SHOW DATABASES; on the admin console tell you what is really loaded, not what you think you wrote.

See also: PostgreSQL: admin cheat sheet. Reference: pgbouncer.ini.