What causes "Failed to commit add_account transaction: {e}" in nautechsystems/nautilus_trader?

Retry add_account with backoff — since commit failed the transaction rolled back, so a full retry is safe (the upserts are idempotent).

nautechsystems/nautilus_trader · error

Failed to commit add_account transaction: {e}

Error message

Failed to commit add_account transaction: {e}

What it means

Both INSERTs inside add_account succeeded, but transaction.commit() failed, so the account write is rolled back entirely (Postgres commits atomically). This error is wrapped from the sqlx commit error and usually means the connection to Postgres was lost or the server rejected the commit (e.g. transaction aborted server-side, idle-in-transaction timeout).

Source

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

                ts_event = $8, ts_init = $9, updated_at = CURRENT_TIMESTAMP
        "#)
            .bind(account_event.event_id.to_string())
            .bind(account_event.account_type.to_string())
            .bind(account_event.account_id.to_string())
            .bind(account_event.base_currency.map(|x| x.code.as_str()))
            .bind(balances)
            .bind(margins)
            .bind(account_event.is_reported)
            .bind(account_event.ts_event.to_string())
            .bind(account_event.ts_init.to_string())
            .execute(&mut *transaction)
            .await
            .map(|_| ())
            .map_err(|e| anyhow::anyhow!("Failed to insert into account_event table: {e}"))?;
        transaction
            .commit()
            .await
            .map_err(|e| anyhow::anyhow!("Failed to commit add_account transaction: {e}"))
    }

    /// Loads all account events for `account_id` via the provided `pool`.
    ///
    /// # Errors
    ///
    /// Returns an error if the SQL SELECT or deserialization fails.
    pub async fn load_account_events(
        pool: &PgPool,
        account_id: &AccountId,
    ) -> anyhow::Result<Vec<AccountState>> {
        sqlx::query_as::<_, AccountEventRow>(
            r#"SELECT * FROM "account_event" WHERE account_id = $1 ORDER BY created_at ASC"#,
        )
        .bind(account_id.to_string())
        .fetch_all(pool)
        .await
        .map(|rows| rows.into_iter().map(|row| row.0).collect())

View on GitHub (pinned to 18893faf8b)

Solutions

Retry add_account with backoff — since commit failed the transaction rolled back, so a full retry is safe (the upserts are idempotent).
Check Postgres logs and timeouts: idle_in_transaction_session_timeout, statement_timeout, and pgbouncer server_idle_timeout.
Improve network stability or co-locate the writer with the DB to shrink the commit window.
Verify server availability (failover/restart) if the error reports 'server closed the connection'.

Example fix

// before: single attempt, hard failure on commit
add_account(&pool, updated, state).await?;

// after: safe retry (upserts are idempotent, failed commit rolled back)
for attempt in 0..3 {
    match add_account(&pool, updated, state.clone()).await {
        Ok(()) => break,
        Err(e) if attempt < 2 && is_transient(&e) => tokio::time::sleep(backoff(attempt)).await,
        Err(e) => return Err(e),
    }
}

Defensive patterns

Strategy: retry

Validate before calling

// preflight connection stability and timeout config
let row = sqlx::query("SHOW idle_in_transaction_session_timeout").fetch_one(pool).await?;
// ensure commits are not starved by long idle-in-transaction windows

Try / catch

for attempt in 0..3 {
    match add_account(&pool, updated, state.clone()).await {
        Ok(()) => break,
        Err(e) if attempt < 2 && e.to_string().contains("commit") => {
            tokio::time::sleep(Duration::from_millis(200 * 2u64.pow(attempt))).await;
        }
        Err(e) => return Err(e),
    }
}

Prevention

Always retry whole transactions on commit failure — rollback makes retries safe.
Raise idle_in_transaction_session_timeout appropriately or shrink the transaction window.
Configure pgbouncer/pooler timeouts to exceed the longest expected transaction.
Monitor Postgres restarts/failovers and reconnect with fresh sessions.

When it happens

Trigger: Calling add_account when the connection drops between the INSERTs and the COMMIT — network partition, Postgres restart, statement/idle-in-transaction timeout, or the pool reclaiming the connection.

Common situations: Slow balance payloads over a WAN link exceeding idle_in_transaction_session_timeout; Postgres failover or connection-pooler (pgbouncer) killing the backend; long backtests writing accounts over an unstable VPN link.

Related errors

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