[YSQL] Request for a view to keep yb_session_info (similar to pg_stat_activity, but keep for longer)

[pdvmoto]

Jira Link: DB-15235

Description

Request if for a view to keep track of Sessions (= client connections), similar to pg_stat_activity, but with longer lifespan. Keeping the session-info for longer will allow for polling+recording of the session and will allow better link-back to ASH records.

Background: When using yb_active_session_history (ASH), there are a lot of PID values that are no longer reflected in pg_stat_activity.

I have created a capturing system for polling data from ASH and from pg_stat_activity (the Session), but the ASH-data contains a lot of pid-values for which the actual connection was never polled from pg_stat_activity bcse the connection only lasted for a short interval.

My suggestion is for a view: yb_session_info, with maximum n-records (configurable) with circular data (new records replace old records as they come). A record in the view should contain a subset of the data form pg_stat_activity, but at minimum: pid, backend_start, datid, usesysid, app_name and client-info: addr+port. The record could be created on connect-time (similar to log-info resulting from the postgres setting log_connections=true).
Optionally, a “disconnect-event" could dump some of the accumulated data from pg_stat_activity into the yb_session_info, but this will only work if there is a regular disconnect (e.g. not a kill/crash/etc).

The main use-case I see is for tracking down high-usage and slow-queries, but I can also envisage this view (and data polled from it) to be used for “auditing” or billing-attribution purposes.

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.