Content-Type: multipart/alternative;
 boundary="------------Rj0YPAEcGN0zDFLoJ6f21iXL"
Message-ID: <e8c67a24-7178-4f07-be8c-3afd33966ff7@cloud.gatewaynet.com>
Date: Wed, 23 Jul 2025 09:06:24 +0100
MIME-Version: 1.0
Subject: Re: PgBouncer Prepared Statement ERROR
To: KK CHN <kkchn.in@gmail.com>, pgsql-general@lists.postgresql.org
References: <CAKgGyB8PvfvuSMEBLhTCHiJphouKS84-BQSg1iD9dWkzmiXdJQ@mail.gmail.com>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <CAKgGyB8PvfvuSMEBLhTCHiJphouKS84-BQSg1iD9dWkzmiXdJQ@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/e8c67a24-7178-4f07-be8c-3afd33966ff7%40cloud.gatewaynet.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------Rj0YPAEcGN0zDFLoJ6f21iXL
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 7/18/25 13:52, KK CHN wrote:

> Hi ,
>
> I am getting error when using  PgBouncer(1.23.1 ) with Postgres 16  
> (RedHAT 9.4)
>
> 2025-07-18 00:00:00 IST ERROR:  prepared statement "S_243" does not exist
> 2025-07-18 00:00:03 IST ERROR:  prepared statement "S_205" does not exist
> 2025-07-18 00:00:03 IST ERROR:  prepared statement "S_206" does not exist
> 2025-07-18 00:00:03 IST ERROR:  prepared statement "S_207" does not exist
>
>
> How to avoid these prepared statement Errors ?
>
>

Before you start considering prepared statements, via pgbouncer, and 
testing them to see if you benefit from them, you may at first disable 
them all together.

Just set prepareThreshold=0 in the connection url, or params.


> Any hints most welcome,
> Krishane
>
>
> config as follows
> [root@pgbouncer ~]# grep ^[^';;;']  /usr/local/etc/pgbouncer.ini
> [databases]
> transaction_db = host=dbmain.erss.in <http://dbmain.erss.in> port=5444 
> dbname= transaction_db
> mgt_db = host=dbmain.erss.in <http://dbmain.erss.in> port=5444 
> dbname=mgt_db
> ....................................
> tsp_db = host=dbmain.erss.in <http://dbmain.erss.in> port=5444 
> dbname=tsp_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 = myuser
> stats_users =  status_user,
> pool_mode = transaction
> server_reset_query = DISCARD ALL
> server_reset_query_always = 1
> ignore_startup_parameters = extra_float_digits, options, 
> statement_timeout, idle_in_transaction_session_timeout
> max_client_conn = 5000
> default_pool_size = 20
> min_pool_size = 10
> reserve_pool_size = 10
> reserve_pool_timeout = 5
> max_db_connections = 900
> max_user_connections = 800
> server_lifetime = 3600
> server_idle_timeout = 60000
> [root@pgbouncer ~]#
>
--------------Rj0YPAEcGN0zDFLoJ6f21iXL
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>On 7/18/25 13:52, KK CHN wrote:</p>
    <blockquote type="cite"
cite="mid:CAKgGyB8PvfvuSMEBLhTCHiJphouKS84-BQSg1iD9dWkzmiXdJQ@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hi ,<br>
        <div><br>
        </div>
        <div>I am getting error when using  PgBouncer(1.23.1 ) with 
          Postgres 16  (RedHAT 9.4)</div>
        <div><br>
        </div>
        <div>2025-07-18 00:00:00 IST ERROR:  prepared statement "S_243"
          does not exist<br>
          2025-07-18 00:00:03 IST ERROR:  prepared statement "S_205"
          does not exist<br>
          2025-07-18 00:00:03 IST ERROR:  prepared statement "S_206"
          does not exist<br>
          2025-07-18 00:00:03 IST ERROR:  prepared statement "S_207"
          does not exist<br>
          <br>
        </div>
        <div><br>
        </div>
        <div>How to avoid these prepared statement Errors ?   </div>
        <div><br>
        </div>
        <div><br>
        </div>
      </div>
    </blockquote>
    <p><br>
    </p>
    <p>Before you start considering prepared statements, via pgbouncer,
      and testing them to see if you benefit from them, you may at first
      disable them all together.</p>
    <p>Just set <span style="font-family:monospace"><span
          style="color:#000000;background-color:#ffffff;">pr</span>epare<span
          style="color:#000000;background-color:#ffffff;">Threshold=0 </span>in
        the connection url, or params.<br>
      </span></p>
    <p><br>
    </p>
    <blockquote type="cite"
cite="mid:CAKgGyB8PvfvuSMEBLhTCHiJphouKS84-BQSg1iD9dWkzmiXdJQ@mail.gmail.com">
      <div dir="ltr">
        <div>Any hints most welcome,</div>
        <div>Krishane</div>
        <div><br>
        </div>
        <div> </div>
        <div><br>
        </div>
        <div>config as follows</div>
        <div>[root@pgbouncer ~]# grep ^[^';;;']
           /usr/local/etc/pgbouncer.ini<br>
          [databases]<br>
          transaction_db = host=<a href="http://dbmain.erss.in"
            moz-do-not-send="true">dbmain.erss.in</a> port=5444 dbname=
          transaction_db<br>
          mgt_db = host=<a href="http://dbmain.erss.in"
            moz-do-not-send="true">dbmain.erss.in</a> port=5444
          dbname=mgt_db<br>
          ....................................</div>
        <div>tsp_db = host=<a href="http://dbmain.erss.in"
            moz-do-not-send="true">dbmain.erss.in</a> port=5444
          dbname=tsp_db<br>
          [users]<br>
          [pgbouncer]<br>
          logfile = /var/log/pgbouncer/pgbouncer.log<br>
          pidfile = /var/run/pgbouncer/pgbouncer.pid<br>
          listen_addr = *<br>
          listen_port = 5444<br>
          auth_type = md5<br>
          auth_file = /usr/local/etc/pgbouncer.users<br>
          admin_users = myuser<br>
          stats_users =  status_user,<br>
          pool_mode = transaction<br>
          server_reset_query = DISCARD ALL<br>
          server_reset_query_always = 1<br>
          ignore_startup_parameters = extra_float_digits, options,
          statement_timeout, idle_in_transaction_session_timeout<br>
          max_client_conn = 5000<br>
          default_pool_size = 20<br>
          min_pool_size = 10<br>
          reserve_pool_size = 10<br>
          reserve_pool_timeout = 5<br>
          max_db_connections = 900<br>
          max_user_connections = 800<br>
          server_lifetime = 3600<br>
          server_idle_timeout = 60000<br>
          [root@pgbouncer ~]#<br>
        </div>
        <div><br>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------Rj0YPAEcGN0zDFLoJ6f21iXL--