Re: Addition of authenticated ID to pg_stat_activity

On Thu, Apr 29, 2021 at 04:56:42PM +0200, Magnus Hagander wrote:
> I definitely use it all the time to monitor autovacuum all the time.
> The others as well regularly, but autovacuum continuously. I also see
> a lot of people doing things like "from pg_stat_activity where query
> like '%mytablename%'" where they'd want both any regular queries and
> any autovacuums currently processing the table.

When it comes to development work, I also look at things different
than backend connections, checkpointer and WAL writer included.

> I'd say client address is also pretty common to identify which set of
> app servers connections are coming in from -- but client port and
> client hostname are a lot less interesting. But it'd be kind of weird
> to split those out.

Yes, I agree that it would be confusing to split the client_* fields
across multiple views.

> For *interactive use* I'd find pretty much all other columns
> interesting and commonly used. Probably not that interested in the
> oids of the database and user, but again they are the cheap ones. We
> could get rid of the joints if we only showed the oids, but in
> interactive use it's really the names that are interesting. But if
> we're just trying to save column count, I'd say get rid of datid and
> usesysid.
>
> I'd hold everything else as interesting.

Yes, you have an argument here about the removal of usesysid and
datid. Now I find joins involving OIDs to be much more natural than
the object names, because that's the base of what we use in the
catalogs.

Not sure if we would be able to agree on something here, but the
barrier to what a session and a connection hold is thin when it comes
to roles and application_name. Thinking more about that, I would be
really tempted to get to do a more straight split with data that's
associated to a session, to a transaction and to a connection, say:
1) pg_stat_session, data that may change.
- PID
- leader PID
- the role name
- role ID
- application_name
- wait_event_type
- wait_event
2) pg_stat_connection, static data associated to a connection.
- PID
- database name
- database OID
- client_addr
- client_hostname
- client_port
- backend_start
- authn ID
- backend_type
3) pg_stat_transaction, or pg_stat_activity, for the transactional
activity.
- PID
- xact_start
- query_start
- backend_xid
- state_change
- query string
- query ID
- state

Or I could just drop a new function that fetches the authn ID for a
given PID, even if this makes things potentially less consistent when
it comes to the lookup of PgBackendStatus, guarantee given now by
pg_stat_get_activity().
--
Michael