Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

On 7/16/25 08:40, Achilleas Mantzios wrote:

>
> On 7/16/25 08:22, KK CHN wrote:
>> Hi,
>>
>> I am facing an issue with PgBouncer-Postgres setup,
>>
>> *PgBouncer (PgBouncer 1.23.1 running on VM instance FreeBSD 14.2) and
>> PostgreSQL DB cluster on a virtual machine (PG version 16.0,  RHEL
>> 9.4) *.
>>
>>
>> My application backend is nodeJS which throws the following Error in
>> the nodejs  console log: when connecting through pgbouncer to the
>> backend database server...
>>
>> ERROR thrown:
>>
>> my-node>* Failed to connect to PostgreSQL database transaction_db :
>> unsupported startup parameter: statement_timeout *{"code":"08P01",
>> "length":69, "name": "error", xx xx
>> my-node> error:[object Object ] {"timestamp":"15-07-2025 10:14:26"} x
>> x xx
>>
>>
>> Note:   The nodejs apps  earlier directly establishing connections to
>> the  PostgreSQL DB ( no such errors)  now redirected via   Pgbouncer
>> throws this error.
>
> How? I am getting , connecting directly to postgresql, or pgpool, or
> pgbouncer :
>
> achill(at)smadevnu:~ % psql
> "postgresql://localhost:5432?statement_timeout=10"
> psql: error: invalid URI query parameter: "statement_timeout"
> achill(at)smadevnu:~ % psql
> "postgresql://localhost:9999?statement_timeout=10"
> psql: error: invalid URI query parameter: "statement_timeout"
> achill(at)smadevnu:~ % psql
> "postgresql://localhost:6432?statement_timeout=10"
> psql: error: invalid URI query parameter: "statement_timeout"
>
> the error is within : src/interfaces/libpq/fe-connect.c
>
Ok looked a little bit further, in case nodejs driver sends this
(statement_timeout) via the options startup packet, as in :

PGOPTIONS=" -c statement_timeout=10" psql "postgresql://localhost:5432"

 then yes, you will have to include this in pgbouncer's
|ignore_startup_parameters|

as explained here : https://www.pgbouncer.org/config.html

>>
>>
>> *The config parameters for  pgbouncer as follows(correct me if any
>> mistakes made)
>> *
>> [root(at)pgbouncer ~]# grep ^[^';;;'] /usr/local/etc/pgbouncer.ini
>> [databases]
>> transaction_db = host=dbmain.mydomain.com
>> <http://dbmain.mydomain.com/> port=5444 dbname=transaction_db
>>
>> [users]
>> [pgbouncer]
>> Logfile = /var/log/pgbouncer/pgbouncer.log
>> pidfile = /var/run/pgbouncer/pgbouncer.pid
>> listen_addr = *
>> listen_port = 5444
>> auth_type = md5
>> auth_file = /usr/local/etc/pgbouncer.users
>> admin_users = adminuser
>> stats_users = adminuser,
>> pool_mode = transaction
>> server_reset_query = DISCARD ALL
>> server_reset_query_always = 1
>> ignore_startup_parameters = extra_float_digits  // I have added this
>> also or can I add these options too in the following line  ?
>> ;; ignore_startup_parameters = extra_float_digits, options,
>> statement_timeout, idle_in_transaction_session_timeout  // doubt
>> options supported in latest versions ?
>>
>> max_client_conn = 5000   // can I give this much high value ?
>> default_pool_size = 20
>> min_pool_size = 10
>> reserve_pool_size = 10
>> reserve_pool_timeout = 5
>> max_db_connections = 100
>> max_user_connections = 30
>> server_lifetime = 3600
>> server_idle_timeout = 600 // is this a low value or need to be
>>  increased ?
>> [root(at)pgbouncer ~]#
>>
>>
>>
>> The config params of  N*odeJS application which uses nodejs
>> connection pooling in* code as follows
>>
>> the Node JS application using the following  nodejs pooling
>> configurations for the application level
>>
>> *cat app_10072025/config/pg-pool-config.js*
>> .......
>> *const poolOptions = {
>>     max: 10,
>>     min: 2,
>>     idleTimeoutMillis: 600000,   //Idle for 5Min
>>     connectionTimeoutMillis: 10000,  //Reconnect 10sec
>>     statement_timeout: 60000,    //Query executiion 1 min
>>     acquire: 20000,
>>     maxUses: 1000 //reconnect after 1000 queries
>> };*
>>
>> const pools = {
>>
>>     transaction_db: new Pool({
>>         connectionString:
>> `postgresql://${DB_USER}:${DB_PASSWORD}(at)${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,
>>         ...poolOptions,
>>     }),
>> };
>>
>> ..................................
>>
>> Any  hints   and suggestions in the config params are most welcome.
>>
>> Thank you,
>> Krishane