What causes "Failed to add pool event block hash storage: {e}" in nautechsystems/nautilus_trader?

Ensure the base migrations created `pool_event_block` before calling this helper, or create the table if absent

nautechsystems/nautilus_trader · error · anyhow::Error

Failed to add pool event block hash storage: {e}

Error message

Failed to add pool event block hash storage: {e}

What it means

The schema-migration helper `ensure_pool_event_block_hash_schema` runs `ALTER TABLE pool_event_block ADD COLUMN IF NOT EXISTS hash TEXT`; failure is wrapped with this message. It indicates the DDL statement itself could not run against Postgres.

Source

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

        .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
    /// SQL parsing and uses PostgreSQL's native binary protocol.
    ///
    /// # Errors
    ///
    /// Returns an error if the COPY operation fails.
    pub async fn add_blocks_copy(&self, chain_id: u32, blocks: &[Block]) -> anyhow::Result<()> {
        let copy_handler = PostgresCopyHandler::new(&self.pool);
        copy_handler.copy_blocks(chain_id, blocks).await
    }

    /// Inserts tokens using PostgreSQL COPY BINARY for maximum performance.
    ///
    /// # Errors

View on GitHub (pinned to 18893faf8b)

Solutions

Ensure the base migrations created `pool_event_block` before calling this helper, or create the table if absent
Run the migration as a role with ALTER privilege (owner/superuser) or grant the required rights
Run against the primary, not a read replica
Check pg_locks for blocking sessions if the ALTER hangs, and run migrations during low traffic

Example fix

// before: ALTER fails when table missing
sqlx::query("ALTER TABLE pool_event_block ADD COLUMN IF NOT EXISTS hash TEXT")
// after: guard the table's existence first
sqlx::query("CREATE TABLE IF NOT EXISTS pool_event_block (...)").execute(&self.pool).await?;
sqlx::query("ALTER TABLE pool_event_block ADD COLUMN IF NOT EXISTS hash TEXT")

Defensive patterns

Strategy: try-catch

Validate before calling

// Verify table exists and you can ALTER before running the migration helper
let exists: Option<i32> = sqlx::query_scalar(
    "SELECT 1 FROM information_schema.tables WHERE table_name = 'pool_event_block'")
    .fetch_optional(&pool).await?;
anyhow::ensure!(exists.is_some(), "pool_event_block table missing; run base migrations first");

Type guard

async fn table_exists(pool: &sqlx::PgPool, name: &str) -> anyhow::Result<bool> {
    Ok(sqlx::query_scalar::<_, i32>("SELECT 1 FROM information_schema.tables WHERE table_name = $1")
        .bind(name).fetch_optional(pool).await?.is_some())
}

Try / catch

if let Err(e) = db.ensure_pool_event_block_hash_schema().await {
    anyhow::bail!("schema upgrade failed (check DDL privileges / primary vs replica): {e:#}");
}

Prevention

Run schema migrations at startup against the primary, before any writes
Use a migration role with ALTER privileges, or grant them to the app role
Never run DDL against read replicas
Check pg_locks if ALTER appears to hang — long transactions can block it

When it happens

Trigger: Calling ensure_pool_event_block_hash_schema when the database user lacks ALTER privilege on the table, the table `pool_event_block` does not exist yet (ALTER fails because the base table is missing), the DB is read-only (replica), or a lock on the table blocks the ALTER until statement timeout.

Common situations: Fresh database provisioned without running the base migrations first; connecting with an app role that has DML but not DDL rights; running against a hot-standby replica; long-running queries holding ACCESS EXCLUSIVE-compatible locks causing ALTER to hang/timeout.

Related errors

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