Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ixZYF-0008SN-EJ for pgsql-bugs@arkaria.postgresql.org; Fri, 31 Jan 2020 16:53:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ixZXF-0002mV-JN for pgsql-bugs@arkaria.postgresql.org; Fri, 31 Jan 2020 16:52:49 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ixYd8-0005X6-1m for pgsql-bugs@lists.postgresql.org; Fri, 31 Jan 2020 15:54:50 +0000 Received: from sonic303-21.consmr.mail.ne1.yahoo.com ([66.163.188.147]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ixYd5-00053Q-CV for pgsql-bugs@lists.postgresql.org; Fri, 31 Jan 2020 15:54:49 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1580486082; bh=/2SI9DnvFeBplWD9WbQdHYJ4f+U8HTPJWNK1uK+MiS8=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=IaATkpPpqmQHMQ1gn1kbWI9pilGXB3+ceivpI7K1/o/dsw0uApNlfVVLpBqNYP70U/in3sDKLABhLQOCskQ8cxtAIyYdzQLW7savrZKhvUAnBl1bKu100Vh+2JyxnEnQjmkV3jAGArUGK/fg47IcsNLTY/UjP80zheQ6vOBDS/Zk6JUC0NvgXne+wjUeCVcom/Sw+khO5Jqp9F0wxLV5fS3+hlv0ykfRUadm5hErWS9twKS0qZcelr4iHCk+gDMfgssFWmyyoIX4OBa8NUu+WwD34DsHsu3icef0rpWzdit5o/8567WSlyZW/N9u3ZmuJ3UhQbO2qpRlWCGX7LMecA== X-YMail-OSG: 8BddgIUVM1nQ3qsdbxdMdmyDbUZHKtDk52VPMtekCwmGPjR88Uxlvsv258dgHgG OgRyIMiCr0uFxz8REJR5J1VcbBZEWlHPMKAkod06DuK_yCPVLfp9xBuR5Xy8rfe6izQgKBInTaUo xvfO9lf02XfRva.WhFeteA6tqmMzSasTidS_3prZWZlxkMpgC1NiZHGCvOuFZr4j_CHUGw_Lhhp_ XIdaKrRL3mbmg2jFGZnVvpMIy2aM.LRJajE5EnD_CZYAW_jPKTRv8vO4DZ3OEPgM0GuGOex6irVT ckQ6cmeSH8bUkYACanr0g1nxtTw4deMI6u8JmEFdc0pJitGMr_pljlJlh5lWnamKjdeydYkJ.6XL zbjEE0aucoU5dOvC31feCg.mlg.YL7gctYqu3j40EfjTxwUhJlz1jaf7p9btMSitN0GHmiOzT3LS AWqXLKZZ7VSauK78yZMqDA5rKyx.Y_QjYU2ulx7AsARNbvsPHtb3Q65HHHm6VPUlioXRevLksEif wz7zo.lOIykUGipUKI9E11iOjMngD5FKHeN5_3KMXTvtUvD9O7yD55ZUq0j780OvJ.qopHnDHoOv e7Z4XnwsBoptjhW1ko5tu7Em3xYudvAudgKju1kGO2jBpnaDvo7biSog4VVVm_YvMv4QArZhye.E nB_zWbMX_oNSbY9WIw0uYSiLH7p7Q5e7FPs9OrZAFESzrpoKJRO2kbqG774wolyt_xJcx0FeLBC3 Oug_HKhNBZUDW3GyrGBx.Gx2SDMfvv00IJqvjJC6ny5CJWYkSDFUK5rChjMopIYyGO5jGRK7iLob 5jLaiDbDi4MH9Uhw4QIoL7iaK_qRGF7g_RV9AYtPV4i6A0bUscrKtglZ2d3xwje0QX2TGjVpt8DA cTJVgP.._Qq6y21MW2ZlPUOjzkP_NVhyFzgOatYK80vGMS_02ubdwlxokmLDJ6FZJiSMQHQPPRp1 ZOym37qeJ7CjvCd6zcMyb.4ynkHgrEE3vw4SPUKyFZbwDnsHHnBb3_ZBjYQPOyOhWoxEOTaEiARE q2T2g.EqH6exj_n98KTwSwpGk5WX4.1SYUCsE1Ij60QeumLxFM8CnAGVUXMKZ0Rh_XJRZhy0lwDC MyLxmaz9V_fqHjeU_koaFdsikAjngSj87RoHhsH75KwOXSPmLUc4xpaRwwrS56c3nBP6z4EoPGF_ Ho4SmcVMnnLhw.MowTfCdNVhky9b_Et8XQrmBI2njPTssQt2Ip.7KTwa4yiCt3LATFR5V95NuAhm 6kh6OlCyDX_PFuSkTBiKoyaBYEmjk6Ji5FWr605EFe0CghOvP2YOmmi6D5MOlK4sBp6LAnX3t4WT 5jeNr3Ac9sCyASg72xe4- Received: from sonic.gate.mail.ne1.yahoo.com by sonic303.consmr.mail.ne1.yahoo.com with HTTP; Fri, 31 Jan 2020 15:54:42 +0000 Date: Fri, 31 Jan 2020 15:54:37 +0000 (UTC) From: Ted Liu To: Jeff Janes Cc: "pgsql-bugs@lists.postgresql.org" Message-ID: <1612134292.2041998.1580486077284@mail.yahoo.com> In-Reply-To: References: <1854294856.1773708.1580425497239.ref@mail.yahoo.com> <1854294856.1773708.1580425497239@mail.yahoo.com> Subject: Re: auto_explain logs ERROR: 08P01 for every query using bind variable MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2041997_1969982761.1580486077282" X-Mailer: WebService/1.1.15113 YMailNorrin Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.121 Safari/537.36 Content-Length: 7871 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2041997_1969982761.1580486077282 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Jeff, Thank you for looking into this issue. Our Postgres version is 11.5. The auto_explain settings are=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 name=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | setting------------------------= ------------+---------=C2=A0auto_explain.log_analyze=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| off=C2=A0auto_explain.log_buffers=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0| off=C2=A0auto_explain.log_format=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 | text=C2=A0auto_explain.log_min_duration=C2=A0 =C2= =A0 =C2=A0 | 190000=C2=A0auto_explain.log_nested_statements | off=C2=A0auto= _explain.log_timing=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | on=C2=A0auto= _explain.log_triggers=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | off=C2=A0auto_exp= lain.log_verbose=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| off=C2=A0auto_ex= plain.sample_rate=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 1(9 rows) The client is java using jdbc.=C2=A0 Thanks, Ted On Friday, January 31, 2020, 9:52:51 AM EST, Jeff Janes wrote: =20 =20 On Fri, Jan 31, 2020 at 2:14 AM Ted Liu wrote: To whom it may concern, For performance and security, most of our queries use bind variables. We al= so implemented auto_explain=C2=A0for tracking down un-optimized queries. Ou= r database supports a large application. During the work-hours, there are 3= 0,000+ queries executed every hour. PostgreSQL=C2=A0logs rotates every hour= and each log contains 30,000+ error message like below. 2020-01-27 00:00:03 UTC:xxx.xx.0.189(35556):xxxxxx@xxxxxxdb:[130756]:ERROR:= =C2=A0 ERROR: 08P01: bind message supplies 0 parameters, but prepared state= ment "..." requires 1=C2=A02020-01-27 00:00:03 UTC:xxx.xx.0.189(35558):xxxx= xx@xxxxxxdb:[130756]:LOCATION:=C2=A0 exec_bind_message, postgres.c:16012020= -01-27 00:00:03 UTC:xxx.xx.0.189(35558):xxxxxx@xxxxxxdb:[130756]:STATEMENT:= =C2=A0 EXPLAIN select ...,...,..,... from ... where ....=3D$1 These error messages are really=C2=A0overwhelming to us and they are not us= eful at all. In our opinion, these error messages should not be logged.=C2= =A0 I agree they shouldn't be logged.=C2=A0 And I don't get such messages when = using auto_explain with something like `pgbench -M prepared`. What version are you using?=C2=A0 What settings are you using for auto_expl= ain?=C2=A0 What is your client language/driver?=C2=A0 Maybe the client is d= oing something weird like sending duplicate binds? Cheers, Jeff =20 ------=_Part_2041997_1969982761.1580486077282 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Jeff,

T= hank you for looking into this issue.

Our Postgres versio= n is 11.5. The auto_explain settings are 

=                 name    &= nbsp;           | setting
--------------= ----------------------+---------
 auto_explain.log_analyze&n= bsp;          | off
 auto_explain.l= og_buffers           | off
 au= to_explain.log_format            | text
=
 auto_explain.log_min_duration      | 190000
=
 auto_explain.log_nested_statements | off
 auto_ex= plain.log_timing            | on
&n= bsp;auto_explain.log_triggers          | off
=
 auto_explain.log_verbose           = ;| off
 auto_explain.sample_rate        =    | 1
(9 rows)

The client is= java using jdbc. 

Thanks,

Ted

=20
=20
On Friday, January 31, 2020, 9:52:51 AM EST, Jeff Janes= <jeff.janes@gmail.com> wrote:


On Fri, Jan 31, 2020 at 2:14 AM T= ed Liu <ted_liu@yahoo.com> wrote:
<= /div>
To whom it may concern,

For performance and security= , most of our queries use bind variables. We also implemented auto_explain&= nbsp;for tracking down un-optimized queries. Our database supports a = large application. During the work-hours, there are 30,000+ queries execute= d every hour. PostgreSQL logs rotates every hour and each log contains= 30,000+ error message like below.

2020-01-27 00:00:03 = UTC:xxx.xx.0.189(35556):xxxxxx@xxxxxxdb:[130756]:ERROR:  ERROR: 08P01: bind message supplies 0 parameters= , but prepared statement "..." requires 1 
202= 0-01-27 00:00:03 UTC:xxx.xx.0.189(35558):xxxxxx@xxxxxxdb:[130756]:LOCATION:=   exec_bind_message, postgres.c:1601
2020-01-27 00:00:03 UTC= :xxx.xx.0.189(35558):xxxxxx@xxxxxxdb:[130756]:STATEMENT:  EXPLAIN sele= ct ...,...,..,... from ... where ....=3D$1

These error messages are really overwh= elming to us and they are not useful at all. In our opinion, these error me= ssages should not be logged. 

I agree they shouldn't be logge= d.  And I don't get such messages when using auto_explain with somethi= ng like `pgbench -M prepared`.

What= version are you using?  What settings are you using for auto_explain?=   What is your client language/driver?  Maybe the client is doing= something weird like sending duplicate binds?

Cheers,

Jeff
------=_Part_2041997_1969982761.1580486077282--