Re: System username in pg_stat_activity

Hi,

On 2024-02-16 15:22:16 -0500, Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > I mean, we could split it into more than one view. But adding a new
> > view for every new thing we want to show is also not very good from
> > either a usability or performance perspective. So where would we put
> > it?
>
> It'd have to be a new view with a row per session, showing static
> (or at least mostly static?) properties of the session.

Yep.

> Could we move some existing fields of pg_stat_activity into such a
> view?

I'd suspect that at least some of
- leader_pid
- datid
- datname
- usesysid
- usename
- backend_start
- client_addr
- client_hostname
- client_port
- backend_type

could be moved. Whether's worth breaking existing queries, I don't quite know.

One option would be to not return (some) of them from pg_stat_get_activity(),
but add them to the view in a way that the planner can elide the reference.

> I'm not sure that this is worth the trouble TBH. If it can be shown
> that pulling a few fields out of pg_stat_activity actually does make
> for a useful speedup, then maybe OK ... but Andres hasn't provided
> any evidence that there's a measurable issue.

If I thought that the two columns proposed here were all that we wanted to
add, I'd not be worried. But there have been quite a few other fields
proposed, e.g. tracking idle/active time on a per-connection granularity.

We even already have a patch to add pg_stat_session
https://commitfest.postgresql.org/47/3405/

Greetings,

Andres Freund