Split out of #350, where surfacing the client IP / hostname for a query turned out not to be achievable without a collector change.
Problem
client_addr and client_hostname are only available in pg_stat_activity, but our probe does not collect query_id, and metrics.pg_stat_activity has no such column. There is therefore no join key between activity rows and metrics.pg_stat_statements, so a client address cannot be attributed to a query id. Matching on query text instead would be unreliable and could attribute the wrong client, so it was rejected.
Proposed change
- Add
query_id to the SELECT in collector/src/probes/pg_stat_activity_probe.go.
- Add a
query_id BIGINT column to metrics.pg_stat_activity via a new schema migration (next free version is 8; see the migration pattern in collector/src/database/schema.go).
- Join
activity.query_id = statements.queryid to surface the client address in the query drill-down.
Caveats worth documenting
pg_stat_activity.query_id exists only on PostgreSQL 14 and later, and is populated only when compute_query_id is enabled, so the probe needs a version and setting guard with a graceful fallback on older or unconfigured servers.
Even once collected, the association is inherently best-effort: pg_stat_activity is a point-in-time view, so a client address is only ever captured for statements that happened to be in flight when a snapshot was taken. The UI should present this as "last observed client" rather than implying it is the only client that ran the query.
Split out of #350, where surfacing the client IP / hostname for a query turned out not to be achievable without a collector change.
Problem
client_addrandclient_hostnameare only available inpg_stat_activity, but our probe does not collectquery_id, andmetrics.pg_stat_activityhas no such column. There is therefore no join key between activity rows andmetrics.pg_stat_statements, so a client address cannot be attributed to a query id. Matching on query text instead would be unreliable and could attribute the wrong client, so it was rejected.Proposed change
query_idto theSELECTincollector/src/probes/pg_stat_activity_probe.go.query_id BIGINTcolumn tometrics.pg_stat_activityvia a new schema migration (next free version is 8; see the migration pattern incollector/src/database/schema.go).activity.query_id = statements.queryidto surface the client address in the query drill-down.Caveats worth documenting
pg_stat_activity.query_idexists only on PostgreSQL 14 and later, and is populated only whencompute_query_idis enabled, so the probe needs a version and setting guard with a graceful fallback on older or unconfigured servers.Even once collected, the association is inherently best-effort:
pg_stat_activityis a point-in-time view, so a client address is only ever captured for statements that happened to be in flight when a snapshot was taken. The UI should present this as "last observed client" rather than implying it is the only client that ran the query.