{"record":{"id":"548b41a74415dc61","repo":"nautechsystems/nautilus_trader","slug":"failed-to-insert-into-position-event-table-e","errorCode":null,"errorMessage":"Failed to insert into position_event table: {e}","messagePattern":"Failed to insert into position_event table: (.+?)","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"crates/infrastructure/src/sql/queries.rs","lineNumber":1074,"sourceCode":"        .bind(event.order_side.to_string())\n        .bind(event.last_px.to_string())\n        .bind(event.last_qty.to_string())\n        .bind(event.liquidity_side.to_string())\n        .bind(position_id.to_string())\n        .bind(event.commission.map(|commission| commission.to_string()))\n        .bind(event.reconciliation)\n        .bind(position_event_info)\n        .bind(\n            event\n                .causation_id\n                .map(|causation_id| causation_id.to_string()),\n        )\n        .bind(event.ts_event.to_string())\n        .bind(event.ts_init.to_string())\n        .execute(&mut **transaction)\n        .await\n        .map(|_| ())\n        .map_err(|e| anyhow::anyhow!(\"Failed to insert into position_event table: {e}\"))\n    }\n\n    fn event_position_id(event: &OrderFilled) -> anyhow::Result<PositionId> {\n        event.position_id.ok_or_else(|| {\n            anyhow::anyhow!(\n                \"Cannot persist position event with no position_id: {}\",\n                event.event_id\n            )\n        })\n    }\n\n    /// Inserts or updates an `AccountState` event via the provided `pool`.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the SQL INSERT or UPDATE operation fails.\n    pub async fn add_account(\n        pool: &PgPool,","sourceCodeStart":1056,"sourceCodeEnd":1092,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/infrastructure/src/sql/queries.rs#L1056-L1092","documentation":"This is the final INSERT INTO \"position_event\" inside insert_position_event failing. All 22 bound columns (event id, ids, prices, timestamps, etc.) are bound as strings, so failures are dominated by DB-level causes: connection issues, schema mismatch (missing columns/types after migrations), or constraint violations such as a foreign key on trader_id or a duplicate event id under a unique index.","triggerScenarios":"Persisting an OrderFilled event when the position_event INSERT is rejected — schema drift (missing column like liquidity_side or commission after an upgrade), duplicate event_id with a unique constraint, FK violation, dead connection, or a value that cannot be cast to the column type.","commonSituations":"Upgrading NautilusTrader without re-running DB migrations so the Rust code binds columns the table lacks; replaying historical data into a DB that already contains those event ids; the Postgres connection dropped after a long-running backtest write burst; wrong database pointed to by the connection URL (stale/empty schema).","solutions":["Read the embedded sqlx error: 'relation does not exist' or 'column does not exist' means run the latest migrations; 'duplicate key' means the event was already persisted.","Verify the connection URL points at the schema version matching this crate build.","For duplicate-key errors during replays, make the insert idempotent (ON CONFLICT DO NOTHING) or deduplicate events before persisting.","For connection errors, retry the transaction with backoff; check pool limits and server logs."],"exampleFix":"// before: hard failure on replay duplicates\nINSERT INTO \"position_event\" (...) VALUES (...)\n\n// after: idempotent persist\nINSERT INTO \"position_event\" (...) VALUES (...)\nON CONFLICT (id) DO NOTHING","handlingStrategy":"retry","validationCode":"// preflight: schema present and no duplicate event id\nlet schema_ok = sqlx::query(\"SELECT to_regclass('\"\"position_event\"\"') IS NOT NULL\").fetch_one(pool).await?;\nlet dup = sqlx::query(\"SELECT 1 FROM \\\"position_event\\\" WHERE id = $1\")\n    .bind(event.event_id.to_string()).fetch_optional(pool).await?;","typeGuard":null,"tryCatchPattern":"match persist(&event).await {\n    Err(e) if e.to_string().contains(\"duplicate key\") => log::debug!(\"already persisted: {}\", event.event_id),\n    Err(e) if is_transient(&e) => retry_with_backoff(|| persist(&event)).await?,\n    Err(e) => return Err(e),\n    Ok(()) => {},\n}","preventionTips":["Run migrations on every deploy and verify schema version matches the crate build.","Use ON CONFLICT DO NOTHING for idempotent replay-safe persistence.","Monitor connection health and enable TCP keepalive on the pool.","Classify sqlx errors (schema vs constraint vs connectivity) before retrying."],"tags":["database","postgres","sqlx","persistence","insert"],"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-14T05:17:10.506Z"}