{"record":{"id":"ab121d4bebeef44b","repo":"nautechsystems/nautilus_trader","slug":"failed-to-insert-into-pool-table-e","errorCode":null,"errorMessage":"Failed to insert into pool table: {e}","messagePattern":"Failed to insert into pool table: (.+?)","errorType":"exception","errorClass":"anyhow::Error","httpStatus":null,"severity":"error","filePath":"crates/adapters/blockchain/src/cache/database.rs","lineNumber":839,"sourceCode":"        )\n        .bind(pool.chain.chain_id as i32)\n        .bind(pool.address.to_string())\n        .bind(pool.pool_identifier.as_ref())\n        .bind(pool.dex.name.to_string())\n        .bind(pool.creation_block as i64)\n        .bind(pool.token0.chain.chain_id as i32)\n        .bind(pool.token0.address.to_string())\n        .bind(pool.token1.chain.chain_id as i32)\n        .bind(pool.token1.address.to_string())\n        .bind(pool.fee.map(|fee| fee as i32))\n        .bind(pool.tick_spacing.map(|tick_spacing| tick_spacing as i32))\n        .bind(pool.initial_tick)\n        .bind(pool.initial_sqrt_price_x96.as_ref().map(|p| p.to_string()))\n        .bind(pool.hooks.as_ref().map(|h| h.to_string()))\n        .execute(&self.pool)\n        .await\n        .map(|_| ())\n        .map_err(|e| anyhow::anyhow!(\"Failed to insert into pool table: {e}\"))\n    }\n\n    /// Inserts multiple pools in a single database operation using UNNEST for optimal performance.\n    ///\n    /// # Errors\n    ///\n    /// Returns an error if the database operation fails.\n    pub async fn add_pools_batch(&self, pools: &[Pool]) -> anyhow::Result<()> {\n        if pools.is_empty() {\n            return Ok(());\n        }\n\n        // Prepare vectors for each column\n        let len = pools.len();\n        let mut addresses: Vec<String> = Vec::with_capacity(len);\n        let mut pool_identifiers: Vec<String> = Vec::with_capacity(len);\n        let mut dex_names: Vec<String> = Vec::with_capacity(len);\n        let mut creation_blocks: Vec<i64> = Vec::with_capacity(len);","sourceCodeStart":821,"sourceCodeEnd":857,"githubUrl":"https://github.com/nautechsystems/nautilus_trader/blob/18893faf8b356be3320add8de2f861b0b647cf06/crates/adapters/blockchain/src/cache/database.rs#L821-L857","documentation":"Database write error in the blockchain cache's pool insert: the SQLx upsert of a liquidity pool row (addresses, tokens, fee, tick spacing, initial state) into PostgreSQL failed; the driver error is embedded in the message.","triggerScenarios":"Calling the pool insert with a record whose `initial_sqrt_price_x96` string is malformed for the column's cast, `hooks`/`initial_tick` conflicting with column constraints, foreign-key failure when the pool's dex doesn't exist in `dex`, or connection/pool failure during execute.","commonSituations":"Inserting a pool whose DEX wasn't registered first (FK violation); V4-style pools with hooks written to a schema predating the hooks column; sqrt_price serialized with scientific notation that the TEXT/numeric column rejects; empty database missing migrations.","solutions":["Read the wrapped `{e}` for the exact constraint (FK to dex, NOT NULL, cast)","Insert the parent DEX record before pools that reference it","Confirm the schema includes initial_tick/initial_sqrt_price_x96/hooks columns (run migrations) — old schemas cause 'column does not exist'","Validate initial_sqrt_price_x96 renders as a plain decimal/hex string the column accepts before binding"],"exampleFix":"// before\ndb.insert_pool(&pool).await?; // FK failure if dex missing\n// after\ndb.insert_dex(&dex).await?;\ndb.insert_pool(&pool).await?;","handlingStrategy":"validation","validationCode":"anyhow::ensure!(db_dex_exists(pool, &pool_record.dex).await?, \"referencing dex not in DB; insert it first\");\nif let Some(p) = &pool_record.initial_sqrt_price_x96 {\n    anyhow::ensure!(!p.to_string().is_empty(), \"empty sqrt price\");\n}","typeGuard":"fn pool_record_valid(p: &PoolRecord) -> bool {\n    !p.address.is_empty() && p.initial_tick.map_or(true, |t| t >= i32::MIN as i64 && t <= i32::MAX as i64)\n}","tryCatchPattern":"match db.insert_pool(&pool_record).await {\n    Err(e) if e.to_string().contains(\"violates foreign key\") => {\n        db.insert_dex(&parent_dex).await?;\n        db.insert_pool(&pool_record).await?;\n    }\n    Err(e) => return Err(e),\n    Ok(()) => {}\n}","preventionTips":["Persist parent DEX rows before pool rows in the same transaction","Run migrations so initial_tick/sqrt_price/hooks columns exist","Serialize sqrt_price_x96 as a plain decimal string compatible with the column type"],"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"}