Thread: further meta-data in pg_stat_activity?

further meta-data in pg_stat_activity?

From
"Andrew Hammond"
Date:
How much work would it be to implement and how valuable would people
find the following additions to pg_stat_activity?

1) # of transactions committed on this connection since start
2) # of transactions rolled back
3) milliseconds used processing requests
4) milliseconds idle in transaction
5) milliseconds idle
6) this is the n'th backend spawned since the postmaster started
7) this is the n'th backend for the given client_addr
8) this is the n'th backend for the given user
9) timestamp for start of the current transaction (null if idle?)

Drew



Re: further meta-data in pg_stat_activity?

From
Tom Lane
Date:
"Andrew Hammond" <andrew.george.hammond@gmail.com> writes:
> How much work would it be to implement and how valuable would people
> find the following additions to pg_stat_activity?

I won't speak to the "how valuable" bit, but as far as costs go,
I think:

> 1) # of transactions committed on this connection since start
> 2) # of transactions rolled back

Trivial, we report these to the stats collector already, they're just
not summed in this particular fashion.

> 3) milliseconds used processing requests
> 4) milliseconds idle in transaction
> 5) milliseconds idle

All moderately expensive, we're talking at least two additional kernel
calls per request to get the information.

> 6) this is the n'th backend spawned since the postmaster started

Cheap on Unix, not so cheap on Windows, usefulness pretty questionable.

> 7) this is the n'th backend for the given client_addr
> 8) this is the n'th backend for the given user

Both *exceedingly* expensive --- where are you going to sum these?
The postmaster does not even have a way to count the second, because
it forks off the subprocess before receiving the connection request
packet which contains the user name.

> 9) timestamp for start of the current transaction (null if idle?)

Don't we do that already?
        regards, tom lane