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

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 .

Cheers,
Kev

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> , 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 J.
> 

"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, 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.