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 (and...@anarazel.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
