Caches made with `create cache concurrently` not getting hit

[lukoktonos]

Summary

create cache concurrently isn't working correctly.

Description

If we create a cache with create cache concurrently, we don't seem to update the status in the query status cache for those queries correctly, meaning show caches and query routing don't actually work to hit that cache.

Expected behavior

Queries can hit caches made from create cache concurrently

Actual behavior

Queries that should hit create cache concurrently continue to be proxied

Steps to reproduce

127.0.0.1/testdb=> \d t1
                 Table "public.t1"
 Column │  Type   │ Collation │ Nullable │ Default 
════════╪═════════╪═══════════╪══════════╪═════════
 a      │ integer │           │          │ 
Publications:
    "readyset"
127.0.0.1/testdb=> create cache from select * from t1 where a = 1;

Time: 17.633 ms
127.0.0.1/testdb=> show caches;
      query id      │     cache name     │             query text              │ fallback behavior │ count 
════════════════════╪════════════════════╪═════════════════════════════════════╪═══════════════════╪═══════
 q_89db6225b7396830 │ q_89db6225b7396830 │ SELECT * FROM "t1" WHERE ("a" = $1) │ fallback allowed  │ 0
(1 row)

Time: 6.959 ms
127.0.0.1/testdb=> explain caches;
                                                    query text                                                     
═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 CREATE CACHE "q_89db6225b7396830" FROM SELECT "public"."t1"."a" FROM "public"."t1" WHERE ("public"."t1"."a" = $1)
(1 row)

Time: 3.058 ms
127.0.0.1/testdb=> create cache concurrently from select * from t1 where a <> 1;
 Migration Id 
══════════════
 4294967298
(1 row)

Time: 6.078 ms
127.0.0.1/testdb=> show caches;
      query id      │     cache name     │             query text              │ fallback behavior │ count 
════════════════════╪════════════════════╪═════════════════════════════════════╪═══════════════════╪═══════
 q_89db6225b7396830 │ q_89db6225b7396830 │ SELECT * FROM "t1" WHERE ("a" = $1) │ fallback allowed  │ 0
(1 row)

Time: 5.278 ms
127.0.0.1/testdb=> explain caches;
                                                    query text                                                     
═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 CREATE CACHE "q_89db6225b7396830" FROM SELECT "public"."t1"."a" FROM "public"."t1" WHERE ("public"."t1"."a" = $1)
 CREATE CACHE "q_e491f28bee0093d8" FROM SELECT "public"."t1"."a" FROM "public"."t1" WHERE ("public"."t1"."a" != 1)
(2 rows)

Time: 5.207 ms
127.0.0.1/testdb=> 
127.0.0.1/testdb=> select a from t1 where a <> 1;
 a 
═══
(0 rows)

Time: 4.609 ms
127.0.0.1/testdb=> explain last statement;
 Query_destination │ ReadySet_error 
═══════════════════╪════════════════
 upstream          │ ok
(1 row)

Time: 4.271 ms
127.0.0.1/testdb=> 

ReadySet version

c7af24e8525006d69df8e35793a4075288075a04

Upstream DB type and version

postgres 14

Instance Details

local

Deployment Details

\[Docker | OSS K8s | OSS binary | RS-Cloud\]

OS Information

Logs

[gvsg-rs]

Short term fix: Disable this until we figure out the long term fix.

[jasobrown-rs]

fwiw, i do see the first execution of the query after migration going to the upstream (as per explain last statement), but subsequent executions are being served from readyset.

This is different from the regular, non- CONCURRENTLY created caches, which do serve from readyset on the first execution

[davisjc]

Async cache creation seems pretty messed up. I see the same thing @jasobrown-rs mentioned of the first invocation after cache creation going to upstream (it shouldn't) with subsequent invocations hitting Readyset. However, there's seemingly more wrong with it.

Caches created CONCURRENTLY seem to not respect the ALWAYS directive at all. They also seem to not work if dropped and recreated:

readyset-psql> CREATE TABLE foo (a INT);
CREATE TABLE

readyset-psql> CREATE CACHE ALWAYS CONCURRENTLY FROM SELECT * FROM foo WHERE a = 1;
 Migration Id
--------------
 4294967297
(1 row)

readyset-psql> SHOW READYSET MIGRATION STATUS 4294967297;
 Migration Status
------------------
 Completed
(1 row)

readyset-psql> SELECT * FROM foo WHERE a = 1;
 a
---
(0 rows)

readyset-psql> EXPLAIN LAST STATEMENT;
 Query_destination | ReadySet_error
-------------------+----------------
 upstream          | ok
(1 row)

readyset-psql> SELECT * FROM foo WHERE a = 1;
 a
---
(0 rows)

readyset-psql> EXPLAIN LAST STATEMENT;
 Query_destination | ReadySet_error
-------------------+----------------
 readyset          | ok
(1 row)

readyset-psql> BEGIN;
BEGIN

readyset-psql> SELECT * FROM foo WHERE a = 1;
 a
---
(0 rows)

readyset-psql> EXPLAIN LAST STATEMENT;
 Query_destination | ReadySet_error
-------------------+----------------
 upstream          | ok
(1 row)

readyset-psql> COMMIT;
COMMIT

readyset-psql> SELECT * FROM foo WHERE a = 1;
 a
---
(0 rows)

readyset-psql> EXPLAIN LAST STATEMENT;
 Query_destination | ReadySet_error
-------------------+----------------
 readyset          | ok
(1 row)

readyset-psql> DROP ALL CACHES;

readyset-psql> CREATE CACHE ALWAYS CONCURRENTLY FROM SELECT * FROM foo WHERE a = 1;
 Migration Id
--------------
 12884901889
(1 row)

readyset-psql> SHOW READYSET MIGRATION STATUS 12884901889;
 Migration Status
------------------
 Completed
(1 row)

readyset-psql> SELECT * FROM foo WHERE a = 1;
 a
---
(0 rows)

readyset-psql> EXPLAIN LAST STATEMENT;
 Query_destination | ReadySet_error
-------------------+----------------
 upstream          | ok
(1 row)

readyset-psql> SELECT * FROM foo WHERE a = 1;
 a
---
(0 rows)

readyset-psql> EXPLAIN LAST STATEMENT;
 Query_destination | ReadySet_error
-------------------+----------------
 upstream          | ok
(1 row)

readyset-psql> SELECT * FROM foo WHERE a = 1;
 a
---
(0 rows)

readyset-psql> EXPLAIN LAST STATEMENT;
 Query_destination | ReadySet_error
-------------------+----------------
 upstream          | ok
(1 row)