Background
Query Pilot is being enhanced to integrate with Readyset shallow caching. To support that integration, Query Pilot needs a programmatic way to determine, after issuing a CREATE CACHE FROM ... statement, whether Readyset created a Deep or Shallow cache for that query.
Today, a successful CREATE CACHE FROM returns no rows (QueryResult::Empty in readyset-adapter/src/backend.rs, around the create_cached_query / create_deep_cache / create_shallow_cache paths). The cache type is decided internally (we already model both variants in CacheType::{Deep, Shallow} in readyset-sql/src/ast/create.rs), but that decision is never surfaced to the client.
Proposed change
CREATE CACHE FROM ... should return exactly one row describing the cache that was created. At minimum the row must include the cache type (DEEP or SHALLOW). We should also consider including, in the same row, fields useful to a client like Query Pilot:
cache_type — DEEP | SHALLOWname — the cache name (auto-generated or user-supplied)query_id — the Readyset query id
Exact column set/names to be finalized during design; the hard requirement is that the cache type is unambiguously returned in a single row.
Applies to both MySQL and PostgreSQL adapters.
Why
Query Pilot currently has no way to tell whether Readyset chose a deep or shallow cache for a given query. Without this, Query Pilot cannot reason about expected freshness, eviction behavior, or maintenance cost when recommending or rolling out caches, which blocks the shallow caching integration.
Out of scope
- Changing the decision logic for when Readyset picks Deep vs Shallow.
- The
CreateCache hint directive path — the hint is only available for shallow caches and executes the query inline, so it is a separate path from CREATE CACHE FROM and is not covered by this issue.
- Returning multiple rows / extended cache metadata (e.g., eviction policy, coalesce window) — can be a follow-up if Query Pilot needs it.
Acceptance criteria
References
public/readyset-adapter/src/backend.rs — create_cached_query, create_deep_cache, create_shallow_cachepublic/readyset-sql/src/ast/create.rs — CacheType::{Deep, Shallow}- Project: Readyset Shallow Caching
Background
Query Pilot is being enhanced to integrate with Readyset shallow caching. To support that integration, Query Pilot needs a programmatic way to determine, after issuing a
CREATE CACHE FROM ...statement, whether Readyset created a Deep or Shallow cache for that query.Today, a successful
CREATE CACHE FROMreturns no rows (QueryResult::Emptyinreadyset-adapter/src/backend.rs, around thecreate_cached_query/create_deep_cache/create_shallow_cachepaths). The cache type is decided internally (we already model both variants inCacheType::{Deep, Shallow}inreadyset-sql/src/ast/create.rs), but that decision is never surfaced to the client.Proposed change
CREATE CACHE FROM ...should return exactly one row describing the cache that was created. At minimum the row must include the cache type (DEEPorSHALLOW). We should also consider including, in the same row, fields useful to a client like Query Pilot:cache_type—DEEP|SHALLOWname— the cache name (auto-generated or user-supplied)query_id— the Readyset query idExact column set/names to be finalized during design; the hard requirement is that the cache type is unambiguously returned in a single row.
Applies to both MySQL and PostgreSQL adapters.
Why
Query Pilot currently has no way to tell whether Readyset chose a deep or shallow cache for a given query. Without this, Query Pilot cannot reason about expected freshness, eviction behavior, or maintenance cost when recommending or rolling out caches, which blocks the shallow caching integration.
Out of scope
CreateCachehint directive path — the hint is only available for shallow caches and executes the query inline, so it is a separate path fromCREATE CACHE FROMand is not covered by this issue.Acceptance criteria
CREATE CACHE FROM <query>returns one row containing the cache type (DEEP/SHALLOW) on success, in both MySQL and PostgreSQL adapters.web-apps/apps/docs) updated to describe the new result row.References
public/readyset-adapter/src/backend.rs—create_cached_query,create_deep_cache,create_shallow_cachepublic/readyset-sql/src/ast/create.rs—CacheType::{Deep, Shallow}