{"record":{"id":"0a97af296c6066a2","repo":"nautechsystems/nautilus_trader","slug":"execution-payload-rollback-left-invalid-invalid","errorCode":null,"errorMessage":"Execution payload rollback left {invalid} invalid row(s)","messagePattern":"Execution payload rollback left (.+?) invalid row\\(s\\)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":5668,"sourceCode":"        .bind(batch_size)\n        .fetch_all(&mut *transaction)\n        .await\n        .context(\"failed to load execution payload rollback batch\")?;\n\n        if rows.is_empty() {\n            sqlx::query(\"LOCK TABLE execution_transaction_hash IN SHARE ROW EXCLUSIVE MODE\")\n                .execute(&mut *transaction)\n                .await\n                .context(\"failed to lock execution payload rollback completion\")?;\n            let invalid = sqlx::query_scalar::<_, i64>(\n                \"SELECT COUNT(*) FROM execution_transaction_hash \\\n                 WHERE (payload_expected AND (raw_transaction IS NULL OR sealed_transaction IS NOT NULL)) \\\n                    OR (NOT payload_expected AND (raw_transaction IS NOT NULL OR sealed_transaction IS NOT NULL))\",\n            )\n            .fetch_one(&mut *transaction)\n            .await\n            .context(\"failed to verify execution payload rollback completion\")?;\n            anyhow::ensure!(\n                invalid == 0,\n                \"Execution payload rollback left {invalid} invalid row(s)\"\n            );\n\n            for statement in [\n                \"DROP TRIGGER IF EXISTS execution_transaction_payload_fence ON execution_transaction_hash\",\n                \"DELETE FROM execution_payload_state WHERE component = 'signed_transactions'\",\n                \"DELETE FROM execution_schema_version WHERE component = 'evm_execution_payload'\",\n            ] {\n                sqlx::query(statement)\n                    .execute(&mut *transaction)\n                    .await\n                    .context(\"failed to complete execution payload rollback\")?;\n            }\n            transaction\n                .commit()\n                .await\n                .context(\"failed to commit execution payload rollback completion\")?;","sourceCodeStart":5650,"sourceCodeEnd":5686,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L5650-L5686","documentation":"When the rollback batch finds no remaining sealed rows, it locks the whole execution_transaction_hash table and counts rows whose payload representation contradicts their payload_expected flag (raw/sealed present or missing in the wrong combination). If that count is nonzero, the rollback is incomplete or inconsistent, so it refuses to drop the fence trigger and protection state, throwing this error instead of committing a partially converted table.","triggerScenarios":"Completing the final rollback batch while some rows still have raw_transaction IS NULL despite payload_expected, sealed_transaction NOT NULL, or rows with payloads despite payload_expected=false — i.e. rows modified outside the rollback loop, inserted during rollback with wrong representation, or corrupted by manual edits.","commonSituations":"Rows inserted by a concurrent writer (with a stale trigger/fence) during a long rollback; manual data fixes that set raw_transaction or sealed_transaction inconsistently; restoring the table from a partial backup; a previous failed migration that left mixed-representation rows.","solutions":["Run the same COUNT query to identify the offending rows: SELECT id FROM execution_transaction_hash WHERE (payload_expected AND (raw_transaction IS NULL OR sealed_transaction IS NOT NULL)) OR (NOT payload_expected AND (raw_transaction IS NOT NULL OR sealed_transaction IS NOT NULL)).","Fix the inconsistent rows (backfill raw_transaction for payload_expected rows, or clear payloads for rows not expecting them) so each row matches its payload_expected flag.","Ensure all writers are stopped or using a schema version consistent with the rollback before retrying rollback_execution_payload.","If rows are unrecoverable, quarantine or delete them deliberately, then rerun the rollback to completion."],"exampleFix":"-- diagnose\nSELECT id, payload_expected, raw_transaction IS NULL AS no_raw,\n       sealed_transaction IS NOT NULL AS has_sealed\nFROM execution_transaction_hash\nWHERE (payload_expected AND (raw_transaction IS NULL OR sealed_transaction IS NOT NULL))\n   OR (NOT payload_expected AND (raw_transaction IS NOT NULL OR sealed_transaction IS NOT NULL));\n\n// before: retrying rollback with inconsistent rows\n// db.rollback_execution_payload(&keys, 1000).await?; // fails with \"left N invalid row(s)\"\n// after: repair rows first, then retry\n// UPDATE execution_transaction_hash SET sealed_transaction = NULL WHERE id = $bad_id; -- after restoring raw_transaction","handlingStrategy":"validation","validationCode":"let invalid: i64 = sqlx::query_scalar(\n    \"SELECT COUNT(*) FROM execution_transaction_hash \\\n     WHERE (payload_expected AND (raw_transaction IS NULL OR sealed_transaction IS NOT NULL)) \\\n        OR (NOT payload_expected AND (raw_transaction IS NOT NULL OR sealed_transaction IS NOT NULL))\",\n).fetch_one(&mut conn).await?;\nif invalid != 0 {\n    return Err(anyhow!(\"{invalid} payload rows inconsistent; repair before rollback\"));\n}","typeGuard":null,"tryCatchPattern":"match db.rollback_execution_payload(&keys, batch).await {\n    Err(e) if e.to_string().contains(\"invalid row(s)\") => {\n        // run the diagnostic COUNT query, repair rows, retry once\n    },\n    other => other?,\n}","preventionTips":["Add a CHECK constraint mirroring the payload_expected/raw/sealed invariants so bad rows cannot be inserted.","Quiesce writers before starting a rollback.","Never restore partial backups directly into a live execution_transaction_hash table.","Periodically run the consistency COUNT query as a health check."],"tags":["database","data-integrity","rollback","postgresql","migration"],"backgroundTag":"internal-invariant-violation","analyzedSha":"18893faf8b356be3320add8de2f861b0b647cf06","analyzedAt":"2026-09-08T20:49:34.690Z","contentChangedAt":"2026-09-08T20:49:34.690Z","schemaVersion":2},"datasetVersion":"2026-09-14T05:17:10.506Z"}