Re: PgBouncer-Postgres : un supported startup parameter statement_timeout - Mailing list pgsql-general

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@smadevnu:~ % psql "postgresql://localhost:5432?statement_timeout=10"
psql: error: invalid URI query parameter: "statement_timeout"
achill@smadevnu:~ % psql "postgresql://localhost:9999?statement_timeout=10"
psql: error: invalid URI query parameter: "statement_timeout"
achill@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@pgbouncer ~]# grep ^[^';;;'] /usr/local/etc/pgbouncer.ini
[databases]
transaction_db = host=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@pgbouncer ~]#



The config params of  NodeJS 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}@${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,
        ...poolOptions,
    }),
};

..................................

Any  hints   and suggestions in the config params are most welcome. 

Thank you,
Krishane