Currently, when we set a statement_timeout and a query runs over that time there is no log message to say that the statement has timed out. We do get a message which says ERROR: canceling query due to user request and so in the server log it is impossible to tell the difference between a query that has been cancelled deliberately by the user and a query whose time(out) has come.
The timeout causes the query to be cancelled, which is an ERROR, so it is possible that the query will be logged if log_min_error_statement is set to ERROR or below. Again, there is no way to tell the difference between an ERROR statement that has occurred during execution and an ERROR statement that is generated by a syntax error. So, if you do set log_min_error_statement=ERROR then you get swamped by syntax errors rather than being able to see statement timeouts. Logging these queries ought to be a valuable source of tuning information, but as a result of the above, this is not currently the case. For now, I've written a quick patch to the log the query text with a suitable error message. I expect some debate as to whether the line if (log_min_error_statement > WARNING) should be there at all, so comments are sought. --- Longer term, we might handle this differently. If we differentiate between two types of ERROR, at least for log_min_error_statement... - one that prevents the query from ever running (syntax, parse errors) - one that occurs because the query itself gets into difficulty, but not so badly that it constitutes a FATAL error. Lets call it RUNERROR for now. I expect most people to want to log RUNERRORs, but to want to ignore the more normal kind of ERRORs. When/if we can differentiate between levels, I'll reverse out this patch since we'll be able to handle it simply with the new errorlevel. Best Regards, Simon Riggs
Index: src/backend/storage/lmgr/proc.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v retrieving revision 1.163 diff -c -c -r1.163 proc.c *** src/backend/storage/lmgr/proc.c 20 Aug 2005 23:26:24 -0000 1.163 --- src/backend/storage/lmgr/proc.c 9 Sep 2005 11:28:39 -0000 *************** *** 51,57 **** #include "storage/proc.h" #include "storage/procarray.h" #include "storage/spin.h" ! /* GUC variables */ int DeadlockTimeout = 1000; --- 51,58 ---- #include "storage/proc.h" #include "storage/procarray.h" #include "storage/spin.h" ! #include "tcop/tcopprot.h" ! #include "utils/elog.h" /* GUC variables */ int DeadlockTimeout = 1000; *************** *** 1179,1184 **** --- 1180,1196 ---- { /* Time to die */ statement_timeout_active = false; + + /* + * Log the statement that has timed out, unless we already have + * log_min_error_statement set low enough that we will log the + * query as part of the handling for the kill() + */ + if (log_min_error_statement > WARNING) + ereport(LOG, + (errmsg("timeout cancels statement: %s", + debug_query_string ? debug_query_string : ""))); + kill(MyProcPid, SIGINT); } else
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly