Re: Increasing the length of

Great idea. Added to TODO:

* Make log_min_duration_statement output when the duration is reached rather
than when the statement completes

This prints long queries while they are running, making trouble shooting
easier. Also, it eliminates the need for log_statement because it
would now be the same as a log_min_duration_statement of zero.

---------------------------------------------------------------------------

Simon Riggs wrote:
> On Wed, 2004-11-10 at 22:51, Andrew Sullivan wrote:
> > On Wed, Nov 10, 2004 at 09:52:17PM +0000, Simon Riggs wrote:
> > > On Wed, 2004-11-10 at 21:48, Richard Huxton wrote:
> > > >
> > > > Isn't that:
> > > > log_min_duration_statement (integer)
> > >
> > > That gets written when a statement completes, not during execution.
> >
> > I've been following this thread, and I was thinking the same thing.
> > I wonder how much work it'd be to have another log setting -- say
> > log_statement_after_min_duration (integer) -- which did what Simon
> > wants. That'd more than satisfy my need, for sure. Might the cost
> > of that be too high, though?
>
> I think this is a great idea.
>
> ...Rather than invent a new GUC, I think this is the solution:
>
> log_min_duration_statement writes to log at end of execution, if
> execution time is greater than that threshold. Let's move that piece of
> code so it is executed as the query progresses. That way, you get
> notified that a problem query is occurring NOW, rather than "it has
> occurred".
>
> The code already has such a timer check, for statement_timeout, in
> backend/storage/lmgr/proc.c. We could reuse this timer to go off at
> log_min_duration_statement and then log query if still executing. (If
> log_min_duration_statement >= statement_timeout, we would skip that
> step.) We would then reset the timer so that it then goes off at where
> it does now, at statement_timeout. So, same piece of code, used twice...
>
> That way you can set up 2 limits, with three bands of actions:
>
> Between 0 and log_min_duration_statement
> - logs nothing
>
> Between log_min_duration_statement and statement_timeout
> - statement written to log, though execution continues...
>
> At statement_timeout
> - statement cancelled
>
> We'd just need a small piece of code to set timer correctly first, then
> another piece to record state change and reset timer again. Lift and
> drop the existing code from end-of-execution.
>
> This then:
> - solves the *problem query* diagnosis issue, as originally raised by
> Sean and seconded by myself and Greg
> - makes the answer exactly what Tom proposed - look in the logs
> - doesn't make any changes to the technical innards of UDP and pgstats.c
> - no administrative interface changes, just slightly changed behaviour -
> existing users mostly wouldn't even notice we'd done it...
>
> Thoughts?
>
> Easy enough change to be included as a hot fix for 8.0: no new system
> code, no new interface code, just same behaviour at different time.
>
>
> --
> Best Regards, Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073