Re: pg_stat_activity.waiting_start

Joel Jacobson <joel(at)trustly(dot)com> writes:
> Maybe a good tradeoff then would be to let "wait_start" represent the
> very first time the txn started waiting?
> ...
> As long as the documentation is clear on "wait_start" meaning when the
> first wait started in the txn, I think that's useful enough to improve
> the situation, as one could then ask a query like "select all
> processes that have possibly been waiting for at least 5 seconds",
> which you cannot do today.

Meh. You *can* do that now: query pg_stat_activity for waiting processes,
wait a couple seconds, query again, intersect the results. I think really
the only useful improvement on that would be to be able to tell that the
process has been blocked continuously for X seconds, and what you're
proposing here won't do that.

In practice, there should never be waits on LWLocks (much less spinlocks)
that exceed order-of-milliseconds; if there are, either we chose the wrong
lock type or the system is pretty broken in general. So maybe it's
sufficient if we provide a wait start time for heavyweight locks ...
though that still seems kind of ugly. (Also, I don't recall the existing
code factorization there, but getting the start time into pg_stat_activity
without an extra gettimeofday call might be hard. As I said, there is
one being done, but I'm not sure how accessible its result is.)

I did a bit more research over the weekend into the cost of gettimeofday
and clock_gettime, and have some new results that I'll post into that
thread shortly. But the short answer is still "they're damn expensive
on some platforms, and not really cheap anywhere".

regards, tom lane