pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedauto_explain logs ERROR: 08P01 for every query using bind variable
4+ messages / 3 participants
[nested] [flat]
* auto_explain logs ERROR: 08P01 for every query using bind variable
@ 2020-01-30 23:04 Ted Liu <ted_liu@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ted Liu @ 2020-01-30 23:04 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
To whom it may concern,
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:16012020-01-27 00:00:03 UTC:xxx.xx.0.189(35558):xxxxxx@xxxxxxdb:[130756]:STATEMENT: EXPLAIN select ...,...,..,... from ... where ....=$1
These error messages are really overwhelming to us and they are not useful at all. In our opinion, these error messages should not be logged.
Best Regards,
Ted Liu
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto_explain logs ERROR: 08P01 for every query using bind variable
@ 2020-01-31 14:52 Jeff Janes <jeff.janes@gmail.com>
parent: Ted Liu <ted_liu@yahoo.com>
0 siblings, 2 replies; 4+ messages in thread
From: Jeff Janes @ 2020-01-31 14:52 UTC (permalink / raw)
To: Ted Liu <ted_liu@yahoo.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Fri, Jan 31, 2020 at 2:14 AM Ted Liu <ted_liu@yahoo.com> wrote:
> To whom it may concern,
>
> 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
>
> These error messages are really overwhelming to us and they are not
> useful at all. In our opinion, these error messages should not be logged.
>
I agree they shouldn't be logged. And I don't get such messages when using
auto_explain with something 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto_explain logs ERROR: 08P01 for every query using bind variable
@ 2020-01-31 15:54 Ted Liu <ted_liu@yahoo.com>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Ted Liu @ 2020-01-31 15:54 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi Jeff,
Thank you for looking into this issue.
Our Postgres version is 11.5. The auto_explain settings are
name | setting------------------------------------+--------- auto_explain.log_analyze | off auto_explain.log_buffers | off auto_explain.log_format | text auto_explain.log_min_duration | 190000 auto_explain.log_nested_statements | off auto_explain.log_timing | on 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
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 Ted Liu <ted_liu@yahoo.com> wrote:
To whom it may concern,
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:16012020-01-27 00:00:03 UTC:xxx.xx.0.189(35558):xxxxxx@xxxxxxdb:[130756]:STATEMENT: EXPLAIN select ...,...,..,... from ... where ....=$1
These error messages are really overwhelming to us and they are not useful at all. In our opinion, these error messages should not be logged.
I agree they shouldn't be logged. And I don't get such messages when using auto_explain with something 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: auto_explain logs ERROR: 08P01 for every query using bind variable
@ 2020-01-31 17:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2020-01-31 17:11 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Ted Liu <ted_liu@yahoo.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-01-31 17:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-30 23:04 auto_explain logs ERROR: 08P01 for every query using bind variable Ted Liu <ted_liu@yahoo.com>
2020-01-31 14:52 ` Jeff Janes <jeff.janes@gmail.com>
2020-01-31 15:54 ` Ted Liu <ted_liu@yahoo.com>
2020-01-31 17:11 ` Tom Lane <tgl@sss.pgh.pa.us>
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