maximum for auto_explain.log_min_duration doesn't seem to make sense

On Fri, Feb 23, 2018 at 11:34 AM, Kevin Bloch <kev@codingthat.com> wrote:

According to https://dba.stackexchange.com/a/198429/28774 , this setting
maxes out at INT_MAX / 1000, but since it's never multiplied by 1000 or any
other number, it seems it should perhaps just be INT_MAX

​I suspect that the counter to which that value is being compared also
wants to be an INT and if one checks for "val > INT_MAX" then val cannot be
restricted to an integer (and since we are capturing time we need some
unknown buffer).

As for the post question: What can I do if I want to log even
longer-running queries on a data warehouse?

The answer is "nothing special, anything running longer than the supplied
value will be logged". What you cannot do is choose not to log a subset of
queries that take longer INT_MAX/1,000 and less then infinity - once you
hit INT_MAX/1,000 you must log it.

David J.

On 02/23/2018 08:02 PM, David G. Johnston wrote:

On Fri, Feb 23, 2018 at 11:34 AM, Kevin Bloch <kev@codingthat.com
<mailto:kev@codingthat.com>>wrote:

According to https://dba.stackexchange.com/a/198429/28774
<https://dba.stackexchange.com/a/198429/28774&gt; , this setting maxes
out at INT_MAX / 1000, but since it's never multiplied by 1000 or
any other number, it seems it should perhaps just be INT_MAX

​I suspect that the counter to which that value is being compared also
wants to be an INT and if one checks for "val > INT_MAX" then val cannot
be restricted to an integer (and since we are capturing time we need
some unknown buffer).

The unknown buffer is key here, it seems. Thanks for clarifying.

As for the post question: What can I do if I want to log even
longer-running queries on a data warehouse?

The answer is "nothing special, anything running longer than the
supplied value will be logged".  What you cannot do is choose not to log
a subset of queries that take longer INT_MAX/1,000 and less then
infinity - once you hit INT_MAX/1,000 you must log it.

I had phrased that poorly, but you answered my actual meaning in the
end: A higher minimum isn't possible.

Thanks again,
Kev

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, Feb 23, 2018 at 11:34 AM, Kevin Bloch <kev@codingthat.com> wrote:

According to https://dba.stackexchange.com/a/198429/28774 , this setting
maxes out at INT_MAX / 1000, but since it's never multiplied by 1000 or any
other number, it seems it should perhaps just be INT_MAX

​I suspect that the counter to which that value is being compared also
wants to be an INT and if one checks for "val > INT_MAX" then val cannot be
restricted to an integer (and since we are capturing time we need some
unknown buffer).

No, a look at the code shows that it's being compared to a double.
So there's no practical problem with increasing that GUC's limit to
INT_MAX. As to whether it's worth doing, the existing limit is equivalent
to ~35 minutes if I did the math right. I can barely imagine that anyone
would want to set auto_explain.log_min_duration higher than that, but
maybe in some huge data warehouse environment it'd make sense. Anyway,
I notice that the core backend's log_min_duration GUC, which is also
measured in ms, is allowed to go up to INT_MAX; so it seems a bit
inconsistent that this one isn't.

In short, seems like a valid complaint to me.

regards, tom lane

On Fri, Feb 23, 2018 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, Feb 23, 2018 at 11:34 AM, Kevin Bloch <kev@codingthat.com>

wrote:

According to https://dba.stackexchange.com/a/198429/28774 , this

setting

maxes out at INT_MAX / 1000, but since it's never multiplied by 1000 or

any

other number, it seems it should perhaps just be INT_MAX

​I suspect that the counter to which that value is being compared also
wants to be an INT and if one checks for "val > INT_MAX" then val cannot

be

restricted to an integer (and since we are capturing time we need some
unknown buffer).

In short, seems like a valid complaint to me.

​Yeah, I went back and pulled up the code and came to the same conclusion.

David J.
​

On Fri, 23 Feb 2018 14:25:59 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

INT_MAX. As to whether it's worth doing, the existing limit is equivalent
to ~35 minutes if I did the math right. I can barely imagine that anyone
would want to set auto_explain.log_min_duration higher than that, but
maybe in some huge data warehouse environment it'd make sense.

In short, seems like a valid complaint to me.

A client has several production queries that commonly run an hour or two.
Sometimes queries are modified infelicitously or the planner is lead astray
due to statistics etc and they can run much longer unexpectedly. It would be
useful to set a threshold of a few hours to catch those as reproducing them
is a slow process.

-dg

--
David Gould daveg@sonic.net
If simplicity worked, the world would be overrun with insects.