{"record":{"id":"ad71ce445ad8c1e4","repo":"nautechsystems/nautilus_trader","slug":"failed-to-batch-insert-into-pool-fee-collect-table","errorCode":null,"errorMessage":"Failed to batch insert into pool_fee_collect table: {e}","messagePattern":"Failed to batch insert into pool_fee_collect table: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":1777,"sourceCode":"            ON CONFLICT (chain_id, transaction_hash, log_index) DO NOTHING\n           \",\n        )\n        .bind(&chain_ids[..])\n        .bind(&dex_names[..])\n        .bind(&pool_identifiers[..])\n        .bind(&blocks[..])\n        .bind(&transaction_hashes[..])\n        .bind(&transaction_indices[..])\n        .bind(&log_indices[..])\n        .bind(&owners[..])\n        .bind(&amount0s[..])\n        .bind(&amount1s[..])\n        .bind(&tick_lowers[..])\n        .bind(&tick_uppers[..])\n        .execute(&self.pool)\n        .await\n        .map(|_| ())\n        .map_err(|e| anyhow::anyhow!(\"Failed to batch insert into pool_fee_collect table: {e}\"))\n    }\n\n    /// Inserts multiple pool flash events 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_flash_batch(\n        &self,\n        chain_id: u32,\n        flash_events: &[PoolFlash],\n    ) -> anyhow::Result<()> {\n        if flash_events.is_empty() {\n            return Ok(());\n        }\n\n        // Prepare vectors for each column\n        let len = flash_events.len();","sourceCodeStart":1759,"sourceCodeEnd":1795,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L1759-L1795","documentation":"This error is raised by the blockchain cache adapter when a batched UNNEST INSERT of Uniswap V3 pool fee-collect events into the `pool_fee_collect` table fails. The underlying sqlx/PostgreSQL error `e` is wrapped with anyhow and prefixed with this message, so the database driver's reason (constraint violation, type mismatch, connection loss) is preserved at the end of the message. It aborts the cache-write path, meaning the collected-fee event batch was not persisted.","triggerScenarios":"Calling the batch-insert method for pool fee-collect events (multiple `.bind(&...)` arrays followed by `.execute(&self.pool)`) when the database rejects the statement: a column constraint fails, an array length mismatch exists among bound parallel arrays, the pool connection is dead, or a value's text representation does not fit the column type.","commonSituations":"Database migrations out of sync with the struct fields being bound (new/renamed columns); arrays of differing lengths constructed from event batches; connection-pool exhaustion or Postgres restarts during long indexing runs; NULL values bound to NOT NULL columns; schema type changes (e.g. numeric vs text) between adapter versions.","solutions":["Read the wrapped `{e}` tail of the message to get the exact sqlx/PostgreSQL error and address that root cause","Verify the database schema matches the columns bound in the INSERT (run pending migrations)","Check that all parallel arrays bound via UNNEST have identical lengths before executing","Check `self.pool` health: verify connectivity, pool size limits, and that the Postgres instance is up","Retry the batch insert once the connection is healthy; batches are idempotent only if keyed, so use ON CONFLICT if duplicates are possible"],"exampleFix":"// before\n.execute(&self.pool)\n.await\n.map(|_| ())\n.map_err(|e| anyhow::anyhow!(\"Failed to batch insert into pool_fee_collect table: {e}\"))\n// after: validate array lengths first and surface more context\nassert_eq!(pool_identifiers.len(), amounts0.len(), \"UNNEST arrays must match\");\n.execute(&self.pool)\n.await\n.map(|_| ())\n.map_err(|e| anyhow::anyhow!(\"Failed to batch insert into pool_fee_collect table (n={}): {e}\", pool_identifiers.len()))","handlingStrategy":"try-catch","validationCode":"fn validate_fee_collect_batch(rows: &[PoolFeeCollectEvent]) -> anyhow::Result<()> {\n    let n = rows.len();\n    anyhow::ensure!(n > 0, \"empty fee-collect batch\");\n    anyhow::ensure!(rows.iter().all(|r| !r.pool_identifier.is_empty()), \"empty pool_identifier\");\n    Ok(())\n}","typeGuard":"fn is_valid_i32(v: u64) -> bool { v <= i32::MAX as u64 }","tryCatchPattern":"match insert_pool_fee_collects(&events).await {\n    Ok(()) => {},\n    Err(e) if e.to_string().contains(\"connection\") => backoff_and_retry(&events).await?,\n    Err(e) => tracing::error!(\"fee-collect batch dropped: {e:#}\"),\n}","preventionTips":["Validate all parallel arrays have equal lengths before any UNNEST insert","Keep migrations in sync with the adapter struct fields","Use ON CONFLICT clauses for idempotent log replay","Monitor database connectivity and pool saturation during indexing"],"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"}