{"record":{"id":"cdfa1e229eecc756","repo":"nautechsystems/nautilus_trader","slug":"failed-to-batch-insert-into-pool-table-e","errorCode":null,"errorMessage":"Failed to batch insert into pool table: {e}","messagePattern":"Failed to batch insert into pool table: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":923,"sourceCode":"        )\n        .bind(&chain_ids[..])\n        .bind(&addresses[..])\n        .bind(&pool_identifiers[..])\n        .bind(&dex_names[..])\n        .bind(&creation_blocks[..])\n        .bind(&token0_chains[..])\n        .bind(&token0_addresses[..])\n        .bind(&token1_chains[..])\n        .bind(&token1_addresses[..])\n        .bind(&fees[..])\n        .bind(&tick_spacings[..])\n        .bind(&initial_ticks[..])\n        .bind(&initial_sqrt_price_x96s[..])\n        .bind(&hook_addresses as &[Option<String>])\n        .execute(&self.pool)\n        .await\n        .map(|_| ())\n        .map_err(|e| anyhow::anyhow!(\"Failed to batch insert into pool table: {e}\"))\n    }\n\n    /// Inserts multiple pool swaps 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_swaps_batch(\n        &self,\n        chain_id: u32,\n        swaps: &[PoolSwap],\n    ) -> anyhow::Result<()> {\n        if swaps.is_empty() {\n            return Ok(());\n        }\n\n        // Prepare vectors for each column\n        let len = swaps.len();","sourceCodeStart":905,"sourceCodeEnd":941,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L905-L941","documentation":"UNNEST-based batch insert of multiple pools into the `pool` table failed; wrapped with this message. This is the bulk variant of the single pool insert, where all column arrays are bound as slices and unnested in one statement.","triggerScenarios":"Calling the batch pool insert with arrays of unequal length (Postgres 'UNNEST types/lengths mismatch'), an element in initial_sqrt_price_x96s or hook_addresses that fails the column cast, FK violation for pools whose DEX is missing, or execute failure on the pool connection.","commonSituations":"Bulk-loading pools from a snapshot where some pools' DEX rows were skipped; building the hook_addresses Option<String> vec at a different length after filtering; schema drift (missing hooks column in older DBs); oversized batch hitting statement size limits.","solutions":["Check the wrapped `{e}`: 'unequal lengths' → align all bound slices; FK message → insert DEX rows first","Assert every bound slice (addresses, dex ids, ticks, prices, hooks) has the same length before the call","Run migrations so all pool columns exist; ensure parent DEX records are persisted before the batch","Chunk very large batches (e.g. 1k-10k rows) to avoid oversized statements and to isolate failing rows"],"exampleFix":"// before\nlet hooks: Vec<Option<String>> = pools.iter().filter_map(|p| p.hooks.map(|h| h.to_string())).collect(); // wrong length\n// after\nlet hooks: Vec<Option<String>> = pools.iter().map(|p| p.hooks.map(|h| h.to_string())).collect(); // aligned\nassert_eq!(hooks.len(), pools.len());","handlingStrategy":"validation","validationCode":"let n = pools.len();\nanyhow::ensure!(addresses.len() == n && dex_ids.len() == n && initial_ticks.len() == n\n    && initial_sqrt_price_x96s.len() == n && hook_addresses.len() == n, \"UNNEST arrays misaligned\");\nanyhow::ensure!(!pools.is_empty(), \"empty pool batch\");","typeGuard":"fn batch_aligned(len: usize, slices: &[&[impl Sized]]) -> bool {\n    slices.iter().all(|s| s.len() == len)\n}","tryCatchPattern":"for chunk in pools.chunks(5_000) {\n    if let Err(e) = db.insert_pools_batch(chunk).await {\n        return Err(e.context(format!(\"pool batch insert failed at chunk starting index {}\", chunk[0].index)));\n    }\n}","preventionTips":["Derive every bound array from the same iteration over the input vec so lengths always match","Insert parent DEX rows before the pool batch","Chunk large batches to bound statement size and localize failures","Keep schema migrations applied before bulk loads"],"tags":["database","postgres","sqlx","insert","batch"],"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"}