{"record":{"id":"af411a17126a7d46","repo":"nautechsystems/nautilus_trader","slug":"failed-to-batch-insert-into-pool-event-block-table","errorCode":null,"errorMessage":"Failed to batch insert into pool_event_block table: {e}","messagePattern":"Failed to batch insert into pool_event_block table: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":628,"sourceCode":"                chain_id, number, hash, timestamp\n            )\n            SELECT\n                $1, *\n            FROM UNNEST(\n                $2::int8[], $3::text[], $4::text[]\n            )\n            ON CONFLICT (chain_id, number)\n            DO UPDATE SET hash = EXCLUDED.hash, timestamp = EXCLUDED.timestamp\n           \",\n        )\n        .bind(chain_id_db)\n        .bind(&numbers[..])\n        .bind(&hashes[..])\n        .bind(&timestamps[..])\n        .execute(&self.pool)\n        .await\n        .map(|_| ())\n        .map_err(|e| anyhow::anyhow!(\"Failed to batch insert into pool_event_block table: {e}\"))\n    }\n\n    /// Adds block-hash storage to databases created before hash-bound profiler checkpoints.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the schema update fails.\n    pub async fn ensure_pool_event_block_hash_schema(&self) -> anyhow::Result<()> {\n        sqlx::query(\"ALTER TABLE pool_event_block ADD COLUMN IF NOT EXISTS hash TEXT\")\n            .execute(&self.pool)\n            .await\n            .map(|_| ())\n            .map_err(|e| anyhow::anyhow!(\"Failed to add pool event block hash storage: {e}\"))\n    }\n\n    /// Inserts blocks using PostgreSQL COPY BINARY for maximum performance.\n    ///\n    /// This method is significantly faster than INSERT for bulk operations as it bypasses","sourceCodeStart":610,"sourceCodeEnd":646,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L610-L646","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Check the wrapped `{e}` for the specific constraint or 'UNEQUAL' unnest-array-lengths message","Ensure numbers/hashes/timestamps slices have identical lengths before calling the method","Add ON CONFLICT handling or dedupe block numbers before insert so replays don't violate unique constraints","Confirm the `hash` column exists (run ensure_pool_event_block_hash_schema) before inserting hashes"],"exampleFix":"// before\nassert!(numbers.len() == timestamps.len());\n// after\nassert!(numbers.len() == hashes.len() && numbers.len() == timestamps.len(), \"pool_event_block arrays must be aligned\");","handlingStrategy":"validation","validationCode":"anyhow::ensure!(numbers.len() == hashes.len() && numbers.len() == timestamps.len(), \"pool_event_block arrays misaligned\");\nanyhow::ensure!(numbers.windows(2).all(|w| w[0] != w[1]), \"duplicate block numbers in batch\");","typeGuard":"fn aligned(numbers: &[i64], hashes: &[String], timestamps: &[i64]) -> bool {\n    numbers.len() == hashes.len() && numbers.len() == timestamps.len()\n}","tryCatchPattern":"match db.insert_pool_event_blocks(&numbers, &hashes, &timestamps).await {\n    Ok(()) => {}\n    Err(e) if e.to_string().contains(\"duplicate key\") => tracing::debug!(\"pool event blocks already recorded\"),\n    Err(e) => return Err(e),\n}","preventionTips":["Build the three parallel arrays from a single loop so they can never diverge","Call ensure_pool_event_block_hash_schema before first write on upgraded databases","Make inserts idempotent with ON CONFLICT for crash-recovery replays"],"tags":["database","postgres","sqlx","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-14T05:17:10.506Z"}