Thread

1. PgBouncer-Postgres : un supported startup parameter statement_timeout

   KK CHN <kkchn.in@gmail.com> — 2025-07-16T07:22:54Z

   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.
   
   
   
   *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  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}@
   ${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,
           ...poolOptions,
       }),
   };
   
   ..................................
   
   Any  hints   and suggestions in the config params are most welcome.
   
   Thank you,
   Krishane
   

2. Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

   Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-07-16T07:40:11Z

   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
   
   >
   >
   > *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 
   > <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@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}@${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,
   >         ...poolOptions,
   >     }),
   > };
   >
   > ..................................
   >
   > Any  hints   and suggestions in the config params are most welcome.
   >
   > Thank you,
   > Krishane

3. Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

   Robert Sjöblom <robert.sjoblom@fortnox.se> — 2025-07-16T07:47:18Z

   There's an option to ignore parameters in pgbouncer's config. Here's an SO
   answer that gives an example: https://stackoverflow.com/a/36911794
   
   On Wed, 16 Jul 2025, 09:19 KK CHN, <kkchn.in@gmail.com> 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.
   >
   >
   >
   > *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  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}@
   > ${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,
   >         ...poolOptions,
   >     }),
   > };
   >
   > ..................................
   >
   > Any  hints   and suggestions in the config params are most welcome.
   >
   > Thank you,
   > Krishane
   >
   
   -- 
   Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för 
   adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är 
   förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag 
   gått till fel adressat vänligen radera det ursprungliga meddelandet och 
   underrätta avsändaren via e-post
   

4. Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

   Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> — 2025-07-16T08:30:03Z

   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 
   >> <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@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}@${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,
   >>         ...poolOptions,
   >>     }),
   >> };
   >>
   >> ..................................
   >>
   >> Any  hints   and suggestions in the config params are most welcome.
   >>
   >> Thank you,
   >> Krishane

5. Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

   Adrian Klaver <adrian.klaver@aklaver.com> — 2025-07-16T15:29:13Z

   On 7/16/25 00:40, Achilleas Mantzios wrote:
   > 
   > On 7/16/25 08:22, KK CHN wrote:
   
   > 
   > 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"
   
   For completeness the below does work:
   
   psql postgresql://postgres@localhost:5432?options=-cstatement_timeout%3D10
   
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com