mirror of
https://github.com/supabase/supabase.git
synced 2026-05-09 18:30:12 -04:00
Jordi Enric
c689e91160
## Problem On self-hosted Supabase instances where the `pg_stat_statements` extension is not installed, the Observability Overview page automatically queries the extension on every page load. This produces "relation pg_stat_statements does not exist" errors in Postgres logs for all projects without the extension. Additionally, if a user navigated to the Query Performance page, they received a generic error with no actionable guidance. A secondary issue allowed malformed sort URL params (e.g. `?sort=created_at:asc&order=asc`) to be interpolated directly into SQL ORDER BY clauses. ## Fix - Wrapped the `useSlowQueriesCount` SQL in a `CASE WHEN EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements')` guard. The query now returns 0 silently instead of erroring when the extension is absent. - Added a `VALID_SORT_COLUMNS` whitelist in `generateQueryPerformanceSql`. Invalid column names from URL params are rejected and the query falls back to the preset default ORDER BY. - When the Query Performance page fails because `pg_stat_statements` does not exist, a `warning` admonition now appears with "Enable it in Database -> Extensions" guidance instead of a generic destructive error. The Sentry capture is skipped for this expected configuration state. - Extracted `buildSlowQueriesCountSql` as a testable function and added unit tests for both fixes. ## How to test **Extension not installed (self-hosted):** 1. Run a self-hosted Supabase instance without the `pg_stat_statements` extension enabled. 2. Navigate to the Observability Overview page. 3. Check Postgres logs -- no "relation pg_stat_statements does not exist" errors should appear. 4. Navigate to the Query Performance page. 5. Expected: a yellow warning admonition appears saying the extension is not enabled, with a link to Database -> Extensions. No red error. **Extension installed (normal flow):** 1. With `pg_stat_statements` installed, navigate to Observability Overview. 2. Expected: slow queries count loads as normal. 3. Navigate to Query Performance -- data loads as normal. **Invalid sort URL param:** 1. Navigate to `/project/<ref>/observability/query-performance?sort=created_at:asc&order=asc`. 2. Expected: the page loads and falls back to the default sort order (total time descending). No SQL error in logs. **Unit tests:** ``` node apps/studio/node_modules/vitest/dist/cli.js run --no-coverage \ apps/studio/components/interfaces/Observability/useSlowQueriesCount.test.ts \ apps/studio/components/interfaces/QueryPerformance/useQueryPerformanceQuery.test.ts ``` All 28 tests should pass. --------- Co-authored-by: Claude Sonnet 4.6 <noreply@anthropic.com>
22 lines
743 B
TypeScript
22 lines
743 B
TypeScript
import { describe, expect, it } from 'vitest'
|
|
|
|
import { buildSlowQueriesCountSql } from './useSlowQueriesCount'
|
|
|
|
describe('buildSlowQueriesCountSql', () => {
|
|
it('checks pg_extension before querying pg_stat_statements', () => {
|
|
const sql = buildSlowQueriesCountSql()
|
|
expect(sql).toContain('pg_extension')
|
|
expect(sql).toContain("extname = 'pg_stat_statements'")
|
|
})
|
|
|
|
it('returns 0 when extension is not installed', () => {
|
|
expect(buildSlowQueriesCountSql()).toContain('ELSE 0')
|
|
})
|
|
|
|
it('still queries pg_stat_statements when extension exists', () => {
|
|
const sql = buildSlowQueriesCountSql()
|
|
expect(sql).toContain('pg_stat_statements')
|
|
expect(sql).toContain('total_exec_time + total_plan_time > 1000')
|
|
})
|
|
})
|