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
