nautechsystems/nautilus_trader · error · anyhow::Error

Failed to batch insert into pool_event_block table: {e}

Error message

Failed to batch insert into pool_event_block table: {e}

What it means

sqlx batch INSERT into `pool_event_block` (block number/hash/timestamp triples seen while streaming pool events) failed and is wrapped with this message. It exists to give context that the failing statement is the pool-event block bookkeeping write rather than a block-table write.

Source

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

                chain_id, number, hash, timestamp
            )
            SELECT
                $1, *
            FROM UNNEST(
                $2::int8[], $3::text[], $4::text[]
            )
            ON CONFLICT (chain_id, number)
            DO UPDATE SET hash = EXCLUDED.hash, timestamp = EXCLUDED.timestamp
           ",
        )
        .bind(chain_id_db)
        .bind(&numbers[..])
        .bind(&hashes[..])
        .bind(&timestamps[..])
        .execute(&self.pool)
        .await
        .map(|_| ())
        .map_err(|e| anyhow::anyhow!("Failed to batch insert into pool_event_block table: {e}"))
    }

    /// Adds block-hash storage to databases created before hash-bound profiler checkpoints.
    ///
    /// # Errors
    ///
    /// Returns an error if the schema update fails.
    pub async fn ensure_pool_event_block_hash_schema(&self) -> anyhow::Result<()> {
        sqlx::query("ALTER TABLE pool_event_block ADD COLUMN IF NOT EXISTS hash TEXT")
            .execute(&self.pool)
            .await
            .map(|_| ())
            .map_err(|e| anyhow::anyhow!("Failed to add pool event block hash storage: {e}"))
    }

    /// Inserts blocks using PostgreSQL COPY BINARY for maximum performance.
    ///
    /// This method is significantly faster than INSERT for bulk operations as it bypasses

View on GitHub (pinned to 18893faf8b)

Solutions

1. Check the wrapped `{e}` for the specific constraint or 'UNEQUAL' unnest-array-lengths message
2. Ensure numbers/hashes/timestamps slices have identical lengths before calling the method
3. Add ON CONFLICT handling or dedupe block numbers before insert so replays don't violate unique constraints
4. Confirm the `hash` column exists (run ensure_pool_event_block_hash_schema) before inserting hashes

Example fix

// before
assert!(numbers.len() == timestamps.len());
// after
assert!(numbers.len() == hashes.len() && numbers.len() == timestamps.len(), "pool_event_block arrays must be aligned");

anyhow::ensure!(numbers.len() == hashes.len() && numbers.len() == timestamps.len(), "pool_event_block arrays misaligned");
anyhow::ensure!(numbers.windows(2).all(|w| w[0] != w[1]), "duplicate block numbers in batch");

fn aligned(numbers: &[i64], hashes: &[String], timestamps: &[i64]) -> bool {
    numbers.len() == hashes.len() && numbers.len() == timestamps.len()
}

match db.insert_pool_event_blocks(&numbers, &hashes, &timestamps).await {
    Ok(()) => {}
    Err(e) if e.to_string().contains("duplicate key") => tracing::debug!("pool event blocks already recorded"),
    Err(e) => return Err(e),
}

When it happens

Trigger: Calling the batch insert for pool-event blocks with mismatched `numbers`/`hashes`/`timestamps` slice lengths (UNNEST produces unequal-row-set error), duplicate (number, hash) rows violating a unique constraint, or any Postgres-level execution failure on the pool.

Common situations: Indexer crash-recovery replaying events already recorded (duplicate key); arrays built from filtered events where one array was filtered but others were not (length mismatch); DB migrated to a schema expecting NOT NULL hash while old rows pass NULL.

Related errors

• Failed to insert into block table: {e}
• Failed to batch insert into block table: {e}
• Failed to insert into dex table: {e}
• Failed to insert into pool table: {e}
• Failed to batch insert into pool table: {e}

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