public inbox for [email protected]
help / color / mirror / Atom feedFrom: Achilleas Mantzios <[email protected]>
To: [email protected]
Subject: Re: PgBouncer-Postgres : un supported startup parameter statement_timeout
Date: Wed, 16 Jul 2025 09:30:03 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAKgGyB8iYj_JavbDD13-4PknNJPji+=tbmSjk=80ZtcZmr-2nw@mail.gmail.com>
<[email protected]>
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: PgBouncer-Postgres : un supported startup parameter statement_timeout
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox