What causes "Failed to update execution hash {transaction_hash}: {e}" in nautechsystems/nautilus_trader?

Downcast to the root cause: err.downcast_ref:: () and read as_database_error() code/message to classify connection vs constraint vs timeout

nautechsystems/nautilus_trader · error · anyhow::Error

Failed to update execution hash {transaction_hash}: {e}

Error message

Failed to update execution hash {transaction_hash}: {e}

What it means

Wrapped sqlx error from the UPDATE execution_transaction_hash statement inside record_execution_status (crates/adapters/blockchain/src/cache/database.rs:3638-3661), which persists a receipt observation (status, block_number, block_hash, receipt_success, gas_used, effective_gas_price) for one (intent_id, transaction_hash) pair. The {e} suffix carries the underlying sqlx::Error; this message only identifies which statement failed. The statement runs inside a transaction that already holds a FOR UPDATE lock on the intent row, so the most common causes are infrastructure faults or schema drift, not row contention.

Source

Thrown at crates/adapters/blockchain/src/cache/database.rs:3661

                block_hash = COALESCE($5, block_hash),
                receipt_success = COALESCE($6, receipt_success),
                gas_used = COALESCE($7, gas_used),
                effective_gas_price = COALESCE($8, effective_gas_price),
                updated_at = NOW()
            WHERE intent_id = $1 AND transaction_hash = $2
            ",
        )
        .bind(intent_id)
        .bind(transaction_hash)
        .bind(status.as_str())
        .bind(block_number_db)
        .bind(block_hash)
        .bind(receipt_success)
        .bind(gas_used_db)
        .bind(effective_gas_price)
        .execute(&mut *transaction)
        .await
        .map_err(|e| anyhow::anyhow!("Failed to update execution hash {transaction_hash}: {e}"))?;
        anyhow::ensure!(
            hash_result.rows_affected() == 1,
            "Execution transaction hash {transaction_hash} was not found for intent {intent_id}"
        );

        sqlx::query(
            "
            UPDATE execution_intent
            SET status = $2, active = $3, updated_at = NOW()
            WHERE id = $1
            ",
        )
        .bind(intent_id)
        .bind(status.as_str())
        .bind(active)
        .execute(&mut *transaction)
        .await
        .map_err(|e| anyhow::anyhow!("Failed to update execution intent {intent_id}: {e}"))?;

View on GitHub (pinned to 2114cf6f76)

Solutions

Downcast to the root cause: err.downcast_ref::<sqlx::Error>() and read as_database_error() code/message to classify connection vs constraint vs timeout
Verify the schema is current: run the project's migrations so execution_transaction_hash matches the eight bound columns
For timeout/deadlock SQLSTATEs (57014/40P01/40001) retry the whole call with backoff - the transaction rolls back atomically and the transition_key makes the flow idempotent
If the cause is PoolTimedOut/Io, check Postgres reachability and pool sizing (max_connections, acquire_timeout)
Fix the offending bind value if a CHECK/NOT NULL/type violation is reported for a specific column

Example fix

// before: single shot, any error aborts receipt processing
let _ = db.record_execution_status(intent_id, hash, status, block, block_hash, ok, gas, price).await?;

// after: classify and retry transient failures (rollback makes the call idempotent)
for attempt in 1..=3u32 {
    match db.record_execution_status(intent_id, hash, status, block, block_hash, ok, gas, price).await {
        Ok(()) => break,
        Err(e) if attempt < 3 && is_transient_db_error(&e) => {
            tokio::time::sleep(std::time::Duration::from_millis(100u64 * 2u64.pow(attempt))).await;
        }
        Err(e) => return Err(e),
    }
}

Defensive patterns

Strategy: retry

Type guard

fn is_transient_db_error(err: &anyhow::Error) -> bool {
    match err.downcast_ref::<sqlx::Error>() {
        Some(sqlx::Error::PoolTimedOut | sqlx::Error::PoolClosed | sqlx::Error::Io(_)) => true,
        Some(e) => e.as_database_error().and_then(|d| d.code()).map_or(false, |c| {
            matches!(c.as_ref(), "40001" | "40P01" | "55P03" | "57014" | "08000" | "08003" | "08006")
        }),
        None => false,
    }
}

Try / catch

match db.record_execution_status(...).await {
    Ok(()) => {}
    Err(e) if is_transient_db_error(&e) => retry_with_backoff(e), // whole call is idempotent after rollback
    Err(e) => return Err(e), // constraint/schema faults: fix data or migrations, do not retry
}

Prevention

Run migrations before deploying a new adapter version so execution_transaction_hash always matches the bound columns
Keep begin-to-commit windows short to limit exposure to timeouts and failovers
Monitor sqlx pool acquire timeouts and database error codes; alert on anything outside transient classes
Log err.chain() (root_cause) instead of only the top-level message so classification is possible after the fact

When it happens

Trigger: Calling record_execution_status when Postgres rejects or never receives the UPDATE: connection dropped mid-transaction, statement_timeout firing while waiting on other row locks, a serialization/deadlock abort (40001/40P01), a CHECK/NOT NULL violation on a bound column (e.g., an unexpected status string), or a database whose execution_transaction_hash schema lacks one of the eight bound columns because migrations were not applied.

Common situations: Postgres restart or failover while the blockchain watcher is processing receipts; running the cache against a database created by an older adapter version whose schema predates gas_used/effective_gas_price receipt columns; a concurrent watcher holding the intent lock past statement_timeout; connecting through a pool left half-dead after a network blip.

Related errors

AI-assisted analysis of nautechsystems/nautilus_trader@2114cf6f76 (2026-08-21). Data as JSON: /api/errors/c978689eb4b344b3. Report an issue: GitHub.