What causes "Failed to truncate tables: {e}" in nautechsystems/nautilus_trader?

Run the project's SQL migrations so the `truncate_all_tables()` function exists in the database.

Failed to truncate tables: {e}

Error message

Failed to truncate tables: {e}

What it means

`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.

Source

Thrown at crates/infrastructure/src/sql/queries.rs:61

    orders::{OrderEventAnyRow, OrderFilledRow},
    types::CurrencyRow,
};

#[derive(Debug)]
pub struct DatabaseQueries;

impl DatabaseQueries {
    /// Truncates all tables in the cache database via the provided Postgres `pool`.
    ///
    /// # Errors
    ///
    /// Returns an error if the TRUNCATE operation fails.
    pub async fn truncate(pool: &PgPool) -> anyhow::Result<()> {
        sqlx::query("SELECT truncate_all_tables()")
            .execute(pool)
            .await
            .map(|_| ())
            .map_err(|e| anyhow::anyhow!("Failed to truncate tables: {e}"))
    }

    /// Inserts a raw key-value entry into the `general` table via the provided `pool`.
    ///
    /// # Errors
    ///
    /// Returns an error if the INSERT operation fails.
    pub async fn add(pool: &PgPool, key: String, value: Vec<u8>) -> anyhow::Result<()> {
        sqlx::query("INSERT INTO general (id, value) VALUES ($1, $2)")
            .bind(key)
            .bind(value)
            .execute(pool)
            .await
            .map(|_| ())
            .map_err(|e| anyhow::anyhow!("Failed to insert into general table: {e}"))
    }

    /// Loads all entries from the `general` table via the provided `pool`.

View on GitHub (pinned to 18893faf8b)

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.

Example fix

// before
DatabaseQueries::truncate(&pool).await?;
// after
sqlx::query("SELECT 1").execute(&pool).await
    .map_err(|e| anyhow::anyhow!("db unreachable: {e}"))?;
DatabaseQueries::truncate(&pool).await?;

Defensive patterns

Strategy: retry

Validate before calling

sqlx::query("SELECT 1").execute(pool).await?;
let exists: bool = sqlx::query_scalar(
    "SELECT EXISTS (SELECT 1 FROM pg_proc WHERE proname = 'truncate_all_tables')",
).fetch_one(pool).await?;
if !exists {
    return Err(anyhow::anyhow!("truncate_all_tables() missing — run migrations"));
}

Try / catch

for attempt in 1..=3 {
    match DatabaseQueries::truncate(&pool).await {
        Ok(()) => break,
        Err(e) if attempt < 3 => {
            tokio::time::sleep(Duration::from_millis(200 * attempt)).await;
        }
        Err(e) => return Err(e),
    }
}

Prevention

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.

When it happens

Trigger: 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.

Common situations: 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.

Understand the failure class

Background: Database query failed: Internal Server Error 500s wrapping SQL, Prisma, and connection failures — what to check first — this error's family across 16 libraries.

Related errors

AI-assisted analysis of nautechsystems/nautilus_trader@18893faf8b (2026-09-08). Data as JSON: /api/errors/ecf0780d26adfcd7. Report an issue: GitHub.