From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Ted Liu <ted_liu@yahoo.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: auto_explain logs ERROR: 08P01 for every query using bind variable
Date: Fri, 31 Jan 2020 12:11:19 -0500
Message-ID: <1968.1580490679@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMkU=1y7YWi4CYjou0hAw_6B7zRtQT9X4dYeJBjoR+aYYoTDRw@mail.gmail.com>
References: <1854294856.1773708.1580425497239.ref@mail.yahoo.com>
<1854294856.1773708.1580425497239@mail.yahoo.com>
<CAMkU=1y7YWi4CYjou0hAw_6B7zRtQT9X4dYeJBjoR+aYYoTDRw@mail.gmail.com>
Jeff Janes <jeff.janes@gmail.com> writes:
> On Fri, Jan 31, 2020 at 2:14 AM Ted Liu <ted_liu@yahoo.com> wrote:
>> For performance and security, most of our queries use bind variables. We
>> also implemented auto_explain for tracking down un-optimized queries. Our
>> database supports a large application. During the work-hours, there are
>> 30,000+ queries executed 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
>> 2020-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 select ...,...,..,... from ... where ....=$1
> I agree they shouldn't be logged. And I don't get such messages when using
> auto_explain with something like `pgbench -M prepared`.
Surely these are reporting a genuine client-side logic error. You
need to fix your application, not complain about the fact that we're
reporting that it's broken. (Note that auto_explain does NOT issue
"EXPLAIN" commands; it works at a lower level than that. These
EXPLAIN commands are coming from a client application.)
If you really don't care to get log messages about incorrect queries,
you could always set log_min_messages higher than ERROR ...
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jeff.janes@gmail.com, ted_liu@yahoo.com
Subject: Re: auto_explain logs ERROR: 08P01 for every query using bind variable
In-Reply-To: <1968.1580490679@sss.pgh.pa.us>
References: <1854294856.1773708.1580425497239.ref@mail.yahoo.com> <1854294856.1773708.1580425497239@mail.yahoo.com> <CAMkU=1y7YWi4CYjou0hAw_6B7zRtQT9X4dYeJBjoR+aYYoTDRw@mail.gmail.com> <1968.1580490679@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox