Right now, I need a way to log the lock wait times for certain queries,
to see if they are acceptable. DTrace is not available.
I'm dealing with a problem that is either a standard lock wait involving
RI trigger locking, or a weirder problem involving lock starvation as a
result of soft deadlock queue re-arrangement. I doubt its the latter but
neither situation can be straightforwardly enabled to diagnose the
situation.
Sometime earlier, I proposed log_min_lockwait.
A frequent case is where the lockwait is so long that the statement
effectively never completes, so the statement duration doesn't appear in
the log. That can cause further diagnosis problems.
Ideally, I'd like to know both
1. there is a lock wait and it is happening now
2. there was a lock wait and it was THIS bad
For 1, I need to log something while the lock wait is happening, not
just when it ends - it might not end successfully. For 2 we can log it
during or afterwards, not a problem.
I'm thinking to write an INFO message, so that people can choose to log
this and/or the SQL statement if they choose.
e.g. INFO: lock wait time of XXX secs has been exceeded
Comments?
-- Simon Riggs EnterpriseDB http://www.enterprisedb.com