fix: use transaction for idx consistency checking (#2840)

1 files changed, 7 insertions, 2 deletions

diff --git a/crates/atuin-server-postgres/src/lib.rs b/crates/atuin-server-postgres/src/lib.rs
index 65e8efbf..f399945e 100644
--- a/crates/atuin-server-postgres/src/lib.rs
+++ b/crates/atuin-server-postgres/src/lib.rs
@@ -620,9 +620,12 @@ impl Database for Postgres {
         const STATUS_SQL: &str =
             "select host, tag, max(idx) from store where user_id = $1 group by host, tag";
 
+        // Use a transaction to ensure consistent reads from both tables
+        let mut tx = self.pool.begin().await.map_err(fix_error)?;
+
         let mut res: Vec<(Uuid, String, i64)> = sqlx::query_as(STATUS_SQL)
             .bind(user.id)
-            .fetch_all(&self.pool)
+            .fetch_all(&mut *tx)
             .await
             .map_err(fix_error)?;
         res.sort();
@@ -636,11 +639,13 @@ impl Database for Postgres {
         let mut cached_res: Vec<(Uuid, String, i64)> =
             sqlx::query_as("select host, tag, idx from store_idx_cache where user_id = $1")
                 .bind(user.id)
-                .fetch_all(&self.pool)
+                .fetch_all(&mut *tx)
                 .await
                 .map_err(fix_error)?;
         cached_res.sort();
 
+        tx.commit().await.map_err(fix_error)?;
+
         let mut status = RecordStatus::new();
 
         let equal = res == cached_res;