nautechsystems/nautilus_trader · error · anyhow::Error

Failed to batch insert into pool_fee_collect table: {e}

Error message

Failed to batch insert into pool_fee_collect table: {e}

What it means

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.

Source

Thrown at crates/adapters/blockchain/src/cache/database.rs:1777

            ON CONFLICT (chain_id, transaction_hash, log_index) DO NOTHING
           ",
        )
        .bind(&chain_ids[..])
        .bind(&dex_names[..])
        .bind(&pool_identifiers[..])
        .bind(&blocks[..])
        .bind(&transaction_hashes[..])
        .bind(&transaction_indices[..])
        .bind(&log_indices[..])
        .bind(&owners[..])
        .bind(&amount0s[..])
        .bind(&amount1s[..])
        .bind(&tick_lowers[..])
        .bind(&tick_uppers[..])
        .execute(&self.pool)
        .await
        .map(|_| ())
        .map_err(|e| anyhow::anyhow!("Failed to batch insert into pool_fee_collect table: {e}"))
    }

    /// Inserts multiple pool flash events in a single database operation using UNNEST for optimal performance.
    ///
    /// # Errors
    ///
    /// Returns an error if the database operation fails.
    pub async fn add_pool_flash_batch(
        &self,
        chain_id: u32,
        flash_events: &[PoolFlash],
    ) -> anyhow::Result<()> {
        if flash_events.is_empty() {
            return Ok(());
        }

        // Prepare vectors for each column
        let len = flash_events.len();

View on GitHub (pinned to 18893faf8b)

Solutions

1. Read the wrapped `{e}` tail of the message to get the exact sqlx/PostgreSQL error and address that root cause
2. Verify the database schema matches the columns bound in the INSERT (run pending migrations)
3. Check that all parallel arrays bound via UNNEST have identical lengths before executing
4. Check `self.pool` health: verify connectivity, pool size limits, and that the Postgres instance is up
5. Retry the batch insert once the connection is healthy; batches are idempotent only if keyed, so use ON CONFLICT if duplicates are possible

Example fix

// before
.execute(&self.pool)
.await
.map(|_| ())
.map_err(|e| anyhow::anyhow!("Failed to batch insert into pool_fee_collect table: {e}"))
// after: validate array lengths first and surface more context
assert_eq!(pool_identifiers.len(), amounts0.len(), "UNNEST arrays must match");
.execute(&self.pool)
.await
.map(|_| ())
.map_err(|e| anyhow::anyhow!("Failed to batch insert into pool_fee_collect table (n={}): {e}", pool_identifiers.len()))

fn validate_fee_collect_batch(rows: &[PoolFeeCollectEvent]) -> anyhow::Result<()> {
    let n = rows.len();
    anyhow::ensure!(n > 0, "empty fee-collect batch");
    anyhow::ensure!(rows.iter().all(|r| !r.pool_identifier.is_empty()), "empty pool_identifier");
    Ok(())
}

fn is_valid_i32(v: u64) -> bool { v <= i32::MAX as u64 }

match insert_pool_fee_collects(&events).await {
    Ok(()) => {},
    Err(e) if e.to_string().contains("connection") => backoff_and_retry(&events).await?,
    Err(e) => tracing::error!("fee-collect batch dropped: {e:#}"),
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• Failed to batch insert into pool_flash_event table: {e}
• Failed to batch insert into pool_fee_protocol_update_event t
• Failed to batch insert into pool_fee_protocol_collect_event
• Failed to batch insert into pool_position table: {e}
• Failed to batch insert into pool_tick table: {e}

AI-assisted analysis of nautechsystems/nautilus_trader@18893faf8b (2026-09-08). Data as JSON: /api/errors/ad71ce445ad8c1e4. Report an issue: GitHub.