PFC <lists@peufeu.com> writes:
> So, the proposal :
> On executing a command, Backend stores the command string, then
> overwrites the counter with (counter + 1) and with the timestamp of
> command start.
> Periodically, like every N seconds, a separate process reads the counter,
> then reads the data, then reads the counter again.
Well, it wouldn't be "periodic", it'd be "whenever someone reads
pg_stat_activity". I was considering solutions like this, but I'm a
little disturbed by the possibility that the would-be reader might
loop indefinitely if the source backend is constantly changing its
entry. Still, slow reads of pg_stat_activity might be a good tradeoff
for taking overhead out of the update operation.
BTW, I think the writer would actually need to bump the counter twice,
once before and once after it modifies its stats area. Else there's
no way to detect that you've copied a partially-updated stats entry.
> If the backend process itself should update its process title, and this
> operation is costly, it should only be done if the current query has been
> running for more than T seconds.
Managing that would cost more than just doing it, I think.
regards, tom lane