nautechsystems/nautilus_trader · error · anyhow::Error

Failed to batch insert into pool_position table: {e}

Error message

Failed to batch insert into pool_position table: {e}

What it means

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.

Source

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

        .bind(snapshot_transaction_index as i32)
        .bind(snapshot_log_index as i32)
        .bind(&pool_identifiers[..])
        .bind(&owners[..])
        .bind(&tick_lowers[..])
        .bind(&tick_uppers[..])
        .bind(&liquidities[..])
        .bind(&fee_growth_inside_0_lasts[..])
        .bind(&fee_growth_inside_1_lasts[..])
        .bind(&tokens_owed_0s[..])
        .bind(&tokens_owed_1s[..])
        .bind(&total_amount0_depositeds as &[Option<String>])
        .bind(&total_amount1_depositeds as &[Option<String>])
        .bind(&total_amount0_collecteds as &[Option<String>])
        .bind(&total_amount1_collecteds as &[Option<String>])
        .execute(&self.pool)
        .await
        .map(|_| ())
        .map_err(|e| anyhow::anyhow!("Failed to batch insert into pool_position table: {e}"))
    }

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

View on GitHub (pinned to 18893faf8b)

Solutions

1. Inspect the `{e}` tail for the underlying sqlx/PostgreSQL error
2. Verify all parallel arrays bound via UNNEST have identical lengths
3. Run migrations so pool_position columns match the bound fields
4. Add ON CONFLICT (e.g. on token_id) or deduplicate the batch for idempotent replay
5. Chunk very large batches to stay within parameter limits and check pool health

Example fix

// before
.map_err(|e| anyhow::anyhow!("Failed to batch insert into pool_position table: {e}"))
// after: idempotent insert + context
// INSERT ... ON CONFLICT (token_id) DO UPDATE SET ...
.map_err(|e| anyhow::anyhow!("Failed to batch insert into pool_position table (rows={}): {e}", token_ids.len()))

fn validate_position_batch(positions: &[PoolPosition]) -> anyhow::Result<()> {
    for p in positions {
        anyhow::ensure!(p.token_id > 0, "invalid token_id");
        anyhow::ensure!(p.pool_identifier.iter().all(|b| !b.is_empty()), "empty pool id");
    }
    Ok(())
}

fn has_valid_amounts(p: &PoolPosition) -> bool {
    p.total_amount1_deposited.as_ref().map_or(true, |s| !s.is_empty())
}

match insert_pool_positions(&positions).await {
    Ok(()) => {},
    Err(e) if e.to_string().contains("duplicate key") => tracing::debug!("positions already stored"),
    Err(e) => return Err(e.into()),
}

When it happens

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

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

Related errors

• Failed to batch insert into pool_fee_collect table: {e}
• 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_tick table: {e}

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