mirror of
https://github.com/supabase/supabase.git
synced 2026-10-05 17:35:10 +03:00
codex/fix-tanstack-e2e
8
Commits
| Author | SHA1 | Message | Date | |
|---|---|---|---|---|
 Andrew ValleteauandClaude Fable 5
|
6c6a721cb7 |
fix(pg-meta): scope remaining O(catalog) introspection queries behind pgMetaScopedIntrospection (#48148)
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES ## What kind of change does this PR introduce? Bug fix (performance), follow-up to #47894, plus regression-guard tests. ## What is the current behavior? #47894 scoped the Table Editor and entity-definition introspection queries, but four more `@supabase/pg-meta` query families still do O(catalog) work per request. On a production project with a very large catalog (hundreds of schemas, ~465K `pg_constraint` rows) they run 5 to 55 seconds each, trip the 58s `statement_timeout`, and spill sorts to temp files. During a recent "DB CPU > 85%" incident on such a project, 24 of 27 active backends were running these queries concurrently. 1. **`tables.retrieve()` (single-table lookup by name+schema or id)**: the `tables`/`columns` CTEs scan the whole catalog (`pg_class`, `pg_constraint`, `pg_index`, all of `pg_attribute`, per-table sizes) and the one-table predicate is applied only on the outer select. Same bug class #47894 fixed for the OID-based table editor query; this sibling path never got the treatment. It accounted for 94 of the 96 statement-timeout cancellations in the incident. 2. **Types listing**: the `t_enums` and `t_attributes` subqueries aggregate the entire `pg_enum` and every composite relation before the wrapper's schema filter applies. 3. **Table privileges**: `aclexplode` + double `pg_roles` join + GROUP BY over every relation in the database; schema/OID filters applied only after aggregation, in both `list()` and `retrieve()`. 4. **Row counts**: `getTableRowsCountSql` treats `reltuples = -1` (never-analyzed table) as "small table, run exact count(*)". A freshly bulk-loaded multi-million-row table times out on every Table Editor pagination render. Two Studio-side amplifiers turned one slow query into a sustained load storm: - `useTableQuery` (behind `tables.retrieve()`) mounts once per visible foreign-key grid cell via `ForeignKeyFormatter`, so a single Table Editor view fires ~20 concurrent copies against the FK target table. A timed-out query caches nothing, and TanStack retries errored no-data queries on every observer mount by default, so scrolling kept re-issuing the 58s scan. - `useTableApiAccessQuery` fetched table privileges for the entire database and filtered down to one schema client-side. ## What is the new behavior? **pg-meta (all behind the existing `pgMetaScopedIntrospection` flag, same rollout mechanism as #47894; `scoped: false` keeps serving the current SQL):** - `tables.retrieve()`: the identifier is resolved to a scalar `targetOid` init-plan and pushed into the base scan, primary-key, relationships (both FK directions kept: `conrelid` or `confrelid`) and columns CTEs. A materialized `target` CTE was deliberately avoided: it acts as an optimization barrier and forces the very seq scans being removed. - Types: filter `pg_type`/`pg_namespace` first, then compute enums/attributes per surviving row via correlated index-scan subqueries (`pg_enum(enumtypid, enumsortorder)`, `pg_attribute(attrelid, attnum)`). - Table privileges: schema/OID predicates injected into the base WHERE before `aclexplode`/GROUP BY for `list()` and `retrieve()`. - Row counts: `reltuples = -1` is treated as "unknown" and gated on physical size via `pg_relation_size` (a cheap stat call; `relpages` is equally stale pre-vacuum). At or below `THRESHOLD_ESTIMATE_BYTES` (~10MB, derived from `THRESHOLD_COUNT` at a conservative ~200 bytes/row) the exact count runs as before: fast by construction, and it avoids bogus estimates since Postgres floors never-vacuumed heaps at 10 pages, so an empty table would otherwise report ~2K estimated rows. Above the gate the count routes through the EXPLAIN-based `pg_temp.count_estimate`, or returns `-1`/`is_estimate = true` in read-only contexts where the temp function cannot be created. The scoped branch embeds the estimated select via `literal()` instead of legacy's apostrophe-only escaping, so it stays correct under `standard_conforming_strings = off`. `enforceExactCount` unchanged. **Studio:** - The flag decision is contained in the data layer instead of prop-drilled: a small imperative accessor (`apps/studio/data/scoped-introspection.ts`) is hydrated from `useFlag` via a one-line `useSyncScopedIntrospection()` call in `DefaultLayout`, and the query functions read it internally when building the pg-meta SQL. `DefaultLayout` is shared by both the Next and TanStack router trees; hydrating from `_app.tsx` alone would leave TanStack-served pages permanently unscoped since `routes/__root.tsx` mounts its own flag provider. Cold loads cannot race the flag: the query functions await a readiness promise that resolves only after the sync hook has hydrated the accessor with a loaded flag store (immediately on self-hosted where flags are disabled; a 5s safety net armed lazily on the first `ready()` call - not at module import, which would let the timer expire before a project page ever mounts - bounds genuine ConfigCat outages). No component threading, no query-key changes (remaining tradeoff, documented in the module: a mid-session flag flip can serve stale-keyed caches until refetch, fine for a session-stable rollout flag). #47894's existing threading is left as-is and gets deleted together with the flag in the cleanup PR. Also fixes the previously-missing `scoped` pass-through in `getTableRowsCount`. - Flag-independent hardening: `useTableQuery` now sets `retryOnMount: false`, `refetchOnWindowFocus: false` and `staleTime: 5min`. Errored (timed-out) queries no longer refire on every grid cell remount, while stale successful metadata still revalidates on mount after `staleTime`. - `useTableApiAccessQuery` now passes `includedSchemas: [schemaName]`; the client-side filter stays as a safety net. - The rows-count query is `enabled`-gated on the permission check settling, so a transiently-false `canSQLAdminWrite` can no longer cache a read-only `-1` count for a writable user (read replicas short-circuit synchronously as before). **Regression guards (extending the #47894 infrastructure):** - Execution-based scoped-vs-legacy equivalence tests for all four queries: both variants run against the test database and are compared with raw `toEqual` - no normalization, ids included (types across 6 option combos, privileges incl. multi-grantee + PUBLIC, `tables.retrieve` for both identifier branches, row counts for every case where the two paths must agree). Two documented exceptions where only the LEGACY side is sorted, because a de-normalized diagnostic run proved legacy emits genuinely plan-dependent order there (an adversarial-FK fixture shows it is neither oid, name, nor creation order): the `types.list` outer row order (scoped adds `order by t.oid`; legacy has no ORDER BY) and the `tables.retrieve` relationships array (scoped orders by `constraint_name` + column-name tie-breakers - a composite two-column FK expands to 4 entries sharing one constraint_name). Everything else (privileges via `aclexplode` over the same relacl, columns by `ordinal_position`, primary keys by `indkey` order, enums by `enumsortorder`) is byte-identical between the two paths with no test-side help. The one intentional value divergence, never-analyzed tables above the size gate where legacy's exact count is the timeout bug itself, is asserted explicitly as a divergence. - Plan-guard budgets for every scoped query against the stress catalog (extended with 200 enums + 200 composite types). Residual seq scans are justified in-budget: `pg_constraint` max 2 (no index on `confrelid`), `pg_attrdef` max 1, `pg_authid` max 2 (scales with role count, not schema count). - Legacy templates carry a FROZEN do-not-edit marker (they must keep matching production behavior until the flag cleanup deletes them); the ordinary test suite runs against the legacy default, so behavioral drift there fails regular tests. ### Validation - pg-meta: typecheck clean; the affected suites (types, table-privileges, tables, rows-count, catalog-plan-guard) pass in full. - Cross-version: the scoped-vs-legacy equivalence and rows-count behavioral suites were validated on PostgreSQL 14, 15, and 17 (identical results on all three). Two version-marginal planner choices surfaced on 17 (`pg_type` / `pg_class` seq scan vs full-index bitmap for per-schema listings, both structurally unavoidable without an index leading on the namespace column) and are carried as justified plan-guard budget entries. A full 468-test suite run sequentially: 452 passed, 16 failures verified environmental (13 timeouts in an untouched file that passes 27/27 in isolation on the marathon-run cluster, 3 cluster-global role collisions from container reuse). - Studio: `pnpm --filter studio typecheck` clean; 39/39 tests across the touched data hooks; eslint clean on touched files. ### Rollout Same staged ConfigCat rollout as #47894 via `pgMetaScopedIntrospection` (user-email targeting first, then percentage, then 100%). The `useTableQuery` hardening and the API-access schema scoping ship unflagged (behavior-safe). Gate before percentage rollout: functionally verify the FK popover/selector UX under the new `staleTime`/`retryOnMount` settings (a just-edited FK target must not look stale anywhere Studio does not already refetch on save). Once fully rolled out, the legacy templates and flag get deleted together with #47894's in one cleanup PR. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Fable 5 <noreply@anthropic.com> |
||
|
Joshen Lim
|
1baaded0bb |
Consolidate execute-sql-query into execute-sql-mutation (#46944)
## Context Just some clean up as I was going through stuff - `useExecuteSqlQuery` is deprecated and not used at all - As such `execute-sql-query` is technically irrelevant, the more relevant file is `execute-sql-mutation` - Hence opting to consolidate `execute-sql-query` into `execute-sql-mutation` - Also removing `ExecuteSqlError` since its just re-exporting the `ResponseError` type There's a lot of file changes but its essentially just updating the importing statements across the files |
||
|
Charis
|
d79a276824 |
studio: ColumnTypeRef cascade + FK type comparison fixes (2/7) (#45903)
## I have read the [CONTRIBUTING.md](https://github.com/supabase/supabase/blob/master/CONTRIBUTING.md) file. YES ## What kind of change does this PR introduce? Refactor + bug fixes (part of the SafeSql migration stack — PR 2 of 7, stacks on top of #45897). ## What is the current behavior? - `pgMeta.columns.create` and the table-editor SQL builder take column type as a string with array suffix and schema baked in (e.g. `'private.test_enum'`, `'int4[]'`). - The studio table-editor SQL emits the legacy schema-embedded `format` string for enums in non-public schemas, while the pg-meta columns SQL already returns the new shape (bare `format` + separate `format_schema`). The two queries disagree on how to represent the same column, surfacing as a false-positive type mismatch in the FK selector when both ends are an enum from a non-public schema. - The FK selector compares column types by `format` alone — same-named enums in different schemas appear equal, and arrays vs. scalars of the same base type pass the family check. - `displayColumnType` renders arrays as the raw `_typname` pg-meta emits (e.g. `_int4` instead of `int4[]`). ## What is the new behavior? **pg-meta** - Introduce `ColumnTypeRef` (`{ schema?, name, isArray? }`) for column type input, replacing the legacy string-with-array-suffix format. `pgMeta.columns.create` and the table-editor SQL builder consume the new shape. - Add `format_schema` to the column zod schema; pg-meta SQL emits the type's schema for the table editor's ColumnType dropdown. - `pgMeta.columns.create` returns a `SafeSqlFragment`. - Studio table-editor SQL now emits bare `format` + `format_schema`, matching pg-meta's columns SQL. **Studio** - `SafePostgresColumn`/`SafePostgresTable` extend the new `PG*` types (master dropped postgres-meta). - Pipe `ColumnTypeRef` through `SidePanelEditor` → `ColumnEditor` → `TableEditor`, along with the column-create mutation, table retrieve/list queries, and the `TableList`/`ColumnList` surfaces. - `displayColumnType` helper renders arrays as `type[]` (or `schema.type[]`) and handles non-implicit schemas. - FK selector now carries `sourceIsArray`/`targetIsArray` and compares the full `(format, format_schema, isArray)` triple. Family checks for numeric/text/uuid skip when either side is an array (FKs across array boundaries are never compatible). - Type-mismatch and type-notice alerts pass `isArray` to the display helper. - Bundle `Policies.utils` + `Policies.types` + `sql-policy-mutation`, `PolicyEditorModal`, and `SchemaGraph` here because `SidePanelEditor` consumes `acceptGeneratedPolicy`/`AcceptedGeneratedPolicy` — splitting requires temporary overloads with no architectural payoff. ## Additional context Part of the SafeSql migration stack. Stacks on top of #45897. ### Manual test checklist Surfaces touched by this PR — please exercise each: **Table editor** - [x] Create a new table with a mix of column types (scalar, array, enum, foreign key) - [x] Add a column to an existing table; verify the type dropdown lists scalars + arrays separately and shows schema-qualified names for non-public enums - [x] Edit an existing column's type (scalar ↔ array, switch between enums in different schemas) and save - [x] Verify enum types from a non-public schema (e.g. `private.my_enum`) display as `private.my_enum` in the column list **Foreign key selector** - [x] Open the FK selector for a column and pick a target column with a matching type — no mismatch warning - [x] Pick a target column whose type differs only by schema (two same-named enums in different schemas) — should show a type-mismatch alert - [x] Pick a target column where one side is an array and the other is a scalar of the same base type — should show a type-mismatch alert (no auto-cast across array boundary) - [x] When FK target sets the column type, verify `format_schema` and `isArray` are preserved on the source column - [x] Type-mismatch and type-notice alert messages render array types as `type[]` (not `_type`) **Column list / table list** - [x] Schema-qualified type names display correctly for columns whose type lives in a non-public schema - [x] Array columns display as `type[]` (or `schema.type[]`) **Policies (bundled due to import dependency)** - [x] Open the Policies page; create/edit/delete a row-level policy via the modal - [x] Generate a policy via the AI assistant and accept it through `SidePanelEditor` — verify the accepted policy lands in the editor correctly **Schema visualizer** - [x] Open the Schemas → Schema Visualizer page; verify it renders without type errors and shows tables/relationships <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **Improvements** * Support for column types in non-public schemas and richer column type presentation (includes schema and array info). * Stronger SQL safety around policies and constraints; draft policy SQL is now promoted explicitly on save. * Improved foreign-key type validation and compatibility checks using enhanced type metadata. * **Tests** * Updated snapshots and tests to reflect new column metadata and SQL fragment handling. <!-- review_stack_entry_start --> [](https://app.coderabbit.ai/change-stack/supabase/supabase/pull/45903) <!-- review_stack_entry_end --> <!-- end of auto-generated comment: release notes by coderabbit.ai --> |
||
|
Charis
|
3b7052b5a9 | cleanup: fix import order and prefixes for studio/data (#44501) | ||
|
Saxon FletcherandJoshen Lim
|
9d66964b62 |
table create performance (#40857)
* table create performance * test * Clean up + refactor * Nit housekeeping * Minor housekeeping again * Fix * Fixy --------- Co-authored-by: Joshen Lim <joshenlimek@gmail.com> |
||
|
Ivan Vasilov
|
8b657165b5 |
chore: Migrate to use custom type for ReactQuery queries and mutations (#40073)
* Add custom types for queries, mutations and infinite queries. * Migrate all queries to use the new type. * Migrate all infinite queries to useCustomInfiniteQueryOptions. * Migrate all mutations to use useCustomMutationOptions. * Add type to all imports in `types` folder. |
||
|
Alaister YoungandJoshen Lim
|
8855d05803 |
chore(studio): swap react-query to object syntax (#39842)
* chore(studio): swap react-query to object syntax * Fix small issues found * Fix realtime settings * Nit --------- Co-authored-by: Joshen Lim <joshenlimek@gmail.com> |
||
|
|
b09440bd47 |
fix: move table create update delete to query route (#35662)
* fix: move table create update delete to query route * chore: implement query to fetch a single table * fix: retrieve table after update * chore: assign type to update table payload * chore: use updated table columns for edit * chore: make executeSql castable with generic (#35685) * Chore/refactor derivate more types from queries (#35687) * chore: make executeSql castable with generic * chore: derivate types from performed queries - It allows to decouple more the frontend logic and the pg-meta/sql-query logic allowing to reduce the number of cast and get closer types between what we do fetch and what we expect in our components * fix: remove existing check * chore: handle null comment and check * fix: format check name as identifier --------- Co-authored-by: avallete <andrew.valleteau@supabase.io> Co-authored-by: Andrew Valleteau <avallete@users.noreply.github.com> |
