Readyset is a MySQL and Postgres wire-compatible caching layer that sits in front of existing databases to speed up queries and horizontally scale read throughput. Under the hood, ReadySet caches the results of cached select statements and incrementally updates these results over time as the underlying data changes.
A customer created a cache using a query that had table aliased in it. After restarting readyset (due to unrelated reasons), queries were no longer hitting the readyset cache and were proxied to the upstream database.
The problem is that when the adapter calls the server to derive the status of the query, that code path does not perform all the same query rewrites that the create cache path does. In fact, the step that's missing is the "table_alias_removal". Hence, they lookup doesn't find the query cache properly, thinks the query is not cached, and thus proxies everything.
Steps to reproduce
create table ints (c1 int, c2 int);
create cache from select i.c2 from ints i where i.c1 = 1;
-- restart readyset
select i.c2 from ints i where i.c1 = 1;
noria=> explain last statement;
Query_destination | ReadySet_error
-------------------+----------------
upstream | ok
-- wait 5-10 seconds for adapter to asynchronously check the query's status on the server.
select i.c2 from ints i where i.c1 = 1;
noria=> explain last statement;
Query_destination | ReadySet_error
-------------------+----------------
upstream | ok
The query will be proxied in perpetuity, unless you re-create the cache.
ReadySet version
HEAD as of 2024-Oct-10 (101f84276badfc70e185c962c33f2c1859eb2246)
Description
A customer created a cache using a query that had table aliased in it. After restarting readyset (due to unrelated reasons), queries were no longer hitting the readyset cache and were proxied to the upstream database.
The problem is that when the adapter calls the server to derive the status of the query, that code path does not perform all the same query rewrites that the create cache path does. In fact, the step that's missing is the "table_alias_removal". Hence, they lookup doesn't find the query cache properly, thinks the query is not cached, and thus proxies everything.
Steps to reproduce
The query will be proxied in perpetuity, unless you re-create the cache.
ReadySet version
HEAD as of 2024-Oct-10 (
101f84276badfc70e185c962c33f2c1859eb2246)Upstream DB type and version
PG, but upstream is irrelevant here.