Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ubwkW-007Bmy-8x for pgsql-general@arkaria.postgresql.org; Wed, 16 Jul 2025 07:40:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ubwkU-008TX7-Ae for pgsql-general@arkaria.postgresql.org; Wed, 16 Jul 2025 07:40:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ubwkT-008TW3-R7 for pgsql-general@lists.postgresql.org; Wed, 16 Jul 2025 07:40:18 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1ubwkR-007Xjt-0q for pgsql-general@lists.postgresql.org; Wed, 16 Jul 2025 07:40:17 +0000 Content-Type: multipart/alternative; boundary="------------jeI0jm0XYUK3UsWluB2GUg3k" Message-ID: <24f7d36b-679c-48a9-a12d-344f216da88e@cloud.gatewaynet.com> Date: Wed, 16 Jul 2025 08:40:11 +0100 MIME-Version: 1.0 Subject: Re: PgBouncer-Postgres : un supported startup parameter statement_timeout To: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------jeI0jm0XYUK3UsWluB2GUg3k Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable 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=20 > PostgreSQL DB cluster on a virtual machine (PG version 16.0, =C2=A0RHEL= =20 > 9.4) *. > > > My=C2=A0application backend is nodeJS which throws the following Error = in=20 > the nodejs=C2=A0 console log: when connecting through pgbouncer to the=20 > backend database server... > > ERROR thrown: > > my-node>*=C2=A0Failed to connect to PostgreSQL database transaction_db = :=20 > unsupported startup parameter: statement_timeout *{"code":"08P01",=20 > "length":69, "name": "error", xx xx > my-node> error:[object Object ] {"timestamp":"15-07-2025 10:14:26"} x x= xx > > > Note:=C2=A0 =C2=A0The nodejs apps =C2=A0earlier directly establishing c= onnections to=20 > the =C2=A0PostgreSQL DB ( no such errors) =C2=A0now redirected via =C2=A0= Pgbouncer=20 > throws this error. How? I am getting , connecting directly to postgresql, or pgpool, or=20 pgbouncer : achill@smadevnu:~ % psql "postgresql://localhost:5432?statement_timeout=3D= 10" psql: error: invalid URI query parameter: "statement_timeout" achill@smadevnu:~ % psql "postgresql://localhost:9999?statement_timeout=3D= 10" psql: error: invalid URI query parameter: "statement_timeout" achill@smadevnu:~ % psql "postgresql://localhost:6432?statement_timeout=3D= 10" psql: error: invalid URI query parameter: "statement_timeout" the error is within : src/interfaces/libpq/fe-connect.c > > > *The config parameters for =C2=A0pgbouncer as follows(correct me if any= =20 > mistakes made) > * > [root@pgbouncer ~]# grep ^[^';;;'] /usr/local/etc/pgbouncer.ini > [databases] > transaction_db =3D host=3Ddbmain.mydomain.com=20 > =C2=A0port=3D5444 dbname=3Dtransaction_db > > [users] > [pgbouncer] > Logfile =3D /var/log/pgbouncer/pgbouncer.log > pidfile =3D /var/run/pgbouncer/pgbouncer.pid > listen_addr =3D * > listen_port =3D 5444 > auth_type =3D md5 > auth_file =3D /usr/local/etc/pgbouncer.users > admin_users =3D adminuser > stats_users =3D adminuser, > pool_mode =3D transaction > server_reset_query =3D DISCARD ALL > server_reset_query_always =3D 1 > ignore_startup_parameters =3D extra_float_digits =C2=A0// I have added = this=20 > also or can I add these options too in the following line =C2=A0? > ;; ignore_startup_parameters =3D extra_float_digits, options,=20 > statement_timeout, idle_in_transaction_session_timeout =C2=A0// doubt=20 > options supported in latest versions ? > > max_client_conn =3D 5000 =C2=A0 // can I give this much high value ? > default_pool_size =3D 20 > min_pool_size =3D 10 > reserve_pool_size =3D 10 > reserve_pool_timeout =3D 5 > max_db_connections =3D 100 > max_user_connections =3D 30 > server_lifetime =3D 3600 > server_idle_timeout =3D 600 // is this a low value or need to be=20 > =C2=A0increased ? > [root@pgbouncer ~]# > > > > The config params of =C2=A0N*odeJS application which uses nodejs connec= tion=20 > pooling in*=C2=A0code as follows > > the Node JS application using the following =C2=A0nodejs pooling=20 > configurations for the application level > > *cat app_10072025/config/pg-pool-config.js* > ....... > *const poolOptions =3D { > =C2=A0 =C2=A0 max: 10, > =C2=A0 =C2=A0 min: 2, > =C2=A0 =C2=A0 idleTimeoutMillis: 600000, =C2=A0 //Idle for 5Min > =C2=A0 =C2=A0 connectionTimeoutMillis: 10000, =C2=A0//Reconnect 10sec > =C2=A0 =C2=A0 statement_timeout: 60000, =C2=A0 =C2=A0//Query executiion= 1 min > =C2=A0 =C2=A0 acquire: 20000, > =C2=A0 =C2=A0 maxUses: 1000 //reconnect after 1000 queries > };* > > const pools =3D { > > =C2=A0 =C2=A0 transaction_db: new Pool({ > =C2=A0 =C2=A0 =C2=A0 =C2=A0 connectionString:=20 > `postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME= _TRANSACTION_DB}`, > =C2=A0 =C2=A0 =C2=A0 =C2=A0 ...poolOptions, > =C2=A0 =C2=A0 }), > }; > > .................................. > > Any =C2=A0hints=C2=A0 =C2=A0and suggestions in the config params are mo= st welcome. > > Thank you, > Krishane --------------jeI0jm0XYUK3UsWluB2GUg3k Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


On 7/16/25 08:22, KK CHN wrote:
Hi,

I am facing an issue with PgBouncer-Postgres setup,=C2=A0

PgBouncer (PgBouncer 1.23.1 running on VM instance FreeBSD 14.2) and PostgreSQL DB cluster on a virtual machine (PG version 16.0, =C2=A0RHEL 9.4)=C2=A0. =C2=A0=


My=C2=A0application backend is nodeJS which throws the follow= ing Error in the nodejs=C2=A0 console log: when connecting throug= h pgbouncer to the backend database server...=C2=A0

ERROR thrown:

my-node>=C2=A0Failed to connect to PostgreSQL databa= se transaction_db : unsupported startup parameter: statement_timeout=C2=A0{"code":"08P01", "length":69, "name": "error", xx xx
my-node> error:[object Object ] {"timestamp":"15-07-2025 10:14:26"} x x xx


Note:=C2=A0 =C2=A0The nodejs apps =C2=A0earlier directly esta= blishing connections to the =C2=A0PostgreSQL DB ( no such errors) =C2=A0= now redirected via =C2=A0 Pgbouncer throws this error.

How? I am getting , connecting directly to postgresql, or pgpool, or pgbouncer :

achill@smadev= nu:~ % psql "postgresql://localhost:5432?statement_timeout=3D10"
psql: error: invalid URI query parameter: "statement_timeout"
achill@smadevnu:~ % psql "postgresql://localhost:9999?statement_timeout=3D10"
psql: error: invalid URI query parameter: "statement_timeout"
achill@smadevnu:~ % psql "postgresql://localhost:6432?statement_timeout=3D10"
psql: error: invalid URI query parameter: "statement_timeout"

the error is within : src/interface= s/libpq/fe-connect.c

=C2=A0 =C2=A0

The config parameters for =C2=A0pgbouncer as follows(corre= ct me if any mistakes made)

[root@pgbouncer ~]# grep ^[^';;;'] /usr/local/etc/pgbouncer.ini
[databases]
transaction_db =3D host=3Ddbmain.mydomain.= com=C2=A0port=3D5444 dbname=3Dtransaction_db

[users]
[pgbouncer]
Logfile =3D /var/log/pgbouncer/pgbouncer.log
pidfile =3D /var/run/pgbouncer/pgbouncer.pid
listen_addr =3D *
listen_port =3D 5444
auth_type =3D md5
auth_file =3D /usr/local/etc/pgbouncer.users
admin_users =3D adminuser
stats_users =3D adminuser,
pool_mode =3D transaction
server_reset_query =3D DISCARD ALL
server_reset_query_always =3D 1
ignore_startup_parameters =3D extra_float_digits =C2=A0// I h= ave added this also or can I add these options too in the following line =C2=A0?
;; ignore_startup_parameters =3D extra_float_digits, options, statement_timeout, idle_in_transaction_session_timeout =C2=A0= // doubt options supported in latest versions ?

max_client_conn =3D 5000 =C2=A0 // can I give this much high = value ?
default_pool_size =3D 20
min_pool_size =3D 10
reserve_pool_size =3D 10
reserve_pool_timeout =3D 5
max_db_connections =3D 100
max_user_connections =3D 30
server_lifetime =3D 3600
server_idle_timeout =3D 600 // is this a low value or need to be =C2=A0increased ?
[root@pgbouncer ~]#



The config params of =C2=A0NodeJS application which uses nodejs connection pooling in=C2=A0code as follows

the Node JS application using the following =C2=A0nodejs pool= ing configurations for the application level

cat app_10072025/config/pg-pool-config.js
.......
const poolOptions =3D {
=C2=A0 =C2=A0 max: 10,
=C2=A0 =C2=A0 min: 2,
=C2=A0 =C2=A0 idleTimeoutMillis: 600000, =C2=A0 //Idle for = 5Min
=C2=A0 =C2=A0 connectionTimeoutMillis: 10000, =C2=A0//Recon= nect 10sec
=C2=A0 =C2=A0 statement_timeout: 60000, =C2=A0 =C2=A0//Quer= y executiion 1 min
=C2=A0 =C2=A0 acquire: 20000,
=C2=A0 =C2=A0 maxUses: 1000 //reconnect after 1000 queries<= br> };

const pools =3D {
=C2=A0 =C2=A0
=C2=A0 =C2=A0 transaction_db: new Pool({
=C2=A0 =C2=A0 =C2=A0 =C2=A0 connectionString: `postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME_T= RANSACTION_DB}`,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ...poolOptions,
=C2=A0 =C2=A0 }),
};

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

Any =C2=A0hints=C2=A0 =C2=A0and suggestions in the config par= ams are most welcome.=C2=A0

Thank you,
Krishane
--------------jeI0jm0XYUK3UsWluB2GUg3k--