{"record":{"id":"ecf0780d26adfcd7","repo":"nautechsystems/nautilus_trader","slug":"failed-to-truncate-tables-e","errorCode":null,"errorMessage":"Failed to truncate tables: {e}","messagePattern":"Failed to truncate tables: (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/infrastructure/src/sql/queries.rs","lineNumber":61,"sourceCode":"    orders::{OrderEventAnyRow, OrderFilledRow},\n    types::CurrencyRow,\n};\n\n#[derive(Debug)]\npub struct DatabaseQueries;\n\nimpl DatabaseQueries {\n    /// Truncates all tables in the cache database via the provided Postgres `pool`.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the TRUNCATE operation fails.\n    pub async fn truncate(pool: &PgPool) -> anyhow::Result<()> {\n        sqlx::query(\"SELECT truncate_all_tables()\")\n            .execute(pool)\n            .await\n            .map(|_| ())\n            .map_err(|e| anyhow::anyhow!(\"Failed to truncate tables: {e}\"))\n    }\n\n    /// Inserts a raw key-value entry into the `general` table via the provided `pool`.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the INSERT operation fails.\n    pub async fn add(pool: &PgPool, key: String, value: Vec<u8>) -> anyhow::Result<()> {\n        sqlx::query(\"INSERT INTO general (id, value) VALUES ($1, $2)\")\n            .bind(key)\n            .bind(value)\n            .execute(pool)\n            .await\n            .map(|_| ())\n            .map_err(|e| anyhow::anyhow!(\"Failed to insert into general table: {e}\"))\n    }\n\n    /// Loads all entries from the `general` table via the provided `pool`.","sourceCodeStart":43,"sourceCodeEnd":79,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/infrastructure/src/sql/queries.rs#L43-L79","documentation":"`DatabaseQueries::truncate` executes `SELECT truncate_all_tables()`, a database-side helper that clears all cache tables, and wraps any sqlx failure in this anyhow error. Failure means the query could not run or the function errored: unreachable database, missing stored function (migrations not applied), or insufficient privileges inside the function.","triggerScenarios":"Calling `DatabaseQueries::truncate(pool)` when: the pool is broken / Postgres is down; migrations were never run so `truncate_all_tables()` doesn't exist; the DB role lacks EXECUTE/TRUNCATE privileges; the function aborts due to locks, FKs, or statement timeout while other sessions hold table locks.","commonSituations":"Wiping a cache database between backtest runs against a freshly created database without migrations; truncating while another live trading process holds locks; connecting with a read-only or restricted role; wrong DATABASE_URL pointing at a database where the function is absent.","solutions":["Run the project's SQL migrations so the `truncate_all_tables()` function exists in the database.","Verify connectivity and DATABASE_URL (sanity check with a simple `SELECT 1`).","Grant the connecting role EXECUTE on the function and TRUNCATE on the cache tables.","Read the wrapped sqlx error text for the concrete cause and ensure no other sessions hold locks during truncation."],"exampleFix":"// before\nDatabaseQueries::truncate(&pool).await?;\n// after\nsqlx::query(\"SELECT 1\").execute(&pool).await\n    .map_err(|e| anyhow::anyhow!(\"db unreachable: {e}\"))?;\nDatabaseQueries::truncate(&pool).await?;","handlingStrategy":"retry","validationCode":"sqlx::query(\"SELECT 1\").execute(pool).await?;\nlet exists: bool = sqlx::query_scalar(\n    \"SELECT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'truncate_all_tables')\",\n).fetch_one(pool).await?;\nif !exists {\n    return Err(anyhow::anyhow!(\"truncate_all_tables() missing — run migrations\"));\n}","typeGuard":null,"tryCatchPattern":"for attempt in 1..=3 {\n    match DatabaseQueries::truncate(&pool).await {\n        Ok(()) => break,\n        Err(e) if attempt < 3 => {\n            tokio::time::sleep(Duration::from_millis(200 * attempt)).await;\n        }\n        Err(e) => return Err(e),\n    }\n}","preventionTips":["Always run the crate's SQL migrations before using the cache database.","Sanity-check connectivity with a trivial query before bulk operations.","Use a DB role with EXECUTE on truncate_all_tables and TRUNCATE privileges.","Avoid truncating while other writers hold locks on cache tables."],"tags":["postgres","sqlx","database","truncate"],"backgroundTag":"database-query-failed","analyzedSha":"18893faf8b356be3320add8de2f861b0b647cf06","analyzedAt":"2026-09-08T20:49:34.690Z","contentChangedAt":"2026-09-08T20:49:34.690Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}