Specify `statement_timeout` in Postgresql with sqlalchemy?

Question

The following statement_timeout option works on some Postgresql databases and on others, I get Unsupported startup parameter: options. Why?

Is this possibly a difference between Postgres 9.4 and 9.6? This works with the former servers and fails with the latter.

from sqlalchemy import create_engine

# As is: Unsupported startup parameter: options
db_engine = create_engine("postgresql://user:pw@host/database",
    connect_args={"options": "-c statement_timeout=1000"})

with db_engine.connect() as db_connection:
    print("got it")

Specifically, I get:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) ERROR:  Unsupported startup parameter: options

Answer 1

You may have been connecting to those databases via PgBouncer.

If so, add ignore_startup_parameters = options to pgbouncer.ini under the [pgbouncer] section.

From https://www.pgbouncer.org/config.html#ignore_startup_parameters:

By default, PgBouncer allows only parameters it can keep track of in startup packets: client_encoding, datestyle, timezone and standard_conforming_strings. All others parameters will raise an error. To allow others parameters, they can be specified here, so that PgBouncer knows that they are handled by the admin and it can ignore them.

Default: empty

References:

• https://github.com/pgbouncer/pgbouncer/issues/295
• https://github.com/pgbouncer/pgbouncer/issues/496