Re: Addition of authenticated ID to pg_stat_activity

On Tue, Apr 27, 2021 at 09:59:18AM +0900, Michael Paquier wrote:
> On Mon, Apr 26, 2021 at 03:21:46PM -0400, Stephen Frost wrote:
> > * Andres Freund (andres(at)anarazel(dot)de) wrote:
> >> I'm getting a bit worried about the incremental increase in
> >> pg_stat_activity width - it's probably by far the view that's most
> >> viewed interactively. I think we need to be careful not to add too niche
> >> things to it. This is especially true for columns that may be wider.
> >>
> >> It'd be bad for discoverability, but perhaps something like this, that's
> >> not that likely to be used interactively, would be better done as a
> >> separate function that would need to be used explicitly?
> >
> > I mean.. we already have separate functions and views for this, though
> > they're auth-method-specific currently, but also provide more details,
> > since it isn't actually a "one size fits all" kind of thing like this
> > entire approach is imagining it to be.
>
> I am wondering if we should take this as an occasion to move some data
> out of pg_stat_activity into a separate biew, dedicated to the data
> related to the connection that remains set to the same value for the
> duration of a backend's life, as of the following set:
> - the backend PID
> - client_addr
> - client_hostname
> - client_port
> - authenticated ID
> - application_name? (well, this one could change on reload, so I am
> lying).

+backend type
+leader_PID

> It would be tempting to move the database name and the username but
> these are popular fields with monitoring. Maybe we could name that
> pg_stat_connection_status, pg_stat_auth_status or just
> pg_stat_connection?

Maybe - there could also be a trivial view which JOINs pg_stat_activity and
pg_stat_connection ON (pid).

Technically I think it could also move backend_start/backend_xmin, but it'd be
odd to move them if the other timestamp/xid columns stayed in pg_stat_activity.

There's no reason that pg_stat_connection would *have* to be "static" per
connction, right ? That's just how you're defining what would be included.

Stephen wrote:
> Would like to get the info they really want out of that and not anything
> else. If we're going to adjust the fields returned from that then
> that's really the lens we should use.
>
> So, what fields are people really looking at when querying
> pg_stat_activity interactively? User, database, pid, last query,
> transaction start, query start, state, wait event info, maybe backend
> xmin/xid? I doubt most people looking at pg_stat_activity interactively
> actually care about the non-user backends (autovacuum, et al).

I think the narrow/userfacing view would exclude only the OID/XID fields:

datid | oid | | |
usesysid | oid | | |
backend_xid | xid | | |
backend_xmin | xid | | |

I think interactive users *would* care about other backend types - they're
frequently wondering "what's going on?"

TBH, query text is often so long that I have to write left(query,33), and then
the idea of a "userfacing" variant loses its appeal, since it's necessary to
enumerate columns anyway.

--
Justin