ROX-34356: add pg_stat_activity to diagnostic bundle by dashrews78 · Pull Request #20130 · stackrox/stackrox

dashrews78 · 2026-04-21T15:04:42Z

Description

Support frequently needs pg_stat_activity data when triaging database performance issues (slow queries, connection exhaustion, lock contention) but has to ask customers to run manual queries. This adds it to the diagnostic bundle automatically as central-db-pg-activity.json, following the existing pg_stat_statements/tuple stats pattern in pkg/postgres/stats.

Columns captured:
datname, pid, usename, application_name, backend_start, xact_start, query_start, state_change, wait_event_type, wait_event, state, backend_type, query (truncated to 1000 chars)

External database handling:
No special-casing needed. On external databases with limited privileges, PostgreSQL returns only the application's own sessions — which is the right data for diagnosing StackRox issues. If the view is completely inaccessible, the error is captured in the JSON output and the file is still written to the bundle.

User-facing documentation

CHANGELOG.md is updated OR update is not needed
documentation PR is created and is linked above OR is not needed

Testing and quality

the change is production ready: the change is GA, or otherwise the functionality is gated by a feature flag
CI results are inspected

Automated testing

How I validated my change

Verified both pkg/postgres/stats and central/debug/service compile cleanly
Ran the new integration test against a local PostgreSQL instance:
go test -v -tags sql_integration ./central/debug/service/ \
-run TestDBDiagnostic/TestGetPGStatActivities -count=1
Test passes — confirms the query returns results, finds at least one active connection (the test's own session), and produces no errors
Verified the getCentralDBData code path is exercised in both the full diagnostic bundle (withCentral) and the database-only bundle (withDBOnly) modes
Reviewed query behavior for external databases: pg_stat_activity is accessible to any user for their own sessions without requiring pg_read_all_stats or superuser, so no
permission errors are expected in the common case

coderabbitai · 2026-04-21T15:08:53Z

📝 Walkthrough

Walkthrough

Introduces PostgreSQL active session statistics collection to the debug service. A new GetPGStatActivities function queries pg_stat_activity and returns session data. This function is integrated into the central database diagnostics collection process, with accompanying test coverage.

Changes

Cohort / File(s)	Summary
PostgreSQL Statistics Models & Query `pkg/postgres/stats/stats.go`	Added `PGStatActivity` and `PGStatActivities` types; implemented `GetPGStatActivities` to query `pg_stat_activity` with parameterized limit, filter by non-NULL state, order by query start time, and truncate queries to 1000 characters.
Debug Service Integration `central/debug/service/service.go`	Modified `getCentralDBData` to call `stats.GetPGStatActivities`, conditionally log activity errors, and write activity JSON to zip file as `central-db-pg-activity.json`. Also propagates error from `central-db-pg-tuples.json` write.
Integration Test `central/debug/service/db_diagnostics_test.go`	Added `TestGetPGStatActivities` test case verifying function returns active session records without errors and contains at least one entry with "active" state.

Sequence Diagram(s)

sequenceDiagram
    participant Client
    participant DebugService as Debug Service
    participant StatsModule as Stats Module
    participant DB as PostgreSQL

    Client->>DebugService: getCentralDBData()
    DebugService->>StatsModule: GetPGStatActivities(ctx, db, limit)
    StatsModule->>DB: Query pg_stat_activity
    DB-->>StatsModule: Active session records
    StatsModule-->>DebugService: PGStatActivities{records, error}
    Note over DebugService: Marshal to JSON
    DebugService->>DebugService: Write to zip
    DebugService-->>Client: Debug data bundle

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~28 minutes

🚥 Pre-merge checks | ✅ 4 | ❌ 1

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Docstring Coverage	⚠️ Warning	Docstring coverage is 50.00% which is insufficient. The required threshold is 80.00%.	Write docstrings for the functions missing them to satisfy the coverage threshold.

✅ Passed checks (4 passed)

Check name	Status	Explanation
Description check	✅ Passed	The description provides detailed context about the feature, columns captured, external database handling, and comprehensive validation steps performed.
Linked Issues check	✅ Passed	Check skipped because no linked issues were found for this pull request.
Out of Scope Changes check	✅ Passed	Check skipped because no linked issues were found for this pull request.
Title check	✅ Passed	The title clearly and specifically describes the main change: adding pg_stat_activity data to the diagnostic bundle, matching the changeset's core objective.

_{✏️ Tip: You can configure your own custom pre-merge checks in the settings.}

✨ Finishing Touches

📝 Generate docstrings

Create stacked PR
Commit on current branch

🧪 Generate unit tests (beta)

Create PR with unit tests
Commit unit tests in branch dashrews/stat-activity-34356

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

coderabbitai

Actionable comments posted: 1

🧹 Nitpick comments (1)

central/debug/service/db_diagnostics_test.go (1)

73-86: Add an assertion for query truncation contract

This test covers presence/activity well; consider also asserting len(*a.Query) <= 1000 when a.Query != nil so the substr(query, 1, 1000) behavior is regression-tested.

Proposed test addition

 func (s *DBDiagnosticTestSuite) TestGetPGStatActivities() {
 	activities := stats.GetPGStatActivities(s.ctx, s.dbPool, 100)
 	s.Empty(activities.Error)
 	s.True(len(activities.Activities) > 0)

 	foundActive := false
 	for _, a := range activities.Activities {
+		if a.Query != nil {
+			s.LessOrEqual(len(*a.Query), 1000)
+		}
 		if a.State != nil && *a.State == "active" {
 			foundActive = true
 			break
 		}
 	}
 	s.True(foundActive, "expected to find at least one active connection")
 }

🤖 Prompt for AI Agents

Verify each finding against the current code and only fix it if needed.

In `@central/debug/service/db_diagnostics_test.go` around lines 73 - 86, The test
TestGetPGStatActivities currently checks for active connections but omits
validating the query truncation; inside the loop over activities.Activities in
TestGetPGStatActivities, add an assertion that when a.Query != nil then
len(*a.Query) <= 1000 to verify the substr(query, 1, 1000) contract is honored
(use the test suite's assertion helpers, e.g., s.True or s.LessOrEqual,
referencing activities.Activities and a.Query).

🤖 Prompt for all review comments with AI agents

Verify each finding against the current code and only fix it if needed.

Nitpick comments:
In `@central/debug/service/db_diagnostics_test.go`:
- Around line 73-86: The test TestGetPGStatActivities currently checks for
active connections but omits validating the query truncation; inside the loop
over activities.Activities in TestGetPGStatActivities, add an assertion that
when a.Query != nil then len(*a.Query) <= 1000 to verify the substr(query, 1,
1000) contract is honored (use the test suite's assertion helpers, e.g., s.True
or s.LessOrEqual, referencing activities.Activities and a.Query).

ℹ️ Review info

⚙️ Run configuration

Configuration used: Central YAML (base), Organization UI (inherited)

Review profile: CHILL

Plan: Pro Plus

Run ID: 061ac0c7-30f1-48f8-aefd-deb0e089a642

📥 Commits

Reviewing files that changed from the base of the PR and between f948ae4 and 9da8027.

📒 Files selected for processing (3)

central/debug/service/db_diagnostics_test.go
central/debug/service/service.go
pkg/postgres/stats/stats.go

github-actions · 2026-04-21T15:18:12Z

🚀 Build Images Ready

Images are ready for commit 67284ff. To use with deploy scripts:

export MAIN_IMAGE_TAG=4.11.x-738-g67284ff2a7

codecov · 2026-04-21T15:23:56Z

Codecov Report

❌ Patch coverage is 4.25532% with 45 lines in your changes missing coverage. Please review.
✅ Project coverage is 49.74%. Comparing base (0b46bb4) to head (b6fae9c).
⚠️ Report is 6 commits behind head on master.

Files with missing lines	Patch %	Lines
pkg/postgres/stats/stats.go	5.12%	37 Missing ⚠️
central/debug/service/service.go	0.00%	8 Missing ⚠️

Additional details and impacted files

@@            Coverage Diff             @@
##           master   #20130      +/-   ##
==========================================
- Coverage   49.77%   49.74%   -0.03%     
==========================================
  Files        2767     2768       +1     
  Lines      209596   209705     +109     
==========================================
  Hits       104318   104318              
- Misses      97579    97688     +109     
  Partials     7699     7699

Flag	Coverage Δ
go-unit-tests	`49.74% <4.25%> (-0.03%)`	⬇️

Flags with carried forward coverage won't be shown. Click here to find out more.

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

🚀 New features to boost your workflow:

📦 JS Bundle Analysis: Save yourself from yourself by tracking and limiting bundle sizes in JS merges.

Support frequently needs pg_stat_activity data when triaging database performance issues but has to ask customers to run manual queries. This adds it to the diagnostic bundle automatically, following the existing pg_stat_statements/tuple stats pattern. Captures connection state, active queries, wait events, and transaction timestamps in central-db-pg-activity.json. On external databases with limited access, gracefully returns only the application own sessions. This change was partially generated by AI. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Match the style of PGStatStatement, PGTupleStat, and PGAnalyzeStat which rely on default Go JSON marshaling without explicit tags. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Keep type/function groupings together — each stat group (types + function) is now contiguous in the file. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Strip string literals from captured query text to prevent sensitive values from leaking into diagnostic bundles. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

clickboo · 2026-04-22T05:57:51Z

/retest

clickboo

LGTM. Recommendation to verify, if not already done, that the redaction is complete enough to handle the several sql types if required.

dashrews78 requested a review from a team as a code owner April 21, 2026 15:04

dashrews78 added the coderabbit-review label Apr 21, 2026

github-actions Bot added area/central area/postgres labels Apr 21, 2026

coderabbitai Bot reviewed Apr 21, 2026

View reviewed changes

Comment thread pkg/postgres/stats/stats.go

dashrews78 changed the title ~~ROX-34356: ad pg_stat_activity to diagnostic bundle~~ ROX-34356: add pg_stat_activity to diagnostic bundle Apr 21, 2026

dashrews78 and others added 6 commits April 21, 2026 15:54

X-Smart-Branch-Parent: master

986e2d8

ROX-34356: Remove JSON struct tags from PGStatActivity for consistency

859d32c

Match the style of PGStatStatement, PGTupleStat, and PGAnalyzeStat which rely on default Go JSON marshaling without explicit tags. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

ROX-34356: Move PGStatActivity group after GetPGAnalyzeStats

051b1bc

Keep type/function groupings together — each stat group (types + function) is now contiguous in the file. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

ROX-34356: Redact SQL string literals from pg_stat_activity queries

0f6cfe5

Strip string literals from captured query text to prevent sensitive values from leaking into diagnostic bundles. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

ROX-34356: Add unit tests for SQL string literal redaction

b6fae9c

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

dashrews78 force-pushed the dashrews/stat-activity-34356 branch from 3bcfc79 to b6fae9c Compare April 21, 2026 19:54

clickboo approved these changes Apr 22, 2026

View reviewed changes

dashrews78 merged commit 67284ff into master Apr 22, 2026
99 of 100 checks passed

dashrews78 deleted the dashrews/stat-activity-34356 branch April 22, 2026 09:35

Navigation Menu

Repository navigation

ROX-34356: add pg_stat_activity to diagnostic bundle - #20130

ROX-34356: add pg_stat_activity to diagnostic bundle#20130
dashrews78 merged 6 commits into
masterfrom
dashrews/stat-activity-34356

dashrews78 commented Apr 21, 2026

Uh oh!

coderabbitai Bot commented Apr 21, 2026 •

edited

Loading

Walkthrough

Changes

Sequence Diagram(s)

Estimated code review effort

❌ Failed checks (1 warning)

Uh oh!

coderabbitai Bot left a comment

Uh oh!

Uh oh!

github-actions Bot commented Apr 21, 2026 •

edited

Loading

Uh oh!

codecov Bot commented Apr 21, 2026 •

edited

Loading

Uh oh!

clickboo commented Apr 22, 2026

Uh oh!

clickboo left a comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

dashrews78 commented Apr 21, 2026

Description

User-facing documentation

Testing and quality

Automated testing

How I validated my change

Uh oh!

coderabbitai Bot commented Apr 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Walkthrough

Changes

Sequence Diagram(s)

Estimated code review effort

❌ Failed checks (1 warning)

Uh oh!

coderabbitai Bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

github-actions Bot commented Apr 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

🚀 Build Images Ready

Uh oh!

codecov Bot commented Apr 21, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codecov Report

Uh oh!

clickboo commented Apr 22, 2026

Uh oh!

clickboo left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

coderabbitai Bot commented Apr 21, 2026 •

edited

Loading

github-actions Bot commented Apr 21, 2026 •

edited

Loading

codecov Bot commented Apr 21, 2026 •

edited

Loading