nautechsystems/nautilus_trader · error · anyhow::Error

Failed to batch insert into pool table

Error message

Failed to batch insert into pool table: {e}

What it means

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.

Solutions

1. Check the wrapped `{e}`: 'unequal lengths' → align all bound slices; FK message → insert DEX rows first
2. Assert every bound slice (addresses, dex ids, ticks, prices, hooks) has the same length before the call
3. Run migrations so all pool columns exist; ensure parent DEX records are persisted before the batch
4. Chunk very large batches (e.g. 1k-10k rows) to avoid oversized statements and to isolate failing rows

Example fix

// before
let hooks: Vec<Option<String>> = pools.iter().filter_map(|p| p.hooks.map(|h| h.to_string())).collect(); // wrong length
// after
let hooks: Vec<Option<String>> = pools.iter().map(|p| p.hooks.map(|h| h.to_string())).collect(); // aligned
assert_eq!(hooks.len(), pools.len());

let n = pools.len();
anyhow::ensure!(addresses.len() == n && dex_ids.len() == n && initial_ticks.len() == n
    && initial_sqrt_price_x96s.len() == n && hook_addresses.len() == n, "UNNEST arrays misaligned");
anyhow::ensure!(!pools.is_empty(), "empty pool batch");

fn batch_aligned(len: usize, slices: &[&[impl Sized]]) -> bool {
    slices.iter().all(|s| s.len() == len)
}

for chunk in pools.chunks(5_000) {
    if let Err(e) = db.insert_pools_batch(chunk).await {
        return Err(e.context(format!("pool batch insert failed at chunk starting index {}", chunk[0].index)));
    }
}

When it happens

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

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

Related errors

• Failed to batch insert into pool_liquidity_event table
• Failed to batch insert into pool_swap_event table
• Failed to batch insert into block table
• Failed to batch insert into pool_event_block table
• Failed to insert instrument close

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

Appendix: source

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

        )
        .bind(&chain_ids[..])
        .bind(&addresses[..])
        .bind(&pool_identifiers[..])
        .bind(&dex_names[..])
        .bind(&creation_blocks[..])
        .bind(&token0_chains[..])
        .bind(&token0_addresses[..])
        .bind(&token1_chains[..])
        .bind(&token1_addresses[..])
        .bind(&fees[..])
        .bind(&tick_spacings[..])
        .bind(&initial_ticks[..])
        .bind(&initial_sqrt_price_x96s[..])
        .bind(&hook_addresses as &[Option<String>])
        .execute(&self.pool)
        .await
        .map(|_| ())
        .map_err(|e| anyhow::anyhow!("Failed to batch insert into pool table: {e}"))
    }

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

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

View on GitHub (pinned to 18893faf8b)