{"record":{"id":"79e233c3e96cf239","repo":"nautechsystems/nautilus_trader","slug":"failed-to-batch-insert-into-pool-position-table","errorCode":null,"errorMessage":"Failed to batch insert into pool_position table: {e}","messagePattern":"Failed to batch insert into pool_position table: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":2229,"sourceCode":"        .bind(snapshot_transaction_index as i32)\n        .bind(snapshot_log_index as i32)\n        .bind(&pool_identifiers[..])\n        .bind(&owners[..])\n        .bind(&tick_lowers[..])\n        .bind(&tick_uppers[..])\n        .bind(&liquidities[..])\n        .bind(&fee_growth_inside_0_lasts[..])\n        .bind(&fee_growth_inside_1_lasts[..])\n        .bind(&tokens_owed_0s[..])\n        .bind(&tokens_owed_1s[..])\n        .bind(&total_amount0_depositeds as &[Option<String>])\n        .bind(&total_amount1_depositeds as &[Option<String>])\n        .bind(&total_amount0_collecteds as &[Option<String>])\n        .bind(&total_amount1_collecteds as &[Option<String>])\n        .execute(&self.pool)\n        .await\n        .map(|_| ())\n        .map_err(|e| anyhow::anyhow!(\"Failed to batch insert into pool_position table: {e}\"))\n    }\n\n    /// Inserts multiple pool ticks in a single database operation using UNNEST for optimal performance.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the database operation fails.\n    pub async fn add_pool_ticks_batch(\n        &self,\n        chain_id: u32,\n        snapshot_block: u64,\n        snapshot_transaction_index: u32,\n        snapshot_log_index: u32,\n        ticks: &[(PoolIdentifier, &PoolTick)],\n    ) -> anyhow::Result<()> {\n        if ticks.is_empty() {\n            return Ok(());\n        }","sourceCodeStart":2211,"sourceCodeEnd":2247,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L2211-L2247","documentation":"Raised when a batched UNNEST INSERT of pool positions (including total deposited/collected amount strings as nullable text) into the `pool_position` table fails at `.execute`. The sqlx error is wrapped with this message and the position batch is not persisted.","triggerScenarios":"Executing the batch position insert (`.bind(&total_amount1_depositeds as &[Option<String>])`, `.bind(&total_amount0_collecteds ...)`, `.execute(&self.pool)`) when Postgres rejects it: constraint violation, type mismatch on amount/numeric columns, array-length mismatch across the many bound vectors, or connection failure.","commonSituations":"Amount strings exceeding numeric precision; duplicate positions (same token_id) conflicting with a PK; schema drift after migrations; very large batches hitting statement size or parameter limits; stale pool connections.","solutions":["Inspect the `{e}` tail for the underlying sqlx/PostgreSQL error","Verify all parallel arrays bound via UNNEST have identical lengths","Run migrations so pool_position columns match the bound fields","Add ON CONFLICT (e.g. on token_id) or deduplicate the batch for idempotent replay","Chunk very large batches to stay within parameter limits and check pool health"],"exampleFix":"// before\n.map_err(|e| anyhow::anyhow!(\"Failed to batch insert into pool_position table: {e}\"))\n// after: idempotent insert + context\n// INSERT ... ON CONFLICT (token_id) DO UPDATE SET ...\n.map_err(|e| anyhow::anyhow!(\"Failed to batch insert into pool_position table (rows={}): {e}\", token_ids.len()))","handlingStrategy":"try-catch","validationCode":"fn validate_position_batch(positions: &[PoolPosition]) -> anyhow::Result<()> {\n    for p in positions {\n        anyhow::ensure!(p.token_id > 0, \"invalid token_id\");\n        anyhow::ensure!(p.pool_identifier.iter().all(|b| !b.is_empty()), \"empty pool id\");\n    }\n    Ok(())\n}","typeGuard":"fn has_valid_amounts(p: &PoolPosition) -> bool {\n    p.total_amount1_deposited.as_ref().map_or(true, |s| !s.is_empty())\n}","tryCatchPattern":"match insert_pool_positions(&positions).await {\n    Ok(()) => {},\n    Err(e) if e.to_string().contains(\"duplicate key\") => tracing::debug!(\"positions already stored\"),\n    Err(e) => return Err(e.into()),\n}","preventionTips":["Chunk large batches to stay under parameter limits","Use ON CONFLICT on token_id for idempotent upserts","Ensure all UNNEST arrays match in length","Verify numeric precision for amount strings before binding"],"tags":["database","postgres","sqlx","batch-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-14T00:17:10.932Z"}