{"record":{"id":"68217c373c99e0cb","repo":"nautechsystems/nautilus_trader","slug":"failed-to-insert-into-execution-transaction-table","errorCode":null,"errorMessage":"Failed to insert into execution_transaction table: {e}","messagePattern":"Failed to insert into execution_transaction table: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"critical","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":3025,"sourceCode":"            ON CONFLICT (chain_id, transaction_hash)\n            DO UPDATE SET transaction_hash = EXCLUDED.transaction_hash\n            WHERE execution_transaction.wallet_address = EXCLUDED.wallet_address\n              AND execution_transaction.nonce = EXCLUDED.nonce\n              AND execution_transaction.purpose = EXCLUDED.purpose\n              AND execution_transaction.status = EXCLUDED.status\n              AND execution_transaction.client_order_id IS NOT DISTINCT FROM EXCLUDED.client_order_id\n        \",\n        )\n        .bind(chain_id as i32)\n        .bind(wallet_address)\n        .bind(nonce as i64)\n        .bind(transaction_hash)\n        .bind(purpose)\n        .bind(status)\n        .bind(client_order_id)\n        .execute(&self.pool)\n        .await\n        .map_err(|e| anyhow::anyhow!(\"Failed to insert into execution_transaction table: {e}\"))?;\n\n        anyhow::ensure!(\n            result.rows_affected() == 1,\n            \"Execution transaction {transaction_hash} conflicts with its persisted record\"\n        );\n        Ok(())\n    }\n\n    /// Installs execution schema version 2 without changing existing transaction rows.\n    ///\n    /// The migration locks the legacy transaction table, refuses unresolved version 1 rows,\n    /// installs the versioned intent and hash-history tables, and fences older writers before\n    /// releasing the lock. This prevents a mixed-version process from bypassing the new signer\n    /// ownership constraints.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the database operation fails.","sourceCodeStart":3007,"sourceCodeEnd":3043,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/2114cf6f761429e0adb5ca9596fcd7b895b16011/crates/adapters/blockchain/src/cache/database.rs#L3007-L3043","documentation":"add_execution_transaction (crates/adapters/blockchain/src/cache/database.rs:2985) persists each signed on-chain transaction to the execution_transaction table before broadcast, so a signed transaction is never lost. The raw sqlx error is wrapped with this generic message; the actual cause is in {e}: connection failure, missing table/schema (migrations not applied), permission denied, constraint violation, or bind/type errors.","triggerScenarios":"Calling add_execution_transaction while Postgres is unreachable or restarting; before the blockchain cache schema migration created execution_transaction; with a DB role lacking INSERT; or with parameter values violating column constraints/types (e.g. chain_id beyond i32, oversized strings).","commonSituations":"Deploying the blockchain adapter without running its schema migrations; misconfigured Postgres host/credentials/pool; transient network drops between signer and database; Postgres restarted mid-session leaving stale connections.","solutions":["Read the embedded sqlx error {e} - it distinguishes connection refused, 'relation ... does not exist', permission denied, and constraint violations.","Run the blockchain cache migrations so execution_transaction exists in the target schema.","Verify connectivity, credentials, and privileges for the signer's database role.","Treat persistence failure as fatal before broadcast: do not broadcast a transaction that could not be recorded; halt submission and alert."],"exampleFix":null,"handlingStrategy":"retry","validationCode":"// Pre-flight: confirm the table is reachable and present before the trading session\nlet row: (i64,) = sqlx::query_as(\"SELECT count(*) FROM execution_transaction\")\n    .fetch_one(&pool)\n    .await?; // fails fast on missing table or connection","typeGuard":null,"tryCatchPattern":"match db.add_execution_transaction(...).await {\n    Ok(()) => {}\n    Err(e) if is_transient(&e) => {\n        // retry with backoff; still do NOT broadcast until persistence succeeds\n        retry_with_backoff(|| db.add_execution_transaction(...)).await?\n    }\n    Err(e) => {\n        log::error!(\"persistence failed, aborting broadcast: {e}\");\n        return Err(e);\n    }\n}","preventionTips":["Run the blockchain cache schema migrations before starting the signer.","Fail fast on connectivity/permission problems with a pre-flight query at startup.","Never broadcast a transaction whose persistence record failed; treat this error as a stop-the-line condition.","Parse the embedded sqlx error to separate transient (connection) from permanent (schema/privilege) causes."],"tags":["blockchain","postgres","persistence","sql"],"backgroundTag":"database-insert-failed","analyzedSha":"2114cf6f761429e0adb5ca9596fcd7b895b16011","analyzedAt":"2026-08-21T11:28:30.864Z","schemaVersion":2},"datasetVersion":"2026-08-22T14:17:55.899Z"}