{"record":{"id":"e73faba4f7f94efa","repo":"nautechsystems/nautilus_trader","slug":"failed-to-commit-add-account-transaction-e","errorCode":null,"errorMessage":"Failed to commit add_account transaction: {e}","messagePattern":"Failed to commit add_account transaction: (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/infrastructure/src/sql/queries.rs","lineNumber":1159,"sourceCode":"                ts_event = $8, ts_init = $9, updated_at = CURRENT_TIMESTAMP\n        \"#)\n            .bind(account_event.event_id.to_string())\n            .bind(account_event.account_type.to_string())\n            .bind(account_event.account_id.to_string())\n            .bind(account_event.base_currency.map(|x| x.code.as_str()))\n            .bind(balances)\n            .bind(margins)\n            .bind(account_event.is_reported)\n            .bind(account_event.ts_event.to_string())\n            .bind(account_event.ts_init.to_string())\n            .execute(&mut *transaction)\n            .await\n            .map(|_| ())\n            .map_err(|e| anyhow::anyhow!(\"Failed to insert into account_event table: {e}\"))?;\n        transaction\n            .commit()\n            .await\n            .map_err(|e| anyhow::anyhow!(\"Failed to commit add_account transaction: {e}\"))\n    }\n\n    /// Loads all account events for `account_id` via the provided `pool`.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the SQL SELECT or deserialization fails.\n    pub async fn load_account_events(\n        pool: &PgPool,\n        account_id: &AccountId,\n    ) -> anyhow::Result<Vec<AccountState>> {\n        sqlx::query_as::<_, AccountEventRow>(\n            r#\"SELECT * FROM \"account_event\" WHERE account_id = $1 ORDER BY created_at ASC\"#,\n        )\n        .bind(account_id.to_string())\n        .fetch_all(pool)\n        .await\n        .map(|rows| rows.into_iter().map(|row| row.0).collect())","sourceCodeStart":1141,"sourceCodeEnd":1177,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/infrastructure/src/sql/queries.rs#L1141-L1177","documentation":"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).","triggerScenarios":"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.","commonSituations":"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.","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'."],"exampleFix":"// before: single attempt, hard failure on commit\nadd_account(&pool, updated, state).await?;\n\n// after: safe retry (upserts are idempotent, failed commit rolled back)\nfor attempt in 0..3 {\n    match add_account(&pool, updated, state.clone()).await {\n        Ok(()) => break,\n        Err(e) if attempt < 2 && is_transient(&e) => tokio::time::sleep(backoff(attempt)).await,\n        Err(e) => return Err(e),\n    }\n}","handlingStrategy":"retry","validationCode":"// preflight connection stability and timeout config\nlet row = sqlx::query(\"SHOW idle_in_transaction_session_timeout\").fetch_one(pool).await?;\n// ensure commits are not starved by long idle-in-transaction windows","typeGuard":null,"tryCatchPattern":"for attempt in 0..3 {\n    match add_account(&pool, updated, state.clone()).await {\n        Ok(()) => break,\n        Err(e) if attempt < 2 && e.to_string().contains(\"commit\") => {\n            tokio::time::sleep(Duration::from_millis(200 * 2u64.pow(attempt))).await;\n        }\n        Err(e) => return Err(e),\n    }\n}","preventionTips":["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."],"tags":["database","postgres","transaction","commit","network"],"backgroundTag":"database-write-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"}